EF Core: CROSS APPLY (или быстрый аналог)

@kotelok · Регистрация: 08.08.2014

Студворк — интернет-сервис помощи студентам

Подскажите, как переписать этот запрос на LINQ (method-based), чтобы EF-Core построил для него эффективный запрос? Т.е. чтобы не выкачивал все данные на сторону клиента и не выполнял по одному отдельному detail-подзапросу на каждую master-запись.

Если из запроса не очень понятно, то задача следующая - выбрать все master-записи у которых есть detail-записи и последняя из detail-записей (в данном случае по ID) удовлетворяет определённым условиям. Не любая, а именно последняя, т.е. если detail-запись удовлетворяет условиям, но не является последней (в рамках данной master-записи), то она не учитывается. При этом в результирующем наборе нужны как данные из master, так и некоторые поля из последней detail-записи, но не все, лишь малая часть полей, т.е. результат нужно как-то так замапить на кастомный объект, чтобы EF в сгенерированном запросе явно перечислил нужные колонки, а не выбирал все.

На данный момент все подобные запросы в проекте выполняются напрямую, но хотелось бы перевести их на LINQ.

запрос

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT customer.customer_id
, customer.name
, last_customer_data.phone
, last_customer_data.mail
 
FROM customer CROSS apply
(
    SELECT top 1 customer_data.* 
    FROM customer_data 
 
    WHERE customer_data.customer_id = customer.customer_id 
    
    ORDER BY customer_data.customer_data_id DESC
) AS last_customer_data
 
WHERE customer.age >= 18
AND (last_customer_data.mail LIKE '%john%' OR last_customer_data.phone LIKE '%2%')

структура БД

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE customer
(
    customer_id INT NOT NULL,
    name VARCHAR(240) NOT NULL,
    age INT NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (customer_id)
)
 
CREATE TABLE customer_data
(
    customer_data_id INT NOT NULL,
    customer_id INT NOT NULL,
    phone VARCHAR(50) NOT NULL,
    mail VARCHAR(200) NOT NULL,
    CONSTRAINT pk_customer_data PRIMARY KEY (customer_data_id),
    CONSTRAINT fk_customer_to_customer_data FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
)

тестовые данные

SQL
1
2
3
4
5
6
7
8
INSERT INTO customer (customer_id, name, age) VALUES (1, 'Kukish', 42)
INSERT INTO customer (customer_id, name, age) VALUES (2, 'Pufik', 24)
INSERT INTO customer (customer_id, name, age) VALUES (3, 'Iriska', 28)
 
INSERT INTO customer_data (customer_data_id, customer_id, phone, mail) VALUES (1, 1, '111-22-44', 'john@gmail.com')
INSERT INTO customer_data (customer_data_id, customer_id, phone, mail) VALUES (2, 1, '111-22-44', 'kukish@gmail.com')
INSERT INTO customer_data (customer_data_id, customer_id, phone, mail) VALUES (3, 3, '888-44-12', 'iriska@yandex.ru')
INSERT INTO customer_data (customer_data_id, customer_id, phone, mail) VALUES (4, 3, '666-12-88', 'iriska@yandex.ru')

@Usaga · 16.04.2019, 08:18

kotelok, хороший вопрос, интересный. И поставлен правильно.

Про EF Core не скажу, ибо не доводилось его нормально потыкать, но EF6 такое может вполне себе элегантно и эффективно.

Контекст и модели:

Кликните здесь для просмотра всего текста

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
    public class TestContext : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        public IDbSet<CustomerData> CustomerData { get; set; }
 
        public TestContext() : base("server=localhost;database=Experimental;integrated security=true")
        {
            Database.SetInitializer<TestContext>(null);
        }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
 
            modelBuilder.Entity<Customer>().HasKey(x => x.customer_id);
            modelBuilder.Entity<Customer>().ToTable("customer");
 
            modelBuilder.Entity<CustomerData>().HasKey(x => x.customer_data_id);
            modelBuilder.Entity<CustomerData>().ToTable("customer_data");
 
        }
    }
 
    public class CustomerData
    {
        public int customer_data_id { get; set; }
 
        public int customer_id { get; set; }
 
        public Customer Customer { get; set; }
 
        public string phone { get; set; }
 
        public string mail { get; set; }
    }
 
    public class Customer
    {
        public int customer_id { get; set; }
 
        public string name { get; set; }
 
        public int age { get; set; }
 
        public IList<CustomerData> Data { get; set; }
    }

LINQ-запрос:

Кликните здесь для просмотра всего текста

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
using System;
using System.Linq;
 
namespace EfExperiments
{
    class TestDto
    {
        public int CustomerId { get; set; }
 
        public string Name { get; set; }
 
        public string Phone { get; set; }
 
        public string Mail { get; set; }
    }
 
    class Program
    {
        static void Main()
        {
            using (var con = new TestContext())
            {
                var cust = con.Customers
                    .Select(x => new
                    {
                        customer = x,
                        lastData = x.Data.OrderByDescending(y => y.customer_data_id).FirstOrDefault()
                    })
                    .Where(x => x.customer.age >= 18)
                    .Where(x => x.lastData.mail.Contains("john") || x.lastData.phone.Contains("2"))
                    .Select(x => new TestDto
                    {
                        CustomerId = x.customer.customer_id,
                        Name = x.customer.name,
                        Mail = x.lastData.mail,
                        Phone = x.lastData.phone
                    })
                    .ToList()
                    ;
 
                foreach (var item in cust)
                {
                    Console.WriteLine($"{item.CustomerId} {item.Name} {item.Phone} {item.Mail}");
                }
            }
 
            Console.WriteLine("done");
            Console.Read();
        }
    }
}

И запрос, который генерирует EF6:

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    [Extent1].[customer_id] AS [customer_id],
    [Extent1].[name] AS [name],
    [Limit1].[mail] AS [mail],
    [Limit1].[phone] AS [phone]
    FROM  [dbo].[customer] AS [Extent1]
    OUTER APPLY  (SELECT TOP (1) [Project1].[phone] AS [phone], [Project1].[mail] AS [mail]
        FROM ( SELECT
            [Extent2].[customer_data_id] AS [customer_data_id],
            [Extent2].[phone] AS [phone],
            [Extent2].[mail] AS [mail]
            FROM [dbo].[customer_data] AS [Extent2]
            WHERE [Extent1].[customer_id] = [Extent2].[customer_id]
        )  AS [Project1]
        ORDER BY [Project1].[customer_data_id] DESC ) AS [Limit1]
    WHERE ([Extent1].[age] >= 18) AND (([Limit1].[mail] LIKE N'%john%') OR ([Limit1].[phone] LIKE N'%2%'))

@kotelok · 16.04.2019, 09:03 **[ТС]**

Видимо, EF-Core требуются либо какие-то дополнительные настройки модели, либо иной синтаксис. Вот эту часть:

C#
1
2
3
4
5
6
var result = ctx.Customers
    .Select(x => new
    {
        Customer = x,
        LastData = x.CustomerDatas.OrderByDescending(y => y.CustomerDataId).FirstOrDefault()
    }).ToList();

Он транслирует в простой запрос к master-таблице:

SQL
1
2
SELECT [x].[customer_id], [x].[age], [x].[name]
FROM [customer] AS [x]

И в группу запросов (по одному на каждую master-запись) к detail-таблице:

SQL
1
2
3
4
SELECT TOP(1) [y].[customer_data_id], [y].[customer_id], [y].[mail], [y].[phone]
FROM [customer_data] AS [y]
WHERE @_outer_CustomerId = [y].[customer_id]
ORDER BY [y].[customer_data_id] DESC

На всякий случай явно прописал связь, результат тот же:

C#
1
builder.Entity<Customer>().HasMany(c => c.CustomerDatas).WithOne(d => d.Customer);

@Usaga · 16.04.2019, 09:18

kotelok, EF Core ещё сыроватый и каких-то вещей он может тупо не уметь. Так, что не удивительно, что он такую фигню делает)

Я вам могу посоветовать вытащить нужные корневые записи customer сразу отсеянные по возрасту. Потом вытащить все связанные записи сгруппированные по customer_id, чьи customer_data_id = MAX в группе. И в оперативке уже всё это сджойнить.

Элегентным это не назовёшь.

Добавлено через 1 минуту
Блин, интересный вопрос. Дома EF Core воткну и попробую добиться красивого запроса.

@kolorotur · 16.04.2019, 16:22

Вроде фачит, хоть и запрос не самый лучший генерирует.

Нугеты

EF Core: CROSS APPLY (или быстрый аналог)

Контекст и модель

Спер у товарища Usaga.

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
public class TestContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
 
    public DbSet<CustomerData> CustomerData { get; set; }
 
    public TestContext()
    {
 
    }
    public TestContext(DbContextOptions<TestContext> options) : base(options)
    {
 
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
 
        var c = modelBuilder.Entity<Customer>();
        c.HasKey(x => x.customer_id);
        c.ToTable("customer");
 
        c.HasData(
            new Customer
            {
                customer_id = 1,
                name = "Kukish",
                age = 42
            },
            new Customer
            {
                customer_id = 2,
                name = "Pufik",
                age = 24
            },
            new Customer
            {
                customer_id = 3,
                name = "Iriska",
                age = 28
            });
 
        var cd = modelBuilder.Entity<CustomerData>();
        cd.HasKey(x => x.customer_data_id);
        cd.ToTable("customer_data");
 
        cd.HasData(
            new CustomerData
            {
                customer_data_id = 1,
                customer_id = 1,
                phone = "111-22-44",
                mail = "john@gmail.com"
            },
            new CustomerData
            {
                customer_data_id = 2,
                customer_id = 1,
                phone = "111-22-44",
                mail = "kukish@gmail.com"
            },
            new CustomerData
            {
                customer_data_id = 3,
                customer_id = 3,
                phone = "888-44-12",
                mail = "iriska@yandex.ru"
            },
            new CustomerData
            {
                customer_data_id = 4,
                customer_id = 3,
                phone = "666-12-88",
                mail = "iriska@yandex.ru"
            });
    }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=TestDb;");
        optionsBuilder.UseLoggerFactory(new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) }));
        base.OnConfiguring(optionsBuilder);
    }
}
 
public class CustomerData
{
    public int customer_data_id { get; set; }
 
    public int customer_id { get; set; }
 
    public Customer Customer { get; set; }
 
    public string phone { get; set; }
 
    public string mail { get; set; }
}
 
public class Customer
{
    public int customer_id { get; set; }
 
    public string name { get; set; }
 
    public int age { get; set; }
 
    public IList<CustomerData> Data { get; set; }
}

Запрос

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
static async Task Main()
{
    using (var db = new TestContext())
    {
        var query = from customer in db.Customers
                    let lastDetail = customer.Data.OrderByDescending(d => d.customer_data_id).FirstOrDefault()
                    where customer.age >= 18
                    where lastDetail.mail.Contains("john") || lastDetail.phone.Contains("2")
                    select new
                    {
                        customer_id = customer.customer_id,
                        name = customer.name,
                        phone = lastDetail.phone,
                        mail = lastDetail.mail
                    };
 
        var item = await query.ToListAsync();
    }
}

SQL

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
      SELECT [customer].[customer_id], [customer].[name], (
          SELECT TOP(1) [d1].[phone]
          FROM [customer_data] AS [d1]
          WHERE [customer].[customer_id] = [d1].[customer_id]
          ORDER BY [d1].[customer_data_id] DESC
      ) AS [phone], (
          SELECT TOP(1) [d2].[mail]
          FROM [customer_data] AS [d2]
          WHERE [customer].[customer_id] = [d2].[customer_id]
          ORDER BY [d2].[customer_data_id] DESC
      ) AS [mail]
      FROM [customer] AS [customer]
      WHERE ([customer].[age] >= 18) AND ((CHARINDEX(N'john', (
          SELECT TOP(1) [d].[mail]
          FROM [customer_data] AS [d]
          WHERE [customer].[customer_id] = [d].[customer_id]
          ORDER BY [d].[customer_data_id] DESC
      )) > 0) OR (CHARINDEX(N'2', (
          SELECT TOP(1) [d0].[phone]
          FROM [customer_data] AS [d0]
          WHERE [customer].[customer_id] = [d0].[customer_id]
          ORDER BY [d0].[customer_data_id] DESC
      )) > 0))

План

Оригинальный план

Запрос, конечно, кривой делает.

@Usaga · 16.04.2019, 18:18

Мда... Лучше бы оно эксепшен кинуло, чем такой запрос молча выдавать, под видом того, что работает...

Добавлено через 1 час 22 минуты
Ну, похоже, что ребята именно в этом направлении и двигаются:

Старое поведение

До выхода версии 3.0, если системе EF Core не удавалось преобразовать выражение, являющееся частью запроса, в код SQL или параметр, она автоматически рассчитывала это выражение на клиенте. По умолчанию вычисление на клиенте потенциально ресурсоемких выражений вызывало лишь отображение предупреждения.

Новое поведение

Начиная с версии 3.0 система EF Core разрешает вычислять на клиенте только выражения в высокоуровневой проекции (последний вызов Select() в запросе). Если выражения в любой другой части запроса невозможно преобразовать в код SQL или параметр, возникает исключение.

Начиная с версии 3.0 EF Core не начнёт лучше работать, просто станет кидать исключения, когда не сможет родить нормальный запрос.

@kotelok · 16.04.2019, 18:44 **[ТС]**

Сообщение от Usaga

Начиная с версии 3.0 EF Core не начнёт лучше работать, просто станет кидать исключения, когда не сможет родить нормальный запрос

Куда больше беспокоит, чтобы все вот такие хитрые запросы, после очередного обновления не стали внезапно генерировать менее эффективный SQL. Тихонько так, незаметно.

Думаю, может и в самом деле перейти на на смешанный вариант - EF для базовых CRUD-операций, а всё остальное по-старинке на чистом SQL. Понадёжнее как-то выглядит, да и, в случае чего, перевести проект на другой ORM будет попроще.

@Usaga · 16.04.2019, 18:53

kotelok, лучше посмотрите в сторону Linq2Db. Не такая навороченная ORM как EF, но позволяет более низкоуровневые вещи делать и при этом всё равно оставаться на LINQ-запросах. А на голый SQL переходить надо только в самом крайнем случае, когда вы железно уверены, что иначе никак.

@amr-now · 18.04.2019, 14:27

Всем здравствуйте.
kotelok, имхо такой запрос поэффективней будет:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
;WITH last_customer_data
AS (
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER(partition BY customer_id ORDER BY customer_data_id DESC) AS rn 
        FROM dbo.customer_data
    ) AS t
    WHERE rn = 1
) 
SELECT 
    customer.customer_id,
    customer.name,
    last_customer_data.phone,
    last_customer_data.mail
FROM 
    customer
JOIN 
    last_customer_data ON customer.customer_id = last_customer_data.customer_id
WHERE 
    customer.age >= 18
    AND (last_customer_data.mail LIKE '%john%' OR last_customer_data.phone LIKE '%2%')

@kotelok · 18.04.2019, 14:31 **[ТС]**

amr-now
Да, спасибо. Изначально в приложении он именно через CTE был сделан. На 'APPLY' был переписан в попытках мигрировать на EF (что потребовало добавить в используемые таблицы ещё несколько индексов).

@Usaga · 18.04.2019, 18:34

kotelok, смею заметить, что Linq2Db поддерживает CTE.

@amr-now · 18.04.2019, 19:34

Usaga, CTE сейчас, если честно сказать, не актуален.
Современные версии MS SQL Server умеют его джойнить в плане запроса в классическом понимании, уравнивая скорость исполнения.

Новые блоги и статьи Все статьи Все блоги /
Krabik - рыболовный бот для WoW 3.3.5a AmbA 21.03.2026 без регистрации и смс. Это не торговля, приложение не содержит рекламы. Выполняет свою непосредственную задачу - автоматизацию рыбалки в WoW - и ничего более. Однако если админы будут против -. . .	Программный отбор значения справочника Maks 21.03.2026 Процедура ВодителиНачалоВыбора(Элемент, ДанныеВыбора, ВыборДобавлением, СтандартнаяОбработка) / / Отключаем стандартную обработку (стандартное открытие формы выбора без фильтров) . . .	Переходник USB-CAN-GPIO Eddy_Em 20.03.2026 Достаточно давно на работе возникла необходимость в переходнике CAN-USB с гальваноразвязкой, оный и был разработан. Однако, все меня терзала совесть, что аж 48-ногий МК используется так тупо: просто. . .	Оттенки серого Argus19 18.03.2026 Оттенки серого Нашёл в интернете 3 прекрасных модуля: Модуль класса открытия диалога открытия/ сохранения файла на Win32 API; Модуль класса быстрого перекодирования цветного изображения в оттенки. . .
SDL3 для Desktop (MinGW): Рисуем цветные прямоугольники с помощью рисовальщика SDL3 на Си и C++ 8Observer8 17.03.2026 Содержание блога Финальные проекты на Си и на C++: finish-rectangles-sdl3-c. zip finish-rectangles-sdl3-cpp. zip	Символические и жёсткие ссылки в Linux. algri14 15.03.2026 Существует два типа ссылок — символические и жёсткие. Ссылка в Linux — это запись в каталоге, которая может указывать либо на inode «файла-ИСТОЧНИКА», тогда это будет «жёсткая ссылка» (hard link),. . .	[Owen Logic] Поддержание уровня воды в резервуаре количеством включённых насосов: моделирование и выбор регулятора ФедосеевПавел 14.03.2026 Поддержание уровня воды в резервуаре количеством включённых насосов: моделирование и выбор регулятора ВВЕДЕНИЕ Выполняя задание на управление насосной группой заполнения резервуара,. . .	делаю науч статью по влиянию грибов на сукцессию anaschu 13.03.2026 прикрепляю статью

@Usaga 14308 / 9389 / 1355 Регистрация: 21.01.2016 Сообщений: 35,410
	16.04.2019, 09:18
	kotelok, EF Core ещё сыроватый и каких-то вещей он может тупо не уметь. Так, что не удивительно, что он такую фигню делает) Я вам могу посоветовать вытащить нужные корневые записи `customer` сразу отсеянные по возрасту. Потом вытащить все связанные записи сгруппированные по `customer_id`, чьи `customer_data_id` = MAX в группе. И в оперативке уже всё это сджойнить. Элегентным это не назовёшь. Добавлено через 1 минуту Блин, интересный вопрос. Дома EF Core воткну и попробую добиться красивого запроса. 0

@Usaga 14308 / 9389 / 1355 Регистрация: 21.01.2016 Сообщений: 35,410
	16.04.2019, 18:53
	kotelok, лучше посмотрите в сторону Linq2Db. Не такая навороченная ORM как EF, но позволяет более низкоуровневые вещи делать и при этом всё равно оставаться на LINQ-запросах. А на голый SQL переходить надо только в самом крайнем случае, когда вы железно уверены, что иначе никак. 1

@kotelok 1341 / 920 / 265 Регистрация: 08.08.2014 Сообщений: 2,768
	18.04.2019, 14:31 [ТС]
	amr-now Да, спасибо. Изначально в приложении он именно через CTE был сделан. На 'APPLY' был переписан в попытках мигрировать на EF (что потребовало добавить в используемые таблицы ещё несколько индексов). 0

@Usaga 14308 / 9389 / 1355 Регистрация: 21.01.2016 Сообщений: 35,410
	18.04.2019, 18:34
	kotelok, смею заметить, что Linq2Db поддерживает CTE. 0

@amr-now 6496 / 3907 / 2006 Регистрация: 14.06.2018 Сообщений: 6,781
	18.04.2019, 19:34
	Usaga, CTE сейчас, если честно сказать, не актуален. Современные версии MS SQL Server умеют его джойнить в плане запроса в классическом понимании, уравнивая скорость исполнения. 0

EF Core: CROSS APPLY (или быстрый аналог)

Решение