LINQ In Entity Framework Core.

Entity Framework Core (EF Core) is a powerful Object-Relational Mapper (ORM) for .NET that allows developers to interact with databases using .NET objects. One of EF Core’s most important and powerful features is LINQ (Language Integrated Query) — a feature that allows you to write queries directly in C# using intuitive syntax.

Whether you're working on a Web API, MVC application, or a console app, LINQ in EF Core makes data querying clean, efficient, and maintainable.

What is LINQ?

LINQ (Language Integrated Query) allows you to query collections (like arrays, lists, and database sets) using C# syntax. With EF Core, LINQ becomes the primary way to interact with your database.

Instead of writing raw SQL queries, you use C# expressions to retrieve, filter, and project data from the database.

Benefits of Using LINQ in EF Core

Type-safe queries
IntelliSense support in IDEs
Compile-time checking
Improved maintainability
No need for raw SQL

Types of LINQ Queries

EF Core supports two styles of LINQ:

1. Method Syntax (most common)

var products = await _context.Products
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .ToListAsync();

2. Query Syntax

var products = await (from p in _context.Products
                      where p.Price > 100
                      orderby p.Name
                      select p).ToListAsync();

Both styles generate the same SQL under the hood.

Common LINQ Query Examples in EF Core.

1. Get All Records:

var products = await _context.Products.ToListAsync();

2. Filter Records (WHERE)

var expensive = await _context.Products
    .Where(p => p.Price > 500)
    .ToListAsync();

3. Select Specific Fields (SELECT)

var names = await _context.Products
    .Select(p => p.Name)
    .ToListAsync();

4. Sort Records (ORDER BY)

var sorted = await _context.Products
    .OrderByDescending(p => p.Price)
    .ToListAsync();

5. Get Single Record

var product = await _context.Products
    .FirstOrDefaultAsync(p => p.Id == 1);

6. Check Existence (ANY)

bool exists = await _context.Products.AnyAsync(p => p.Price > 1000);

7. Count Records

int count = await _context.Products.CountAsync();

8. Join With Another Table

var orders = await (from o in _context.Orders
                    join c in _context.Customers on o.CustomerId equals c.Id
                    select new { o.Id, c.Name }).ToListAsync();

Asynchronous Queries in LINQ

EF Core supports async versions of most LINQ methods, like:

ToListAsync()
FirstOrDefaultAsync()
AnyAsync()
CountAsync()

These async methods are essential in ASP.NET Core to avoid thread blocking and improve scalability.

Performance Tips for LINQ.

Use `AsNoTracking()` for read-only: Improves performance, skips tracking overhead
Use `Select()` to limit columns: Reduces DB load and memory usage
Avoid multiple `.ToListAsync()`: Combine filters before executing the query
Use `AnyAsync()` instead of `Count()`: Faster existence check

Why Use .Include()?

By default, EF Core uses lazy loading, which means related data is not loaded unless explicitly accessed, and that can cause N+1 query problems.

.Include() solves this by retrieving the main entity and its related entities in one go.

Example Scenario

Suppose you have these two related entities:

var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

foreach (var product in products)
{
    Console.WriteLine($"Product: {product.Name}, Category: {product.Category.Name}");
}

This performs a JOIN behind the scenes and loads both the Product and its related Category.

You can use .ThenInclude() to include deeper navigation:

var products = await _context.Products
    .Include(p => p.Category)
        .ThenInclude(c => c.Supplier)
    .ToListAsync();

Common Pitfalls

Forget to use await with async methods: Leads to unexecuted queries or runtime bugs.
Loading too much data: Avoid pulling unnecessary columns or rows.
N+1 queries: Use .Include() to load related data when needed.

Pagination is an essential technique in web development to divide large sets of data into manageable chunks (pages) so you don’t overload the server or the user interface. Let's understand this in detail:

What is Pagination?

Pagination is the process of retrieving a specific subset of records (usually a page of data) from a larger data source. Instead of fetching all records at once, you fetch only what is needed for the current page.

Example: Imagine you have 10,000 products in your database. You don’t want to load them all at once, so you display 10 or 20 products per page:

Key Parameters in Pagination

PageNumber: The current page number (e.g., 1, 2, 3…)
PageSize: Number of records to show per page (e.g., 10, 20)
Skip: Number of records to skip: (PageNumber - 1) * PageSize
Take: Number of records to fetch: PageSize

Example in EF Core (LINQ): Let's say you want Page 2 with 5 items per page:

[HttpGet]
public async Task<IActionResult> GetPagedProducts(int pageNumber = 1, int pageSize = 10)
{
    var totalCount = await _context.Products.CountAsync();

    var products = await _context.Products
        .OrderBy(p => p.Id)
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    var result = new
    {
        TotalCount = totalCount,
        PageSize = pageSize,
        CurrentPage = pageNumber,
        TotalPages = (int)Math.Ceiling((double)totalCount / pageSize),
        Data = products
    };

    return Ok(result);
}

This generates a SQL query that only returns 5 records, starting from the 6th product (ID 6–10).

Using LINQ in EF Core makes your database logic clean, expressive, and maintainable — a key advantage in modern ASP.NET Core development.