.NET LINQ: Query Collections and Data Sources

Imagine building an online learning platform with thousands of courses, users, and lessons. You might need to display only published courses, sort them by popularity, find students who completed a lesson, or calculate the average score of a group of learners. Writing separate loops for every operation can quickly make your code repetitive and difficult to maintain.

C# provides LINQ (Language Integrated Query) to make data querying more readable and expressive. LINQ allows you to filter, transform, sort, group, and aggregate data using a consistent set of operators directly in C#.

LINQ works with in-memory collections such as lists and arrays. It also works with query providers, including Entity Framework Core, which can translate supported LINQ expressions into SQL and execute them against a database.

Learning LINQ helps you write cleaner application logic, process collections efficiently, and build database queries without manually constructing SQL for every operation. However, understanding how a query executes is just as important as knowing its syntax, especially when working with large datasets.

1. What Is LINQ in .NET?

LINQ stands for Language Integrated Query. It is a collection of C# language features, standard query operators, and provider-based querying capabilities that allow developers to work with data using a consistent approach.

Without LINQ, filtering a list often requires manually iterating through every item, checking conditions, and building a new collection. LINQ expresses the same operation using methods such as Where(), making the intention easier to understand.

Consider a collection of courses:

public class Course
{
    public int Id { get; set; }

    public string Title { get; set; } = string.Empty;

    public bool IsPublished { get; set; }

    public decimal Price { get; set; }
}

var courses = new List<Course>
{
    new Course
    {
        Id = 1,
        Title = "C# Fundamentals",
        IsPublished = true,
        Price = 499
    },
    new Course
    {
        Id = 2,
        Title = "Advanced .NET",
        IsPublished = false,
        Price = 799
    },
    new Course
    {
        Id = 3,
        Title = "ASP.NET Core",
        IsPublished = true,
        Price = 699
    }
};

Suppose your application must display only published courses. With LINQ, you can write:

var publishedCourses = courses
    .Where(course => course.IsPublished)
    .ToList();

The Where() operator filters the collection by the supplied condition. In this example, the result contains C# Fundamentals and ASP.NET Core because those courses are published.

2. Two Ways to Write LINQ Queries

LINQ supports two common syntaxes: method syntax and query syntax. Both express queries, but they use different forms.

Method Syntax

Method syntax uses standard query operators such as Where(), Select(), and OrderBy(). It is widely used in modern C# code and makes it convenient to chain several operations together.

var result = courses
    .Where(course => course.IsPublished)
    .OrderBy(course => course.Title)
    .Select(course => course.Title)
    .ToList();

This query filters the published courses, sorts them alphabetically by title, selects only their titles, and materializes the final result as a list.

Query Syntax

Query syntax resembles SQL and uses keywords such as from, where, orderby, and select.

var result =
    (from course in courses
     where course.IsPublished
     orderby course.Title
     select course.Title)
    .ToList();

Both examples produce the same list of published course titles. Query syntax can be particularly readable for complex queries involving joins and multiple range variables. Method syntax is convenient when composing reusable operations or using operators that do not have dedicated query-syntax keywords.

3. Filtering Data with Where()

The Where() operator filters a sequence according to a condition. It retains elements for which the supplied predicate evaluates to true.

// Find published courses.
var published = courses
    .Where(course => course.IsPublished)
    .ToList();

// Find courses that cost more than 500.
var expensiveCourses = courses
    .Where(course => course.Price > 500)
    .ToList();

// Combine multiple conditions.
var affordablePublishedCourses = courses
    .Where(course =>
        course.IsPublished && course.Price <= 600)
    .ToList();

A predicate can contain Boolean expressions, comparisons, and other operations supported by the query provider. The logical operators && and || allow you to combine conditions, while the ! operator negates a Boolean condition.

Filtering at the correct stage is important when querying databases. If EF Core can translate the filtering expression into SQL, the database can return only the matching rows instead of sending every row to your application for filtering.

4. Selecting Data with Select()

The Select() operator transforms each element into a new value. This process is called projection. Instead of returning complete objects, you can select individual properties or create a different object containing only the information you need.

// Select only course titles.
var titles = courses
    .Select(course => course.Title)
    .ToList();

// Project courses into a smaller anonymous object.
var courseSummaries = courses
    .Select(course => new
    {
        course.Id,
        course.Title,
        course.Price
    })
    .ToList();

Projection is especially useful when preparing data for API responses, reports, or user interfaces. It avoids carrying unnecessary properties through the rest of your application.

When using EF Core, a projection into a supported result shape can allow the provider to select only the required database columns. This can reduce network traffic and processing overhead, particularly for large tables.

Explore DTOs and Mapping in .NET to learn how projected data can be shaped into explicit objects for API contracts.

5. Sorting Data with OrderBy() and ThenBy()

LINQ provides operators for ordering a sequence. OrderBy() sorts in ascending order, while OrderByDescending() sorts in descending order.

// Sort by course title alphabetically.
var alphabetical = courses
    .OrderBy(course => course.Title)
    .ToList();

// Sort by price from highest to lowest.
var mostExpensive = courses
    .OrderByDescending(course => course.Price)
    .ToList();

// Sort by publication status, then by price.
var sortedCourses = courses
    .OrderByDescending(course => course.IsPublished)
    .ThenBy(course => course.Price)
    .ToList();

Use ThenBy() or ThenByDescending() to add secondary ordering criteria. In the final example, published courses appear first, and courses with the same publication status are ordered by price from lowest to highest.

Calling OrderBy() again on an already ordered sequence establishes a new primary ordering instead of adding a secondary key. Use ThenBy() when you want to preserve the previous ordering as the primary criterion.

6. Finding Items with First, FirstOrDefault, Single, and Any

LINQ provides operators for finding a specific item or checking whether matching data exists. Choosing the correct operator helps your code communicate the intended behavior.

// Return the first published course.
// Throws if no matching course exists.
var firstPublished = courses
    .First(course => course.IsPublished);

// Return the first matching course, or null if none exists.
var missingCourse = courses
    .FirstOrDefault(course => course.Id == 100);

// Check whether any course is published.
bool hasPublishedCourses = courses
    .Any(course => course.IsPublished);

// Check whether at least one course costs more than 1000.
bool hasExpensiveCourses = courses
    .Any(course => course.Price > 1000);

First() throws an exception if the sequence contains no matching element. FirstOrDefault() returns the default value when no match exists; for reference types such as Course, that default is normally null.

Single() and SingleOrDefault() are useful when the query is expected to identify at most one result. They throw if multiple matching elements are found. SingleOrDefault() also returns the default value when no match exists.

Use Any() when you only need to know whether a matching element exists. It expresses the intent more clearly than retrieving an entire list and checking its count.

7. Aggregation with Count, Sum, Average, Min, and Max

Aggregation operators calculate a summary from a collection. They are useful for dashboards, reporting, analytics, and other features that need totals or statistics.

int totalCourses = courses.Count();

int publishedCount = courses.Count(
    course => course.IsPublished);

decimal totalPrice = courses.Sum(
    course => course.Price);

decimal averagePrice = courses.Average(
    course => course.Price);

decimal lowestPrice = courses.Min(
    course => course.Price);

decimal highestPrice = courses.Max(
    course => course.Price);

Count() counts elements, while the numeric operators calculate values based on the selected property. You can also filter the sequence before aggregating it.

Be aware that some aggregate operations can throw or require special handling when the source is empty. For example, calling Average() on an empty sequence of non-nullable numeric values throws an exception. Define the expected behavior for empty collections and use appropriate nullable types or fallback logic when required.

8. Grouping Data with GroupBy()

The GroupBy() operator groups elements according to a key. It is useful when information needs to be organized into categories, such as courses by category, students by department, or orders by status.

Suppose a course has a category:

public class Course
{
    public int Id { get; set; }

    public string Title { get; set; } = string.Empty;

    public string Category { get; set; } = string.Empty;

    public bool IsPublished { get; set; }

    public decimal Price { get; set; }
}

You can group the collection by category and calculate how many courses each category contains:

var coursesByCategory = courses
    .GroupBy(course => course.Category)
    .Select(group => new
    {
        Category = group.Key,
        CourseCount = group.Count(),
        AveragePrice = group.Average(
            course => course.Price)
    })
    .ToList();

Each group exposes a Key representing the category. The query then calculates the count and average price for each group.

With database providers such as EF Core, supported grouping expressions can be translated into SQL aggregation. However, not every possible grouping expression is translatable by every provider. Check the generated SQL and provider behavior when building complex reports.

9. Joining Data with Join()

Real applications often store related information in separate collections or database tables. For example, a course may belong to a category, while a category contains multiple courses. LINQ provides Join() to combine elements based on matching keys.

public class Category
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;
}

public class Course
{
    public int Id { get; set; }

    public string Title { get; set; } = string.Empty;

    public int CategoryId { get; set; }
}

var result = courses.Join(
    categories,
    course => course.CategoryId,
    category => category.Id,
    (course, category) => new
    {
        CourseTitle = course.Title,
        CategoryName = category.Name
    })
    .ToList();

The first collection contains courses, and the second contains categories. The key selectors specify how their records are matched, while the final selector determines the shape of the result.

An inner join returns matching pairs only. If you need to retain courses even when they have no matching category, use an appropriate left-join pattern supported by your LINQ provider. When querying related EF Core entities, navigation properties and relationship configuration may provide a more convenient alternative.

10. Deferred Execution and Immediate Execution

One of the most important LINQ concepts is deferred execution. Many LINQ operators build a query without immediately processing every element. The query runs when an operation enumerates the sequence or otherwise requests a result.

var numbers = new List<int> { 1, 2, 3, 4, 5 };

var query = numbers.Where(number => number > 2);

// The query has not necessarily enumerated the list yet.
numbers.Add(6);

// Enumeration executes the query.
var result = query.ToList();

// result contains 3, 4, 5, and 6.

Operators such as Where() and Select() generally use deferred execution when working with LINQ to Objects. Operations such as ToList(), ToArray(), and many aggregation or single-result operators trigger execution to obtain their results.

Deferred execution means the source data may be observed at a later time than when the query was created. If the underlying collection changes before enumeration, the result may reflect those changes. Depending on the source and operation, repeated enumeration can also repeat work.

Materialize a query when you intentionally need a snapshot of its results, need to reuse the resulting collection, or need to cross a boundary where a concrete collection is required. Do not materialize every query automatically, because doing so can consume additional memory and prevent later operations from being composed into a database query.

11. IEnumerable vs. IQueryable

IEnumerable<T> and IQueryable<T> are important interfaces when working with LINQ. They look similar in everyday queries, but their behavior can differ significantly.

IEnumerable<T> represents a sequence of elements that can be enumerated. When used with LINQ to Objects, operators such as Where() and Select() execute the corresponding logic in .NET as the sequence is enumerated.

IQueryable<T> represents a query through an expression tree and a query provider. The provider can inspect the expression and translate supported operations into another query language, such as SQL.

// An EF Core query that can remain composable.
IQueryable<Product> query = dbContext.Products
    .Where(product => product.Price > 100);

// Add more filtering before executing it.
query = query.OrderBy(product => product.Name);

// The SQL query is executed when results are requested.
var products = await query.ToListAsync();

With EF Core, composing a query before materialization gives the provider an opportunity to translate the full expression into SQL. This enables filtering, ordering, and projection to happen in the database rather than downloading all records into application memory.

Be careful when converting an EF Core query to an in-memory collection too early. Calling ToList() before adding another filter causes the initial query to execute and retrieve its results. Later operations then work on the in-memory list instead of becoming part of the database query.

Also remember that not every C# method can be translated by a database provider. A LINQ expression supported by LINQ to Objects may fail when used in an EF Core query if the provider cannot translate it. Keep database query expressions translatable and perform application-specific transformations after retrieving the appropriate data when necessary.

12. Using LINQ with Entity Framework Core

LINQ is especially useful when querying databases through EF Core. You can build strongly typed queries in C# while allowing the provider to generate the database commands needed to retrieve the results.

using Microsoft.EntityFrameworkCore;

public async Task<List<ProductSummary>> GetPublishedProductsAsync(
    AppDbContext dbContext,
    CancellationToken cancellationToken)
{
    return await dbContext.Products
        .Where(product => product.IsPublished)
        .OrderBy(product => product.Name)
        .Select(product => new ProductSummary
        {
            Id = product.Id,
            Name = product.Name,
            Price = product.Price
        })
        .ToListAsync(cancellationToken);
}

public sealed class ProductSummary
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }
}

This example filters published products, orders them by name, selects the required columns into a summary object, and asynchronously materializes the results.

Because the filtering and projection remain part of the EF Core query, the provider can generally translate them into SQL. The cancellation token allows the operation to respond to a cancellation request when the underlying provider supports cancellation.

For database-backed queries, use asynchronous terminal operators such as ToListAsync(), FirstOrDefaultAsync(), and AnyAsync() where available. These operations should not be confused with LINQ to Objects, which ordinarily uses synchronous enumeration for its standard collection operators.

Learn more about database interaction in Data Access in .NET with Dapper and EF Core and schema evolution in EF Core Migrations.

13. Pagination and Performance Best Practices

LINQ makes queries readable, but a readable query is not necessarily an efficient query. This becomes particularly important when an application works with large collections or database tables.

For example, an API should not normally retrieve every product in a database just to display the first page of results. Apply filtering and ordering before retrieving the page:

public async Task<List<Product>> GetProductPageAsync(
    AppDbContext dbContext,
    int pageNumber,
    int pageSize,
    CancellationToken cancellationToken)
{
    pageNumber = Math.Max(1, pageNumber);
    pageSize = Math.Clamp(pageSize, 1, 100);

    return await dbContext.Products
        .Where(product => product.IsPublished)
        .OrderBy(product => product.Id)
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync(cancellationToken);
}

Skip() ignores the specified number of elements, while Take() limits how many are returned. When used with an EF Core query, the database provider can translate supported pagination operations into SQL.

Pagination should use a deterministic ordering. Without an appropriate OrderBy(), the order of database rows is not guaranteed. For large offsets or frequently changing datasets, keyset pagination may provide more predictable performance than repeatedly skipping many rows.

Other important practices include:

• Filter at the database before materializing an EF Core query.
• Use Select() to retrieve only the fields needed by the application.
• Use Any() or AnyAsync() when checking whether matching data exists.
• Avoid materializing the same query repeatedly when a single result collection will suffice.
• Inspect generated SQL and execution plans for complex or slow database queries.
• Apply reasonable pagination limits to API endpoints.

14. Common LINQ Mistakes

15. Best Practices for Writing LINQ Queries

• Use descriptive lambda parameter names when they improve readability.
• Choose the method or query syntax that makes the operation easiest to understand.
• Filter and project data early when doing so reduces the amount of data processed or transferred.
• Keep EF Core queries composable until the appropriate execution boundary.
• Use asynchronous EF Core terminal operators for database I/O in asynchronous application workflows.
• Choose FirstOrDefault(), SingleOrDefault(), and Any() according to the intended behavior.
• Use ThenBy() when adding secondary ordering criteria.
• Apply deterministic ordering and appropriate limits to paginated queries.
• Measure performance and inspect generated SQL rather than relying on syntax alone.
• Test important query behavior, particularly filtering, grouping, joins, and empty-result cases.

Conclusion

LINQ gives C# developers a consistent way to query collections and data sources. Operators such as Where(), Select(), OrderBy(), GroupBy(), and Join() help express filtering, transformation, sorting, grouping, and relationship matching in readable code.

To use LINQ effectively, you must also understand deferred execution, immediate materialization, and the difference between IEnumerable<T> and IQueryable<T>. These concepts become especially important when LINQ queries are translated into SQL by EF Core.

Practise LINQ first with lists and arrays, then apply the same concepts to database queries. Use deliberate filtering, projection, pagination, and asynchronous execution to create code that remains readable and performs well as your application grows.

Continue learning with Data Access in .NET to understand LINQ with EF Core, EF Core Migrations to understand schema evolution, and .NET Application Architecture to see how queries fit into application and data-access layers.