如何使用Linq和Entity Framework来加入两个连接表？

Question

我有一个非常规范化的数据库，我试图将两个连接表连接在一起。

我的目标是仅显示用户有权访问的文档。我使用实体框架，并有几个外键设置为下表：

关系（外键）

Users --------------------------------------------- 
                | 
          UserGroupMembership (UserID, GroupID) 
                   | 
                   | 
Groups ----- -------------------------------------------------| 
    | 
    | 
    |---------------------------------------------------------| 
                   | 
                   | 
XDocuments    XDocumentSecurity (DocumentID, GroupID) 
     |           | 
     --------------------------------------------- 

表定义

public partial class Users : EntityObject 
{ 
    public int UserID {get;set;} //PK 
    public string UserDisplayName {get;set;} 
    public DateTime CreateDate {get;set;} 
    public DateTime LoginDate {get;set;} 
} 

public partial class Groups : EntityObject 
{ 
    public int GroupID {get;set;} //PK 
    public string GroupDisplayName {get;set;} 
    public DateTime CreateDate {get;set;} 
    public DateTime LoginDate {get;set;} 
} 


public partial class UserGroupMembership: EntityObject // JoinTable 
{ 
    public int UserID {get;set;} //PK 
    public int GroupID {get;set;} //PK 

    // Not sure if this extra columns below causes an issue 
    public bool CanView {get;set;} 
    public bool CanDelete {get;set;} 
    public bool CanUpdate {get;set;} 
    public DateTime CreateDate {get;set;} 
} 

public partial class XDocumentSecurity : EntityObject // JoinTable 
{ 
    public int DocumentID {get;set;} //FK 
    public int GroupID {get;set;} //FK 

    public DateTime CreateDate {get;set;} // Not sure if this extra column causes an issue 
} 

public partial class XDocuments : EntityObject 
{ 
    public int DocumentID {get;set;} //PK 
    public string URL {get;set;} 
    public DateTime CreateDate {get;set;} 
} 

我我听说过很多关于Linq to EF如何以一种性能次优的方式改变SQL查询的故事。

这是is the .Union operator sample，这似乎最适用于我在做什么。我可以简单地获得组列表当前用户中的一员，并发出此查询的修改版本：

public void Linq50() 
{ 
    int[] numbersA = { 0, 2, 4, 5, 6, 8, 9 }; 
    int[] numbersB = { 1, 3, 5, 7, 8 }; 

    var commonNumbers = numbersA.Intersect(numbersB); 

    Console.WriteLine("Common numbers shared by both arrays:"); 
    foreach (var n in commonNumbers) 
    { 
     Console.WriteLine(n); 
    } 
} 

问题

• 如何查看SQL查询EF生成？
• 什么是更好的方式来解决这个问题（如果有的话）

Answer 1

如果您对您的所有键和外键导航属性没有Intersect可选的查询应该是：

var query = context.XDocuments 
    .Where(d => d.Groups.Any(g => g.Users.Any(u => u.UserID == givenUserId))); 

（“过滤器，其是在所有文档至少具有至少与钥匙一个用户= givenUserId一个基团”）

我不知道这是否会在性能方面更好。

在EF 4.1，你可以检查生成的SQL只需：

var sql = query.ToString(); 

编辑

我理解你的模式将看怎么是这样的：

与相应的表

三个实体：

public class User 
{ 
    public int UserID { get; set; } 
    public ICollection<Group> Groups { get; set; } 
} 

public class Group 
{ 
    public int GroupID { get; set; } 
    public ICollection<User> Users { get; set; } 
    public ICollection<XDocument> Documents { get; set; } 
} 

public class XDocument 
{ 
    public int DocumentID { get; set; } 
    public ICollection<Group> Groups { get; set; } 
} 

而User和Group之间的多对多的关系和Group之间XDocument以及：

modelBuilder.Entity<User>() 
    .HasMany(u => u.Groups) 
    .WithMany(g => g.Users) 
    .Map(c => 
    { 
     c.MapLeftKey("UserID"); 
     c.MapRightKey("GroupID"); 
     c.ToTable("UserGroupMembership"); // join table name, no entity 
    }); 

modelBuilder.Entity<XDocument>() 
    .HasMany(d => d.Groups) 
    .WithMany(g => g.Documents) 
    .Map(c => 
    { 
     c.MapLeftKey("DocumentID"); 
     c.MapRightKey("GroupID"); 
     c.ToTable("XDocumentSecurity"); // join table name, no entity 
    }); 

在此模型中，并映射上述查询应该是可能的。没有必要直接访问连接表（并且实际上不能通过LINQ to Entities访问它们，EF在内部管理这些表）。

Answer 2

您还可以查看由EF 4.1生成的SQL使用或者

• SQL事件探查器
• LINQPad

虽然，在这个时候，你需要使用LINQPad beta为EF 4.1。

关于你的第二个问题，我相信你的查询会翻译得很好。使用LINQPad检查SQL，下面的查询

var a1 = Addresses.Where(a => a.City.ToUpper().EndsWith("L")).Select(a => a.AddressID); 
var a2 = Addresses.Where(a => a.City.ToUpper().StartsWith("B")).Select(a => a.AddressID); 

var x1 = a1.Intersect(a2); 

转化为

SELECT 
[Intersect1].[AddressID] AS [C1] 
FROM (SELECT 
    [Extent1].[AddressID] AS [AddressID] 
    FROM [Person].[Address] AS [Extent1] 
    WHERE UPPER([Extent1].[City]) LIKE N'%L' 
INTERSECT 
    SELECT 
    [Extent2].[AddressID] AS [AddressID] 
    FROM [Person].[Address] AS [Extent2] 
    WHERE UPPER([Extent2].[City]) LIKE N'B%') AS [Intersect1] 

---

我觉得@ Slauma的建议使用的导航proeprties是去，如果您的模型支持的方式。

不过，得到LINQPad--你不会后悔的:)