Bulk update методами LINQ (Entity Framework Core) дополнительной таблицы при связи много-ко-многим

Question

Есть две таблицы Pharmacies и Products, которые связаны связью много-ко-многим. Пытаюсь сделать метод для обновления дополнительной таблицы PharmacyProductsEntity методом ExecuteUpdate, но не могу понять, как правильно построить такой запрос. Проблема в том, что не все методы могут транслироваться в SQL запрос.

Вот код, который я смог собрать:

public async Task ApplyRange(Guid pharmacyId, IEnumerable<(Guid id, int count)> products)
{
    await _table
    .Where(entity => entity.PharmacyId == pharmacyId && products.Where(p => p.id == entity.ProductId).Any())
    .ExecuteUpdateAsync(entity => entity.SetProperty(x => x.Count, x => x.Count + products.First(p => p.id == x.ProductId).count));
}

К сведению, используется Entity Framework Core 8.

Answer 1

Насколько я знаю EF, это не сделать чисто его средствами. Дело в том, что тут нужно передавать на сервер коллекцию сложного типа из двух полей и далее как-то осуществлять поиск по ней там же, на сервере. Думаю, для каждой СУБД будет своё решение.
Могу предложить решение для Sql Server.

Будем использовать Table Valued Parameter.
Для начала нужно создать пользовательский тип в БД:

create type dbo.ProductDTO as table (
    [Id] uniqueidentifier not null,
    [Count] int not null
)

И соответствующий ему класс. Он нужен просто для маппинга.

public class ProductDTO
{
    public Guid Id { get; set; }
    public int Count { get; set; }
}

В EF7 нужно дополнительно зарегистрировать эту сущность:

modelBuilder.Entity<ProductDTO>().HasNoKey().ToView(null);

В EF8 можно обойтись без этого.

Создаём DataTable с двумя колонками и заполняем его данными.
Далее создаём TVP с использованием сырого SQL. Увы, без этого никак.
Обращу внимание, что этот SQL не отсылается отдельно в БД, а встраивается в последующий запрос.
После чего соединяем (JOIN) таблицу и табличный параметр. И вызываем ExecuteUpdate.

void ApplyRange(Guid pharmacyId, IEnumerable<(Guid id, int count)> products)
{
    var table = new DataTable
    {
        Columns = {
            { "Id", typeof(Guid) },
            { "Count", typeof(int) }}
    };
    foreach (var x in products)
    {
        table.Rows.Add(x.id, x.count);
    }

    var tvp = context.Set<ProductDTO>().FromSqlRaw(
        "SELECT * FROM @tmp",
        new SqlParameter("@tmp", table) { TypeName = "dbo.ProductDTO" }
    );

    var query =
        from x in context.PharmacyProducts
        where x.PharmacyId == pharmacyId
        join y in tvp on x.ProductId equals y.Id
        select new { x, y };

    query.ExecuteUpdate(entity => entity.SetProperty(
        z => z.x.Count, z => z.x.Count + z.y.Count));
}

Сгенерированный SQL выглядит лаконично и правильно:

UPDATE [p]
SET [p].[Count] = [p].[Count] + [p0].[Count]
FROM [PharmacyProducts] AS [p]
INNER JOIN (
    SELECT * FROM @tmp
) AS [p0] ON [p].[ProductId] = [p0].[Id]
WHERE [p].[PharmacyId] = @__pharmacyId_0

В PostgreSQL вместо TVP, вероятно, можно использовать массив (array) типа NpgsqlDbType.Composite или использовать JSON.

EF Core Tools & Extensions - здесь большой список расширений для EF. Среди них есть несколько, которые позволяют делать массовые операции (искать на странице по терминам bulk и batch).
Но позволяют ли какие-то из них использовать сложный тип-коллекцию из нескольких полей - неизвестно.

БЛОГ НА HUSL

Bulk update методами LINQ (Entity Framework Core) дополнительной таблицы при связи много-ко-многим

Ответы (1 шт):