C# LINQ for Database Queries: Comparison with Doctrine DQL and Entity Framework Core

LINQ (Language Integrated Query) is one of C#'s most remarkable features. It allows you to write queries over collections, XML, APIs, and databases using unified syntax directly in your code — type-safe, with IDE autocompletion and compile-time checking. Combined with Entity Framework Core (EF Core), it is the direct equivalent of Symfony's Doctrine ORM with DQL.

LINQ Basics: Querying Collections¶

LINQ works with any IEnumerable<T>:

var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

// Method Syntax (recommended)
var evenSquares = numbers
    .Where(n => n % 2 == 0)
    .Select(n => n * n)
    .OrderBy(n => n)
    .ToList();
// Result: [4, 16, 36, 64, 100]

// Query Syntax (SQL-like, less common)
var evenSquaresQuery =
    from n in numbers
    where n % 2 == 0
    orderby n * n
    select n * n;

Comparison with PHP¶

// PHP with array_filter / array_map
$evenSquares = array_map(
    fn(int $n): int => $n * $n,
    array_filter($numbers, fn(int $n): bool => $n % 2 === 0)
);
sort($evenSquares);

// PHP with LINQ-like collection library (e.g. Doctrine ArrayCollection)
$collection->filter(fn($n) => $n % 2 === 0)->map(fn($n) => $n * $n);

C# LINQ is type-safe and lazy: the query is only evaluated when you call .ToList(), .ToArray(), or iterate over the sequence.

Entity Framework Core Setup¶

EF Core is the Doctrine equivalent in the .NET ecosystem: a code-first ORM with migrations.

// Models/Product.cs
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public string Category { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }

    public int? CategoryId { get; set; }
    public Category? CategoryNavigation { get; set; }
}

// Data/AppDbContext.cs
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(p => p.Id);
            entity.Property(p => p.Name).HasMaxLength(255).IsRequired();
            entity.Property(p => p.Price).HasPrecision(10, 2);
            entity.HasOne(p => p.CategoryNavigation)
                  .WithMany()
                  .HasForeignKey(p => p.CategoryId);
        });
    }
}

LINQ with Entity Framework Core — Database Queries¶

EF Core translates LINQ expressions into SQL. This is analogous to Doctrine's QueryBuilder or DQL.

Simple Queries¶

// Load all products
List<Product> all = await context.Products.ToListAsync();

// With WHERE
List<Product> affordable = await context.Products
    .Where(p => p.Price < 100)
    .ToListAsync();

// Single product
Product? product = await context.Products
    .FirstOrDefaultAsync(p => p.Id == 42);

Doctrine equivalent:

// All products
$all = $productRepository->findAll();

// With WHERE
// Note: findBy() only supports equality comparisons, so use QueryBuilder:
$affordable = $em->createQueryBuilder()
    ->select('p')
    ->from(Product::class, 'p')
    ->where('p.price < :price')
    ->setParameter('price', 100)
    ->getQuery()
    ->getResult();

Complex Queries with JOIN¶

// JOIN with navigation properties
var productsWithCategory = await context.Products
    .Include(p => p.CategoryNavigation)
    .Where(p => p.CategoryNavigation!.Name == "Electronics")
    .OrderBy(p => p.Price)
    .Select(p => new
    {
        p.Name,
        p.Price,
        Category = p.CategoryNavigation!.Name
    })
    .ToListAsync();

Doctrine equivalent:

$qb = $em->createQueryBuilder()
    ->select('p.name', 'p.price', 'c.name AS category')
    ->from(Product::class, 'p')
    ->join('p.category', 'c')
    ->where('c.name = :name')
    ->setParameter('name', 'Electronics')
    ->orderBy('p.price', 'ASC')
    ->getQuery()
    ->getResult();

Aggregation and Grouping¶

// GROUP BY with aggregation
var priceByCategory = await context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        Count = g.Count(),
        AveragePrice = g.Average(p => p.Price),
        MinPrice = g.Min(p => p.Price),
        MaxPrice = g.Max(p => p.Price)
    })
    .OrderByDescending(r => r.Count)
    .ToListAsync();

Doctrine DQL:

SELECT p.category, COUNT(p.id) as cnt, AVG(p.price) as avgPrice
FROM App\Entity\Product p
GROUP BY p.category
ORDER BY cnt DESC

Pagination¶

// Page 2, 20 items per page
int page = 2;
int pageSize = 20;

var pagedProducts = await context.Products
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

// Total count for pagination
int total = await context.Products.CountAsync();

Doctrine:

$query->setFirstResult(($page - 1) * $pageSize)
      ->setMaxResults($pageSize)
      ->getQuery()
      ->getResult();

Projections: Loading Only the Fields You Need¶

// DTO record for projection
public record ProductSummary(int Id, string Name, decimal Price);

// Projection with Select
var summaries = await context.Products
    .Where(p => p.Price > 50)
    .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
    .ToListAsync();

EF Core translates this into SELECT id, name, price FROM products WHERE price > 50 — only the required columns are loaded.

Raw SQL for Complex Queries¶

When LINQ is not sufficient:

// Raw SQL with EF Core
var products = await context.Products
    .FromSqlRaw("SELECT * FROM products WHERE MATCH(name, description) AGAINST ({0})", searchTerm)
    .ToListAsync();

// Raw SQL for non-entity queries (from EF Core 8)
var results = await context.Database
    .SqlQuery<ProductSummary>($"SELECT id, name, price FROM products WHERE price > {minPrice}")
    .ToListAsync();

Avoiding the N+1 Problem¶

As with Doctrine, the N+1 problem occurs when navigation properties are lazily loaded:

// BAD: N+1 query
var products = await context.Products.ToListAsync();
foreach (var product in products)
{
    // A separate query for each product!
    Console.WriteLine(product.CategoryNavigation?.Name);
}

// GOOD: Eager loading with Include
var products = await context.Products
    .Include(p => p.CategoryNavigation)
    .ToListAsync();

Conclusion¶

LINQ + EF Core is the most powerful ORM duo in the .NET world. For PHP developers familiar with Doctrine, the concepts are well-known: code-first, migrations, navigation properties (relations), and a QueryBuilder equivalent. The main advantage of LINQ over DQL: everything is type-safe, the compiler catches errors immediately, and the IDE provides full autocompletion.