如何使用EF5查询多个记录不存在的记录 [英] how to query a many to many for the absence of records using EF5

问题描述

假设一个纯多个到多个主键是两个外键的组合。例如，Northwind的Employee Privileges有EmployeeID和PrivilegeID。这个m2m明确地将Employees表与特权表相关联。

我需要用EF写一个查询，返回与指定的PrivilegeID不相关联的所有Employees，不管无论它是否与其他特权ID相关。

我有一个EF查询，适用于Sql Server，使用 Except（）但它不适用于Oracle 。

如果我在t-sql中编写查询，我将编写一个使用所需权限过滤员工权限的查询请参见下面的EmpPrivQuery。我将会将EmployPrivQuery加入Employee，如EmployeesMissingPrivileges所示，以完成过滤器。

我们目前拥有具有关系属性的Employee和Privilege模型，以创建多个表。该项目首先使用EF代码创建模式。

可以使用EF连接语法完成此操作吗？可以在没有为EmployeePrivilege表创建实体模型的情况下完成吗？

  EmpPrivQuery：
 SELECT [Employee Privileges]。 [雇员身份]，[雇员权限] [特权身份] 
 FROM [特权] INNER JOIN [员工特权] ON特权[特权ID] = [员工特权] [特权ID] 
 WHERE （（（Privileges。[Privilege Name]）=P3））; 
 
 
 EmployeesMissingPrivileges：
 SELECT EmpPrivQuery。[Employee ID]，Employees。* 
 FROM Employees LEFT JOIN EmpPrivQuery ON Employees.ID = EmpPrivQuery。[Employee ID] 
 WHERE（（（EmpPrivQuery。[Employee ID]）Null））; 
  

原来的帖子，但云的问题。保留注释上下文。
我正在使用Devart dotConnect作为Oracle数据提供者。
Oracle抛出的错误是 ORA-00904：Extent1。EmployeeID：无效标识符。在为适应sql server编写的代码库适用于oracle时，这是我需要解决的常见错误。在大多数情况下，我已经能够通过重新编写查询来将关系转移到使用SelectMany（）的查询主体中的WHERE谓词（其中很容易被动态）的关系。这往往会使发送到数据库服务器的查询变得平坦化，而Oracle似乎要求。

解决方案

您可以使用EF创建基本上与问题中发布的相同的查询。我开始创建一个具有属性的poco模型EmployeePrivilege：int PrivilegeID& int EmployeeID。我没有将它添加到DbContext。

  var EmpPrivQuery = ctx.Privileges 
 .Where（p => p.PrivilegeName ==P3）
 .SelectMany（p => p.Employees，（p，e）=> new EmployeePrivilege {EmployeeID = e.EmployeeID，PrivilegeID = p.PrivilegeID} 
 .Distinct（）; 
 
 var employeesMissingPrivilege = from e in Employees 
加入epP在EmpPrivQuery 
在e.EmployeeID等于epq.EmployeeID 
到可连接的
其中jointable.Count（）== 0 
选择e; 
  

我只是意识到你也可以得到相同的结果，而无需创建poco EmployeePrivilege如下：

  var EmpPrivQuery = ctx.Privileges 
 .Where （p => p.PrivilegeName ==P3）
 .SelectMany（p => p.Employees.Select（e => e.EmployeeID）
 .Distinct（）; 
 
 var employeesMissingPrivilege = from e in Employees 
加入epP在EmpPrivQuery 
在e.EmployeeID等于epq 
到可连接的
其中，jointable.Count（）= = 0 
选择e; 
 

这两个EF查询都返回Employees对Sql Server和Oracle缺少指定的权限（使用Devart的dotConnect for Oracle）

我读过的许多帖子都是使用 DefaultIfEmpty（）来实现左外连接。上面的查询工作，但是，如果使用 DefaultIfEmpty（）更好地使用此结果，请发布。

Assuming a pure many to many where the primary key is the composite of two foreign keys. For example "Employee Privileges" in Northwind has EmployeeID and PrivilegeID. This m2m obviously relates the Employees table to the Privilege table.

I need to write a query with EF that returns all Employees that are NOT associated with a specified PrivilegeID, regardless of whether or not it is related to other PrivilegeIDs.

I have an EF query that works for Sql Server using Except() but it doesn't work for Oracle.

If I were writing the query in t-sql, I would write a query that filters Employee Privileges with the required Privileges "see EmpPrivQuery below". I would then RIGHT join EmpPrivQuery to Employee as seen in EmployeesMissingPrivileges to complete the filter.

We currently have Employee and Privilege models with relationship properties to create the many to many table. The project uses EF code first to create the schema.

Can this be done with EF join syntax? Can it be done without creating an Entity model for the EmployeePrivilege table?

EmpPrivQuery:
SELECT [Employee Privileges].[Employee ID], [Employee Privileges].[Privilege ID] 
FROM [Privileges] INNER JOIN [Employee Privileges] ON Privileges.[Privilege ID] = [Employee Privileges].[Privilege ID]
WHERE (((Privileges.[Privilege Name])="P3"));


EmployeesMissingPrivileges:
SELECT EmpPrivQuery.[Employee ID], Employees.*
FROM Employees LEFT JOIN EmpPrivQuery ON Employees.ID = EmpPrivQuery.[Employee ID]
WHERE (((EmpPrivQuery.[Employee ID]) Is Null));

This block was in original post but clouds the question. Retained for comment context. I'm using Devart dotConnect as as the Oracle data provider. The error that Oracle is throwing is ORA-00904: "Extent1"."EmployeeID": invalid identifier. In adapting the code base that was written for sql server to work against oracle, this is a common error that I've needed to resolve. In most cases I have been able to resolve it by re-writing the query to move relationships to other tables out of the WHERE predicate (where it is very easy to be dynamic) up into the main body of the query using SelectMany(). This tends to flatten the query being sent to the database server and Oracle appears to require that.

解决方案

You can use EF to create basically the same queries posted in the question. I started by creating a poco model EmployeePrivilege with properties: int PrivilegeID & int EmployeeID. I did not add this to the DbContext.

var EmpPrivQuery = ctx.Privileges
                       .Where(p => p.PrivilegeName == "P3")
                       .SelectMany(p => p.Employees, (p, e) => new EmployeePrivilege{EmployeeID = e.EmployeeID, PrivilegeID = p.PrivilegeID}
                       .Distinct();

var employeesMissingPrivilege = from e in Employees
                                join epq in EmpPrivQuery
                                on e.EmployeeID equals epq.EmployeeID
                                into jointable
                                where jointable.Count()==0
                                select e;

I just realized you can also get the same result without creating the poco EmployeePrivilege as follows:

var EmpPrivQuery = ctx.Privileges
                        .Where(p => p.PrivilegeName == "P3")
                        .SelectMany(p => p.Employees.Select(e => e.EmployeeID)
                        .Distinct();

var employeesMissingPrivilege = from e in Employees
                                join epq in EmpPrivQuery
                                on e.EmployeeID equals epq
                                into jointable
                                where jointable.Count()==0
                                select e;

Both of these EF queries return Employees missing specified privileges against both Sql Server and Oracle (using Devart's dotConnect for Oracle).

Many posts that I read referred to using DefaultIfEmpty() to achieve a left outer join. The queries above work, however, please post if there is a better way to this result using DefaultIfEmpty().

这篇关于如何使用EF5查询多个记录不存在的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！