为什么我的实体框架创建多个查询而不是单个UNION查询? [英] Why my Entity Framework creates several queries instead of a single UNION query?

问题描述

我使用Entity Framework Core 2.0.1.在我的EF查询中，我需要将几个简单查询合并到一个UNION查询中.因此，对于一个非常基本的示例，我有几个类似的查询

I use Entity Framework Core 2.0.1. In my EF query I need to combine several simple queries into a single UNION query. So, for a very basic example, I have a couple of similar queries

using (var dbc = new ItemsDbContext("some-connection-string"))
{
    var q1 = dbc.Items.Where(a => a.ItemType == "A");
    var q2 = dbc.Items.Where(a => a.ItemType == "B");

    var myList = q1.Union(q2).ToList();
}

我希望对我的SQL Server数据库执行这样的SQL语句作为一个查询:

I would like such SQL statement to be executed, as a single query, against my SQL Server database:

SELECT I.*
    FROM [dbo].[Items]
    WHERE I.ItemType = N'A'
UNION
SELECT I.*
    FROM [dbo].[Items]
    WHERE I.ItemType = N'B'

但是，正如我在SQL Server Profiler中看到的那样，将执行两个单独的查询:

However, as much as I see in SQL Server Profiler, two separate queries are executed:

查询1:

SELECT I.*
    FROM [dbo].[Items]
    WHERE I.ItemType = N'A'

查询2:

SELECT I.*
    FROM [dbo].[Items]
    WHERE I.ItemType = N'B'

这两个单独查询的结果随后由Entity Framework合并.但这是在调用代码中完成的，而不是在SQL Server方面完成的.

Result of this two separate queries is later union'ed by Entity Framework. But it is done in the calling code, not on SQL Server's side.

如何使Entity Framework Core生成UNION而不是几个查询?

How can I make Entity Framework Core generate UNION instead of several queries?

推荐答案

根据此GitHub问题 EF Core 2.0.1尚未完全实现服务器端UNION.服务器端UNION计划在EF Core v.2.1中实现，直到EF将执行本地联合(在内存中).

According to this GitHub issue server-side UNION is simply not implemented yet in EF Core 2.0.1. Server-side UNION is planned to be implemented in EF Core v. 2.1, till then EF will perform local Union (in memory).

这篇关于为什么我的实体框架创建多个查询而不是单个UNION查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！