EFCore, запрос последнего значения из одной таблицы по каждому внешнему ключу

Question

Есть таблица PoolingValues:

id  objectid  timecode   value
1   1         230238423  18
2   2         485230529  17
3   3         904230428  14
4   3         234324231  250
5   1         546542622  444
6   2         732623211  123
7   3         908942102  1

Нужно через EntityFrameworkCore3.1 в рамках одного запроса получить последние значения для каждого objectid из таблицы PoolingValues. Последние записи по timecode, но можно и по uid, если что timecode индексирован.
Например в виде List, в котором будет следующее:

id  objectid  timecode   value
5   1         546542622  444
6   2         732623211  123
7   3         908942102  1

Если делаю через цикл, где сначала читаю все objectid, а потом внутри цикла запрашиваю последнее значение для каждого objectid - то это очень долго, т.к. objectid в базе десятки тысяч уникальных записей, и пока цикл по каждому вернёт - пройдут минуты, к тому-же таких таблиц может быть несколько, не только PoolingValues.

Хотел сделать так:

var ids = context.Objects.Select(s => s.id).ToList();
context.PoolingValues.GroupBy(g => g.objectid)... //дальше не понятно что делать
context.PoolingValues.Where(w => ids.Contains(w.objectid)).Distinct(d => d.objectid)... //тоже не известно

Answer 1

Положим у вас есть класс:

public class PoolingValue
{
    public int Id { get; set; }

    public int ObjectId { get; set; }

    public int TimeCode { get; set; }
    
    public int Value { get; set; }
}

Если вы хотите сгруппировать по ObjectId и взять максимальный Value - то пишите так:

var result = db.GroupBy(x => x.ObjectId)
               .Select(x => new { ObjectId = x.Key, Value = x.Max(y => y.Value)} );

Для ваших данных:

var db = new List<PoolingValue>()
{
    new PoolingValue { Id = 1, ObjectId = 1, TimeCode = 230238423, Value = 18  },
    new PoolingValue { Id = 2, ObjectId = 2, TimeCode = 485230529  , Value = 17  },
    new PoolingValue { Id = 3, ObjectId = 3, TimeCode = 904230428  , Value = 14  },
    new PoolingValue { Id = 4, ObjectId = 3, TimeCode = 234324231  , Value = 250  },
    new PoolingValue { Id = 5, ObjectId = 1, TimeCode = 546542622  , Value = 444  },
    new PoolingValue { Id = 6, ObjectId = 2, TimeCode = 732623211  , Value = 123  },
    new PoolingValue { Id = 7, ObjectId = 5, TimeCode = 908942102  , Value = 1  },
};

Результат будет:

Для того, чтобы понимать группировку - надо внимательно посмотреть, что будет её результатом. Вот что будет после вашего .GroupBy():

Если же вам нужна более сложная логика для расчёта Value, скажем нужно внутренние Grouping отсортировать по убыванию TimeCode и взять Value то будет так:

var result = db.GroupBy(x => x.ObjectId)
               .Select(x => new { ObjectId = x.Key, Value = x.OrderByDescending(y => y.TimeCode).Select(y => y.Value).First()} );

Ну или вот так для красоты:

var result = db.GroupBy(x => x.ObjectId)
               .Select(x => new PoolingValue {
                   ObjectId = x.Key,
                   TimeCode = x.OrderByDescending(y => y.TimeCode).Select(y => y.TimeCode).First(),
                   Value = x.OrderByDescending(y => y.TimeCode).Select(y => y.Value).First()
               });

Отдельно пара слов по чисто EF'ным моментам. Я создал новый объект, анонимный из двух полей, тут можно было создавать либо тот же PollingValue (остальные поля проставятся в ноль), либо так и оставить анонимный класс без модели, просто аккуратнее материализировать на клиент, т.к. будет переход из серверной логики, которая выполнится на сервере в клиентскую. Но возможно, вы это знаете? Вопрос в общем-то был не об этом.

Answer 2

По моему скромному мнению, EF Core - жуткий фреймворк. И если есть возможность, следует отказаться от него.

Попробуйте следующий запрос:

var result = context.PoolingValues
    .GroupBy(x => x.ObjectId)
    .Select(g => g.Max(x => x.TimeCode))
    .Select(x => context.PoolingValues.First(y => y.TimeCode == x))
    //.ToList()
    ;

Он вернёт нужные вам данные.
Предполагается, что все значения TimeCode уникальные.

При этом будет выполнен всего один запрос в БД, что хорошо.
Сгенерированный SQL-запрос выглядит так:

SELECT [t0].[Id], [t0].[ObjectId], [t0].[TimeCode], [t0].[Value]
FROM (
    SELECT MAX([p].[TimeCode]) AS [c]
    FROM [PoolingValues] AS [p]
    GROUP BY [p].[ObjectId]
) AS [t]
OUTER APPLY (
    SELECT TOP(1) [p0].[Id], [p0].[ObjectId], [p0].[TimeCode], [p0].[Value]
    FROM [PoolingValues] AS [p0]
    WHERE [p0].[TimeCode] = [t].[c]
) AS [t0]

Иногда запрос очень сложно выразить с помощью LINQ, но легко написать на обычном SQL. К счастью, EF позволяет выполнять сырые запросы SQL.

string sql =
@"select *
from PoolingValues
where TimeCode in
(
    select max(TimeCode)
    from PoolingValues
    group by ObjectId
)";

var result = context.PoolingValues
    .FromSqlRaw(sql)
    //.ToList()
    ;

Написав сырой запрос, я понял, как можно выразить его в LINQ.

var result = context.PoolingValues
    .Where(x => context
        .PoolingValues
        .GroupBy(y => y.ObjectId)
        .Select(g => g.Max(x => x.TimeCode))
        .Contains(x.TimeCode))
    //.ToList()
    ;

Сгенерированный SQL:

SELECT [p].[Id], [p].[ObjectId], [p].[TimeCode], [p].[Value]
FROM [PoolingValues] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [PoolingValues] AS [p0]
    GROUP BY [p0].[ObjectId]
    HAVING MAX([p0].[TimeCode]) = [p].[TimeCode])

Прошу потестить все три варианта на реальных данных.

P.S. EF Core не так уж плох. :)

БЛОГ НА HUSL

EFCore, запрос последнего значения из одной таблицы по каждому внешнему ключу

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