News

Using LINQ in C# to Query SQL Server Databases

LINQ gives C# developers a consistent way to describe filters, projections, joins, ordering and grouping. When it is used with Entity Framework Core and SQL Server, those expressions can be translated into SQL and executed close to the data. This makes application code easier to compose than a collection of manually assembled query strings.

The important distinction is that LINQ is a query language embedded in C#, not a database engine. A query against an IQueryable<T> usually builds an expression tree first. Entity Framework Core then translates that tree into SQL Server syntax, sends a command through the configured provider and materialises the returned rows as .NET objects.

That translation boundary determines what code is efficient and what code causes trouble. A predicate that remains inside the query can become a WHERE clause, while calling ToList() too early may load thousands of records into application memory. Understanding when execution happens is therefore more valuable than memorising isolated LINQ methods.

The examples below suit APIs, web applications and background services built with modern .NET. They are also relevant to Australian teams working across Sydney, Melbourne, Brisbane or remote locations, where sensible database access matters when a service is hosted in an Azure Australia region but customers connect over varied NBN and mobile networks.

Choosing A LINQ Data Access Approach

Entity Framework Core is the usual starting point for LINQ-based SQL Server access. A DbContext exposes tables through DbSet<T> properties, and the provider turns supported C# expressions into SQL. A simple model might contain Customer, Order and OrderLine entities, with relationships represented through navigation properties.

public sealed class ShopContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

A query can then read naturally:

var activeCustomers = await context.Customers
    .Where(customer => customer.IsActive)
    .OrderBy(customer => customer.LastName)
    .ToListAsync();

For teams that need complete control over SQL, Dapper or Microsoft.Data.SqlClient may be a better fit for selected operations. LINQ remains useful around those calls for in-memory transformations, yet it should not be assumed that every LINQ method can be translated by every provider. Check the generated SQL when performance or portability matters.

The choice also affects how much behaviour the application hides. Entity Framework Core handles parameterisation, change tracking and relationship mapping, while a micro-ORM leaves more decisions in the developer’s hands. A small Australian consultancy might prefer a single consistent EF Core approach for maintainability; a high-volume reporting service in Melbourne may combine it with hand-tuned SQL for a few demanding queries.

Understanding Deferred Execution And SQL Translation

Most database LINQ queries are deferred. Calling Where, Select, OrderBy or Join creates a new query definition but does not immediately contact SQL Server. Execution generally begins at a terminal operation such as ToListAsync, SingleAsync, FirstOrDefaultAsync, CountAsync or an asynchronous foreach.

var query = context.Orders
    .Where(order => order.CreatedUtc >= startDate)
    .OrderByDescending(order => order.CreatedUtc);

var recentOrders = await query.ToListAsync();

Until ToListAsync runs, query is still an IQueryable<Order>. That lets later code add another filter or projection, and Entity Framework Core can combine the complete expression into one SQL command. This is especially useful when an API applies optional search, status and date criteria.

IEnumerable<T> changes the picture. Once a query has been materialised, later LINQ operators run in memory rather than in SQL Server. The following code retrieves every customer before applying the name filter:

var customers = await context.Customers.ToListAsync();
var perthCustomers = customers
    .Where(customer => customer.City == "Perth")
    .ToList();

That may be acceptable for a tiny fixed list, but it is a poor default for production tables. Keep database operations as IQueryable for as long as practical, and use ToQueryString() during development to inspect the SQL generated by EF Core. Logging can reveal unexpected joins, missing predicates and repeated commands before they affect customers.

Filtering, Sorting And Paging Efficiently

A useful LINQ query narrows the result set early and returns only the fields the caller needs. A projection with Select avoids loading entire entities when an endpoint only needs a summary:

var summaries = await context.Orders
    .Where(order => order.Status == OrderStatus.Paid)
    .Select(order => new OrderSummary
    {
        Id = order.Id,
        CustomerName = order.Customer.Name,
        Total = order.Total
    })
    .OrderByDescending(order => order.Id)
    .ToListAsync();

The navigation property in the projection can become a SQL join. No explicit Include is needed when the selected fields describe the related data. This often produces a leaner query than loading a complete order graph and converting it afterwards.

Paging should use a stable ordering. Offset paging is familiar and straightforward:

var page = await context.Orders
    .OrderByDescending(order => order.CreatedUtc)
    .ThenByDescending(order => order.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Select(order => new OrderRow(order.Id, order.CreatedUtc, order.Total))
    .ToListAsync();

For large tables, keyset or seek pagination can be faster because SQL Server does not need to skip an ever-growing number of rows. Use the last row’s date and ID as the next cursor, and create a matching composite index. This matters for high-traffic services serving customers in Sydney or Brisbane, where a slow search endpoint can multiply database work during busy periods.

Avoid calling .ToLower() or .ToString() casually in a predicate. Such expressions may prevent an index from being used or may vary by provider and collation. SQL Server collation, accent sensitivity and case behaviour should be decided deliberately, especially for names and addresses entered through Australian forms.

Joining Related Records And Calculating Results

LINQ supports both explicit joins and navigation-based queries. Navigation properties are usually clearer when the entity model accurately represents relationships:

var totals = await context.Orders
    .Where(order => order.CreatedUtc >= monthStart)
    .GroupBy(order => order.Customer.State)
    .Select(group => new
    {
        State = group.Key,
        Revenue = group.Sum(order => order.Total),
        Count = group.Count()
    })
    .ToListAsync();

This kind of grouping lets SQL Server calculate totals rather than transferring every order to the web server. It is suitable for dashboards, exports and operational reports, although complex analytical workloads may eventually deserve a view, stored procedure or separate reporting database.

Explicit joins are valuable when there is no navigation property or when a query spans projected sources:

var unpaid = await
    (from order in context.Orders
     join customer in context.Customers
         on order.CustomerId equals customer.Id
     where order.Status == OrderStatus.Pending
     select new
     {
         order.Id,
         customer.Email,
         order.Total
     })
    .ToListAsync();

Be careful with collection navigation properties and Include. Loading several one-to-many relationships in a single query can create a cartesian explosion, where repeated combinations inflate the result set. EF Core’s AsSplitQuery() can separate related loads, but the correct option depends on the data shape and number of round trips.

Aggregates also need clear null and empty-set rules. Count returns zero, while some numeric aggregate expressions may need nullable handling. Encode those rules in the projection so that API consumers receive predictable values rather than database-provider surprises.

Tracking, Updates And Asynchronous Execution

Entity Framework Core tracks entities returned from ordinary queries so that changes can be detected and persisted with SaveChangesAsync. Tracking is convenient for an edit workflow:

var order = await context.Orders
    .SingleOrDefaultAsync(order => order.Id == orderId);

if (order is null)
    return NotFound();

order.Status = OrderStatus.Shipped;
await context.SaveChangesAsync();

For read-only pages, AsNoTracking() reduces tracking overhead:

var order = await context.Orders
    .AsNoTracking()
    .Where(item => item.Id == orderId)
    .Select(item => new OrderDetails(item.Id, item.Status, item.Total))
    .SingleOrDefaultAsync();

Use SingleAsync when the data model guarantees one result and a duplicate would signal corruption. Use FirstOrDefaultAsync when several matches are valid and the first ordered record is sufficient. An absent record should be handled explicitly rather than allowed to become an accidental null-reference error.

Prefer asynchronous database methods in ASP.NET Core applications. ToListAsync releases the request thread while SQL Server works, which helps a service handle concurrent requests. Pass a cancellation token from the HTTP request when appropriate, and configure command timeouts for known long-running reports rather than allowing a request to hang indefinitely.

For Australian businesses, privacy deserves attention during query design. The Privacy Act 1988 and Australian Privacy Principles influence how personal information such as email addresses, addresses and order history is collected, retained and exposed. Returning a narrow DTO, logging identifiers instead of raw personal data and restricting administrative queries are practical safeguards alongside database permissions.

A Practical Query Review Checklist

Code review should examine the SQL shape as well as the elegance of the C# expression. A short query can still perform badly if it scans a large table, triggers multiple round trips or materialises a large object graph. Run representative queries against realistic data volumes, rather than relying only on a local database containing a few rows.

A useful review can cover these points:

Parameterisation is handled automatically when normal LINQ expressions are used. Dynamic sorting and filtering still require care: map allowed client field names to known expressions instead of inserting arbitrary column names into a command. This protects the application from injection and prevents an API contract from becoming an unrestricted database interface.

The local market can make these choices visible quickly. Australian retailers often support catalogue searches, delivery areas and promotional reporting at the same time, while small businesses may run on modest cloud budgets. Efficient projections and appropriate indexes reduce Azure SQL consumption and help control costs billed in Australian dollars, including any applicable GST on the service arrangement.

A Reliable Development Routine

Good LINQ usage comes from a repeatable workflow rather than from avoiding every complex expression. Start with the required result, express it as a projection, inspect the SQL, test it against realistic data and then measure it under expected concurrency. Keep query composition close to the application boundary so that business rules remain visible and reusable.

A practical routine includes:

Integration tests against SQL Server are important because an in-memory provider does not reproduce relational constraints or provider translation. Containers, a dedicated test database or a CI service can expose differences involving collations, date functions, grouping and null semantics. Tests should verify outcomes and, for critical paths, assert that the number of database calls stays bounded.

When diagnosing a slow operation, use SQL Server execution plans and application telemetry together. A query that is fast in Adelaide but slow in production may be affected by data volume, parameter distribution, network distance or blocking. Azure regions in Australia can reduce latency, yet they cannot compensate for a missing index or a query that transfers unnecessary rows.

Steven’s experience across development work and personal projects is described in Steven’s background, and that practical perspective is useful when deciding how much abstraction a project can sensibly carry. The best implementation is usually the one the team can inspect, test and maintain over time.

Use LINQ to describe intent, then verify what SQL Server actually receives. Start with a narrow projection, keep execution deferred until the query is ready, measure important paths and apply database constraints and privacy controls deliberately. With that discipline, C# LINQ becomes a productive layer for reliable SQL Server applications rather than a hidden source of database workload.