Entity Framework/SQL Server. Выбрать коллекцию данных на основе массива из Id, где каждая запись будет уникальна и соответствовать условию

Question

Есть таблица ItemHistory

 -----------------------------------
| Id | ItemId | DateTimeUtc         |
|-----------------------------------|
| 1  | 1      | 2021-01-01 00:00:00 |
|----|------------------------------|
| 2  | 2      | 2021-01-01 00:00:00 |
|----|------------------------------|
| 3  | 2      | 2021-01-01 12:50:00 |
|----|------------------------------|
| 4  | 1      | 2021-01-02 17:00:00 |
|----|------------------------------|
| 5  | 3      | 2021-02-24 16:59:00 |
|----|------------------------------|
|... |...     | ...                 |
------------------------------------

Необходимо реализовать выборку данных, которая будет принимать коллекцию ItemId и время dateTimeUtc, и будет возвращать для каждого Item наиболее близкую запись по времени (т.е. чтобы разница Abs(DateTimeUtc - dateTimeUtc) была минимальной). Как я понимаю, нужно сгрупировать данные по ItemId и отсортировать в каждой группе по Math.Abs((x.DateTimeUtc - dateTimeUtc).TotalMilliseconds и выбрать первую запись из каждой группы. Мои жалкие попытки что-либо сделать:

var result = context.Set<ItemHistory>()
    .Where(x => itemIds.Contains(x.VehicleId))
    .GroupBy(x => x.ItemId)
    .OrderBy(x => Math.Abs((x.DateTimeUtc - dateTimeUtc).TotalMilliseconds))
    .ToList();

Но это, понятное дело, не компилируется (на строке OrderBy ошибка). В группировках не силен, как и в принципе в EntityFramework. Поэтому прошу помощи.

Принимаю ответы как на c#, так и на sql

P.S. Заголовок вопроса было сложно сформулировать.

Answer 1

Я EF не знаю, но если бы надо было сделать с помощью обычного Linq, то получилось бы что-то такое

.GroupBy(x => x.ItemId)
.Select(g => new
{
    ItemId = g.Key,
    DateTimeUtc = g.OrderBy(x => Math.Abs((x.DateTimeUtc - dateTimeUtc).TotalSeconds))
        .First().DateTimeUtc
}).ToList();

Или даже так

.GroupBy(x => x.ItemId)
.Select(g => g.OrderBy(x => Math.Abs((x.DateTimeUtc - dateTimeUtc).TotalSeconds)).First())
.ToList();

Answer 2

Entity Framework не может обработать все выражения, допустимые в коде C#. В частности, разность типов DateTime нужно реализовывать с помощью методов класса DbFunctions.
Дополнительно необходимо приведение к int, иначе там маячит int?, который не принимает Math.Abs.
И метод First необходимо заменить на FirstOrDefault. EF слишком привередлив.

var result = context.Set<ItemHistory>()
    .Where(x => itemIds.Contains(x.ItemId))
    .GroupBy(x => x.ItemId)
    .Select(g => g.OrderBy(x => Math.Abs((int)DbFunctions.DiffMilliseconds(x.DateTimeUtc, dateTimeUtc))).FirstOrDefault())
    .ToList();

В комментариях вы пишете, что данные выгружаются долго.
Предлагаю попробовать следующие запросы.

var result = context.Set<ItemHistory>()
    .Where(x => itemIds.Contains(x.ItemId))
    .GroupBy(x => x.ItemId)
    .Select(g => g.FirstOrDefault(x =>
        Math.Abs((int)DbFunctions.DiffMilliseconds(x.DateTimeUtc, dateTimeUtc)) ==
        g.Min(y => Math.Abs((int)DbFunctions.DiffMilliseconds(y.DateTimeUtc, dateTimeUtc)))))
    .ToList();

или

var result = (from x in context.Set<ItemHistory>()
              where itemIds.Contains(x.ItemId)
              group x by x.ItemId into g
              let min = g.Min(x => Math.Abs((int)DbFunctions.DiffMilliseconds(x.DateTimeUtc, dateTimeUtc)))
              select g.FirstOrDefault(x => Math.Abs((int)DbFunctions.DiffMilliseconds(x.DateTimeUtc, dateTimeUtc)) == min))
             .ToList();

Выглядят они более страшно, но, возможно, окажутся быстрей, так как в них нет сортировки.

Советую включить логирование запросов:

context.Database.Initialize(false);
context.Database.Log += Console.Write;

Первая строка выполнит инициализацию контекста перед включением логирования и в лог не попадёт неважный для нас sql-код.

Все три запроса выглядят громоздкими и неэффективными. Я ожидал, что сгенерированный sql для второго и третьего окажется одинаковым, но оказалось, что это не так.

Попробуйте их на больших объёмах данных и отпишитесь о результатах.

БЛОГ НА HUSL

Entity Framework/SQL Server. Выбрать коллекцию данных на основе массива из Id, где каждая запись будет уникальна и соответствовать условию

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