EF 6：无法生成所需的查询，是否应使用本机 SQL？

Question

我有以下两个具有多对多映射的实体：

ConferenceRoom {ID, Name, IsAccessibleGlobally}  <--- Many-To-Many ---> Department {ID, Name}

• 任何部门都有许多会议室可供该部门的员工使用。
会议室
• 可能由多个部门的员工访问，因为一个部门可能有子部门。 即任何部门也可以访问其子部门的会议室。
• 会议室也可能是全局可访问的，在这种情况下，标志 IsAccessGlobal 设置为 true，并且由于多对多关系生成的表中不存在将其映射到特定部门的记录。

知道由Department和ConferenceRoom之间的多对多关系生成的表称为 DeptCRMapping ，例如，如果我们的给定部门 ID 等于 7，则其员工可访问的会议室将是DeptCRMapping表中配置的会议室加上全局可访问的会议室。

我会为这样的要求编写的SQL查询如下：

SELECT CR.ID, CR.Name
 FROM ConferenceRoom CR
 LEFT JOIN DeptCRMapping DCRM ON CR.ID = DCRM.IDConferenceRoom
 WHERE DCRM.IdDepartment = 7
 OR    CR.IsAccessibleGlobally = 1

这是我的POCO课程：

public class Department
{
    public long Id { get; set; } 
    public string Name { get; set; } 
    public virtual ICollection<ConferenceRoom> ConferenceRooms { get; set; }
}
public class ConferenceRoom
{
    public long Id { get; set; } 
    public string Name { get; set; } 
    public virtual ICollection<Department> Departments { get; set; }
}

我

无法弄清楚我应该编写的 Linq-To-Entities 查询来获取上面提到的 SQL 查询。这是我到目前为止尝试过的：

var left = ctx.ConferenceRooms;
var right = ctx.ConferenceRooms.Where(v => v.Departments.Any(d => d.Id == 7));
var query = from l in left
            from r in right
            where l.Id == r.Id || l.AccesGlobal
            select l;

生成的 sql 是：

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Nom] AS [Nom], 
    [Extent1].[AccesGlobal] AS [AccesGlobal]
    FROM  [dbo].[Verbalisateur] AS [Extent1]
    CROSS JOIN [dbo].[Verbalisateur] AS [Extent2]
    WHERE ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[VerbalisateursJuridiction] AS [Extent3]
        WHERE ([Extent2].[ID] = [Extent3].[IdVerbalisateur]) AND (5 = [Extent3].[IdJuridiction])
    )) AND ([Extent1].[ID] = [Extent2].[ID] OR [Extent1].[AccesGlobal] = 1)

关于我可以做些什么来获得类似于我想要的查询的任何想法。另一方面，你们是否建议在使用ORM时使用本机SQL，至少在查询结果时。谢谢。

<小时 />

编辑

好吧，在未能找到使用 EF 6 执行本机 SQL 查询的方法后，我只是选择了：

ctx.ConferenceRooms.Where(cf => cf.IsAccessibleGlobally || cf.Departments.Any(d => d.Id == 7))

虽然生成的查询不是我所希望的，但至少代码是可读的。

Answer 1

当涉及连接时，尤其是当您经常这样做时，它应该是一个视图。这应该会加快查询时间，并且会明确定义一种查看数据的方式。 我会尽可能多地将您的本机SQL保留在您的数据库中。

Answer 2

目前我没有任何方法可以测试它，但是由于您的查询等效于以下内容（性能应该相似，在我看来，对于新手来说更容易理解）：

SELECT 
    CR.ID, CR.Name
FROM ConferenceRoom CR
WHERE 
    CR.IsAccessibleGlobally = 1
UNION ALL
SELECT 
    CR.ID, CR.Name
FROM DeptCRMapping DCRM
INNER JOIN ConferenceRoom CR ON DCRM.IDConferenceRoom = CR.ID
WHERE 
    DCRM.IdDepartment = 7
    and CR.IsAccessibleGlobally = 0

您可以尝试以下 LINQ 查询（目前我无法测试生成的 SQL），该查询应提供与上述类似的查询：

var query =
    ctx.ConferenceRoom
        .Where(cr => cr.IsAccessibleGlobally == 1)
        .Select(cr => new { cr.ID, cr.Name })
        .Concat(
            ctx.Department
                .Where(d => d.Id == 7)
                .Select(d => 
                    d.ConferenceRooms
                    .Where(cr => cr.IsAccessibleGlobally == 0)
                    .Select(cr => new { cr.ID, cr.Name }))
        )

Answer 3

你快到了：

var rooms = ctx.ConferenceRooms
               .Where(v => v.Departments.Any(d => d.Id == 7)
                        || v.AccesGlobal);