kostyl.dev
Ef Core

LINQ-запити в EF Core (Частина 2)

EF.Functions, складні JOIN, Union/Intersect/Except, AsNoTracking, Distinct, raw SQL у LINQ, типові помилки N+1 та LazyLoading у LINQ, best practices для продуктивних запитів.

LINQ-запити in EF Core: Продвинуті техніки

Це продовження статті «LINQ-запити в EF Core». Читайте послідовно.

EF.Functions: провайдерні SQL-функції

EF.Functions — спеціальний клас у EF Core, що надає доступ до SQL-функцій, які не мають прямого аналога у стандартному LINQ або C#. Ці методи не можна викликати поза LINQ-запитом (вони кидають NotSupportedException) — вони існують виключно для трансляції у SQL.

Like: підстановочний рядковий пошук

// EF.Functions.Like транслюється у SQL LIKE
var products = await context.Products
    .Where(p => EF.Functions.Like(p.Name, "%laptop%"))
    .ToListAsync();
// SQL: WHERE Name LIKE '%laptop%'

// Різниця між Contains і Like:
// p.Name.Contains("laptop") → WHERE Name LIKE '%laptop%' (те саме для простого випадку)
// Але EF.Functions.Like дає доступ до повного LIKE-синтаксису:
var products2 = await context.Products
    .Where(p => EF.Functions.Like(p.Name, "MacBook Pro%")) // Тільки початок
    .ToListAsync();
// WHERE Name LIKE 'MacBook Pro%'

FreeText і Contains (SQL Server Full-Text Search)

// SQL Server: Full-Text Search через EF.Functions
var fullTextResults = await context.Products
    .Where(p => EF.Functions.FreeText(p.Description, "wireless bluetooth audio"))
    .ToListAsync();
// SQL: WHERE FREETEXT(Description, 'wireless bluetooth audio')

// CONTAINS з префіксним пошуком
var containsResults = await context.Products
    .Where(p => EF.Functions.Contains(p.Name, "\"lapt*\""))
    .ToListAsync();
// SQL: WHERE CONTAINS(Name, '"lapt*"')

DateDiffDay, DateDiffHour (SQL Server)

// Замовлення, зроблені за останні 7 днів
var recentOrders = await context.Orders
    .Where(o => EF.Functions.DateDiffDay(o.PlacedAt, DateTime.UtcNow) <= 7)
    .ToListAsync();
// SQL: WHERE DATEDIFF(day, PlacedAt, GETUTCDATE()) <= 7

// Порівняти дату без часу
var today = DateTime.UtcNow.Date;
var todayOrders = await context.Orders
    .Where(o => EF.Functions.DateDiffDay(o.PlacedAt, today) == 0)
    .OrderBy(o => o.PlacedAt)
    .ToListAsync();
// SQL: WHERE DATEDIFF(day, PlacedAt, @today) = 0

ILike (PostgreSQL case-insensitive Like)

// PostgreSQL: ILike — case-insensitive LIKE
// (Npgsql провайдер)
var products = await context.Products
    .Where(p => EF.Functions.ILike(p.Name, "%maC%"))
    .ToListAsync();
// SQL (PostgreSQL): WHERE Name ILIKE '%maC%'
// Знайде: Mac, mac, MAC, maC — без урахування регістру

AtTimeZone (SQL Server 2016+)

// Конвертація UTC у локальний час у запиті
var kyivOrders = await context.Orders
    .Select(o => new
    {
        o.Id,
        o.OrderNumber,
        // UTC → Kyiv time у SQL
        PlacedAtLocal = EF.Functions.AtTimeZone(o.PlacedAt, "FLE Standard Time")
    })
    .ToListAsync();
// SQL: SELECT Id, OrderNumber, PlacedAt AT TIME ZONE 'UTC' AT TIME ZONE 'FLE Standard Time'

Складні JOIN: явні і навігаційні

EF Core підтримує JOIN через навігаційні властивості (неявний JOIN) і через явний LINQ join оператор.

Неявний JOIN через навігаційні властивості (рекомендований)

// Неявний: EF Core розуміє зв'язки і сам додає JOIN
var products = await context.Products
    .Where(p => p.Category.Name == "Electronics")
    .Select(p => new { p.Name, p.Price, CategoryName = p.Category.Name })
    .ToListAsync();
// SQL: SELECT p.Name, p.Price, c.Name
//      FROM Products p
//      LEFT JOIN Categories c ON c.Id = p.CategoryId
//      WHERE c.Name = 'Electronics'

Явний LINQ Join

// Явний join: потрібний коли немає навігаційних властивостей
// або для cross-entity запитів
var productOrders = await context.Orders
    .Join(
        context.Customers,
        order    => order.CustomerId,
        customer => customer.Id,
        (order, customer) => new
        {
            order.OrderNumber,
            order.TotalAmount,
            CustomerName = customer.FullName,
            CustomerEmail = customer.Email
        }
    )
    .Where(x => x.TotalAmount > 5000)
    .ToListAsync();
// SQL: SELECT o.OrderNumber, o.TotalAmount, c.FullName, c.Email
//      FROM Orders o JOIN Customers c ON c.Id = o.CustomerId
//      WHERE o.TotalAmount > 5000

Left Join (GroupJoin + SelectMany)

Стандартний LINQ join робить INNER JOIN. Для LEFT JOIN потрібен GroupJoin + SelectMany або навігаційні властивості:

// LEFT JOIN через GroupJoin + SelectMany
var categoriesWithOpt = await context.Categories
    .GroupJoin(
        context.Products.Where(p => p.IsActive),
        cat     => cat.Id,
        product => product.CategoryId,
        (cat, products) => new { cat, products }
    )
    .SelectMany(
        x => x.products.DefaultIfEmpty(), // DefaultIfEmpty → LEFT JOIN
        (x, product) => new
        {
            CategoryName = x.cat.Name,
            ProductName  = product == null ? "Немає продуктів" : product.Name,
            Price        = product == null ? (decimal?)null : product.Price
        }
    )
    .ToListAsync();
// SQL: SELECT c.Name, p.Name, p.Price
//      FROM Categories c
//      LEFT JOIN Products p ON p.CategoryId = c.Id AND p.IsActive = 1
Практична рекомендація: У більшості випадків LEFT JOIN ефективніше виражається через навігаційні властивості у Select-проєкції або через Include. Явний GroupJoin + SelectMany зарезервовано для нестандартних JOIN-умов.

Кілька JOIN через ланцюг навігацій

// Три таблиці: OrderItems → Products → Categories
var itemDetails = await context.OrderItems
    .Where(oi => oi.Order.CustomerId == customerId)
    .Select(oi => new
    {
        oi.Quantity,
        oi.UnitPrice,
        ProductName  = oi.Product.Name,          // JOIN Products
        CategoryName = oi.Product.Category.Name,  // JOIN Categories
        OrderNumber  = oi.Order.OrderNumber       // JOIN Orders
    })
    .ToListAsync();
// SQL: SELECT oi.Quantity, oi.UnitPrice, p.Name, c.Name, o.OrderNumber
//      FROM OrderItems oi
//      JOIN Orders o ON o.Id = oi.OrderId
//      JOIN Products p ON p.Id = oi.ProductId
//      JOIN Categories c ON c.Id = p.CategoryId
//      WHERE o.CustomerId = @customerId

Union, Intersect, Except

Union: об'єднання двох запитів

// Отримати всі продукти з категорій "Electronics" АБО "Gaming"
var electronicsIds = context.Products
    .Where(p => p.Category.Name == "Electronics")
    .Select(p => p.Id);

var gamingIds = context.Products
    .Where(p => p.Category.Name == "Gaming")
    .Select(p => p.Id);

// Union: дублікати видаляються (UNION)
var allIds = await electronicsIds.Union(gamingIds).ToListAsync();
// SQL: SELECT Id FROM Products WHERE Category='Electronics'
//      UNION
//      SELECT Id FROM Products WHERE Category='Gaming'

// UnionAll: дублікати залишаються (UNION ALL)
var withDuplicates = await electronicsIds.Concat(gamingIds).ToListAsync();
// SQL: ... UNION ALL ...

Практичний Union: пошук зі зваженими результатами

// Спочатку точні збіги, потім часткові
var exactMatches = context.Products
    .Where(p => p.Name == searchTerm)
    .Select(p => new { p.Id, p.Name, Rank = 1 });

var partialMatches = context.Products
    .Where(p => p.Name.Contains(searchTerm) && p.Name != searchTerm)
    .Select(p => new { p.Id, p.Name, Rank = 2 });

var searchResults = await exactMatches
    .Union(partialMatches)
    .OrderBy(r => r.Rank)
    .ThenBy(r => r.Name)
    .ToListAsync();

Intersect та Except

// Intersect: тільки ті, що є І там, І там
var customersWithOrdersAndReviews = await context.Orders
    .Select(o => o.CustomerId)
    .Intersect(
        context.Reviews.Select(r => r.CustomerId)
    )
    .ToListAsync();
// SQL: SELECT CustomerId FROM Orders
//      INTERSECT
//      SELECT CustomerId FROM Reviews

// Except: є у першому, але не у другому
var customersWithOrdersButNoReviews = await context.Orders
    .Select(o => o.CustomerId)
    .Except(
        context.Reviews.Select(r => r.CustomerId)
    )
    .ToListAsync();
// SQL: SELECT CustomerId FROM Orders
//      EXCEPT
//      SELECT CustomerId FROM Reviews

AsNoTracking: запити без Change Tracking

По-замовчуванням EF Core відстежує всі завантажені entity у Change Tracker — зберігає snapshot для виявлення змін. Це займає пам'ять і час. Для read-only запитів (де не плануємо зміни) — AsNoTracking() значно ефективніший.

// З трекінгом (за замовчуванням): дорожче, але потрібне для SaveChanges
var tracked = await context.Products.ToListAsync();
// Change Tracker: зберігає копію кожного Product

// БЕЗ трекінгу: швидше, менше пам'яті
var readOnly = await context.Products
    .AsNoTracking()
    .ToListAsync();
// Жодних snapshot, жодних EntityEntry — чисте читання

// AsNoTrackingWithIdentityResolution (EF Core 5+)
// Уникає дублікатів при Include, але без повного трекінгу
var withNavigation = await context.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .Include(o => o.LineItems)
    .ToListAsync();

Глобальний AsNoTracking у DbContext

// Для цілого DbContext: READ-ONLY режим (наприклад, для репортів)
services.AddDbContext<ReportDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
});

За яких умов AsNoTracking дає виграш

+----------------------+------------------------+------------------------+
| Сценарій             | AsNoTracking           | Default (Tracking)     |
+----------------------+------------------------+------------------------+
| Список для UI        | ✅ Рекомендовано        | ❌ Зайвий overhead     |
| SaveChanges потрібен | ❌ Не можна зберегти   | ✅ Необхідний          |
| Великі resultsets    | ✅ Менша пам'ять       | ❌ Snapshot великий    |
| Повторний Find()     | ❌ Немає кешу          | ✅ Кеш через Id        |
| API Read endpoint    | ✅ Завжди              | ❌ Зайвий              |
+----------------------+------------------------+------------------------+

Distinct і DistinctBy

// Distinct: унікальні значення (по всьому об'єкту або примітиву)
var uniqueCategories = await context.Products
    .Select(p => p.CategoryId)
    .Distinct()
    .ToListAsync();
// SQL: SELECT DISTINCT CategoryId FROM Products

// Distinct по об'єкту (порівнює всі поля через Equals)
var distinctLocations = await context.Customers
    .Select(c => new { c.City, c.CountryCode })
    .Distinct()
    .OrderBy(l => l.CountryCode)
    .ThenBy(l => l.City)
    .ToListAsync();
// SQL: SELECT DISTINCT City, CountryCode FROM Customers
//      ORDER BY CountryCode, City

// DistinctBy (EF Core 7+): унікальні за конкретним полем
var firstPerCategory = await context.Products
    .OrderBy(p => p.Price)
    .DistinctBy(p => p.CategoryId) // перший (найдешевший) за категорією
    .ToListAsync();
// Увага: не всі провайдери транслюють DistinctBy — перевіряйте SQL

Типові помилки LINQ у EF Core

Помилка 1: Завантаження у пам'ять і фільтрація в C#

// ❌ НЕПРАВИЛЬНО: 1 мільйон рядків у пам'ять
var allCustomers = await context.Customers.ToListAsync();
var kyivCustomers = allCustomers.Where(c => c.City == "Kyiv").ToList();

// ✅ ПРАВИЛЬНО: фільтр у SQL
var kyivCustomers = await context.Customers
    .Where(c => c.City == "Kyiv")
    .ToListAsync();

Помилка 2: N+1 через навігаційні властивості без Include

// ❌ N+1: один запит для orders, потім 1 запит на кожен order для Customer
var orders = await context.Orders.ToListAsync(); // 1 SQL

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.FullName); // N SQL (Lazy Loading або виняток)
}

// ✅ ПРАВИЛЬНО: один запит з JOIN
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync(); // 1 SQL з JOIN

// Або проєкція:
var orderDtos = await context.Orders
    .Select(o => new { o.OrderNumber, CustomerName = o.Customer.FullName })
    .ToListAsync(); // 1 SQL з JOIN у SELECT

Помилка 3: Count у циклі

// ❌ N+1 з Count
var categories = await context.Categories.ToListAsync();
foreach (var cat in categories)
{
    int count = cat.Products.Count(); // N SQL! Lazy Loading або Exception
}

// ✅ ПРАВИЛЬНО: агрегація у Select
var categoriesWithCount = await context.Categories
    .Select(c => new { c.Name, ProductCount = c.Products.Count() })
    .ToListAsync(); // 1 SQL з COUNT у підзапиті

Помилка 4: Нетрансльовний вираз у середині запиту

// ❌ Метод, що не транслюється
var products = await context.Products
    .Where(p => MyBizRule(p.Price, p.CategoryId)) // InvalidOperationException!
    .ToListAsync();

// ✅ Завантажити необхідне і фільтрувати в C#
// (якщо таблиця відносно невелика і фільтр розумний)
var candidates = await context.Products
    .Where(p => p.IsActive)  // SQL фільтр
    .ToListAsync();

var filtered = candidates.Where(p => MyBizRule(p.Price, p.CategoryId)).ToList();

Помилка 5: Забути await

// ❌ Повертає Task<List<T>>, не List<T>
var products = context.Products.ToListAsync(); // Task без await!
// products — це Task, не список

// ✅ Завжди await
var products = await context.Products.ToListAsync();

Best Practices для продуктивних LINQ-запитів

1. Завжди проєктуйте те, що потрібно

// ❌ SELECT * — завантажуємо зайве
var products = await context.Products.Include(p => p.Reviews).ToListAsync();

// ✅ SELECT тільки потрібне
var summaries = await context.Products
    .Select(p => new ProductSummaryDto
    {
        Id      = p.Id,
        Name    = p.Name,
        Rating  = p.Reviews.Any() ? p.Reviews.Average(r => r.Rating) : 0
    })
    .ToListAsync();

2. AsNoTracking для read-only

// Read-only endpoints завжди з AsNoTracking
var list = await context.Products.AsNoTracking().Select(p => new DTO { ... }).ToListAsync();

3. Перевіряйте SQL через логування

// Реєстрація SQL логування у development:
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    if (env.IsDevelopment())
        options.LogTo(Console.WriteLine, LogLevel.Information)
               .EnableSensitiveDataLogging();
});

4. IQueryable як параметр репозиторію

// ✅ Передаємо IQueryable, а не IEnumerable
public async Task<List<ProductDto>> GetProductsAsync(
    IQueryable<Product> query)  // гнучкий — можна додати Where, Skip, Take ззовні
{
    return await query
        .AsNoTracking()
        .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
        .ToListAsync();
}

5. Compiled Queries для часто виконуваних запитів

// Compiled Query: компілюється один раз, виконується швидше
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Products.FirstOrDefault(p => p.Id == id));

// Виклик:
var product = await GetProductById(context, productId);
// Перший виклик: компілює LINQ у SQL
// Наступні: використовує скомпільований план

Практичні завдання (Частина 2)

Рівень 1 — Базовий

Завдання 1.1: EF.Functions на практиці

Для Employee (Id, FirstName, LastName, HireDate, Salary) напишіть запити через EF.Functions:

  • Всі, найняті більше 2 років тому: DateDiffDay(HireDate, NOW) >= 730
  • Повнотекстовий пошук по LastName через Like з wildcard: %ченко%
  • (PostgreSQL) ILike для case-insensitive пошуку по FirstName

Завдання 1.2: Union для пошуку

Реалізуйте пошуковий метод SearchProductsAsync(string query):

  1. Точний збіг по Name або Sku (ранг 1)
  2. Частковий збіг по Name (ранг 2)
  3. Збіг по Description (ранг 3)

Результати: Union трьох запитів, впорядковані по рангу, потім по Price.

Завдання 1.3: AsNoTracking аудит

Для Order з Include(Customer) напишіть два варіанти:

  1. З трекінгом → виміряйте кількість context.ChangeTracker.Entries().Count()
  2. AsNoTracking → те ж вимірювання

Поясніть різницю у контексті реального API endpoint.

Рівень 2 — Логіка

Завдання 2.1: Складний звіт без N+1

Напишіть запит для «Звіту по продуктивності менеджерів» де кожен менеджер має: FullName, TotalOrders, TotalRevenue, AvgOrderValue, TopProductName (найчастіше замовлений продукт).

Все одним запитом, без N+1. Підказка: TopProductName вимагає correlated subquery.

Завдання 2.2: Except для виявлення «порожніх» категорій

Знайдіть категорії, що мають продукти, але жодне замовлення ще не містить ці продукти:

  • Категорії з продуктами: context.Categories.Where(c => c.Products.Any())
  • Категорії у замовленнях: отримати через OrderItems → Products → CategoryId
  • Різниця через Except

Рівень 3 — Архітектура

Завдання 3.1: Generic Repository з LINQ Specification

Реалізуйте повний Generic Repository:

public interface IRepository<T> where T : BaseEntity
{
    Task<T?> GetByIdAsync(int id);
    Task<List<T>> GetAllAsync();
    Task<PagedResult<T>> GetPagedAsync(int page, int pageSize, IQueryable<T>? filter = null);
    Task<List<TDto>> ProjectAsync<TDto>(Expression<Func<T, TDto>> selector,
                                        Expression<Func<T, bool>>? filter = null);
    Task<int> CountAsync(Expression<Func<T, bool>>? filter = null);
    Task<bool> ExistsAsync(Expression<Func<T, bool>> filter);
}

Правила:

  • GetAllAsync і GetByIdAsync: з трекінгом (для збереження змін)
  • ProjectAsync і GetPagedAsync: завжди AsNoTracking
  • Всі методи паважають Global Query Filters

Підсумок статті 16

Ця стаття повністю розкрила LINQ у EF Core:

Частина 1:

  • IQueryable<T> vs IEnumerable<T> — Expression Tree будується лазово, матеріалізується один раз
  • Server vs Client Evaluation — EF Core 3+ кидає виняток при нетрансльовних виразах
  • Where, OrderBy, Skip/Take — базові операції з правильним SQL
  • Select — проєкція уникає SELECT *, вкладені навігаційні → JOIN без Include
  • GroupBy — GROUP BY + HAVING через .Where() після GroupBy
  • Any, All, Count, Find vs FirstOrDefault

Частина 2:

  • EF.Functions — Like, ILike, FreeText, DateDiff, AtTimeZone
  • Складні JOIN — неявний через навігаційні, явний Join/GroupJoin, LEFT JOIN
  • Union, Intersect, Except — операції над Result Sets
  • AsNoTracking — read-only запити без overhead Change Tracker
  • Distinct, DistinctBy — дедублікація
  • Типові помилки: N+1, завантаження у пам'ять, нетрансльовні вирази, забутий await
  • Best Practices: проєкція, AsNoTracking, SQL-логування, Compiled Queries

Наступна стаття — Loading Related Data (стаття 17) — дає повне занурення у Eager Loading (Include/ThenInclude), Lazy Loading, Explicit Loading та Split Queries.

Додаткові ресурси

LINQ-запити в EF Core (Частина 1)

Глибокий розбір LINQ в EF Core — трансляція у SQL, IQueryable vs IEnumerable, Server vs Client Evaluation, проєкції Select, GroupBy, підзапити, складні умови фільтрації. Анатомія LINQ-виразу та типові помилки.

Завантаження Пов'язаних Даних (Частина 1)

Eager Loading через Include і ThenInclude в EF Core — стратегії завантаження навігаційних властивостей, Filtered Include, Split Queries для оптимізації картезіанського вибуху, АuditableIncludes і типові помилки.

Copyright © 2026