如何使用Linq和Entity Framework来连接两个可连接对象? [英] How do I use Linq and Entity Framework to join two jointables?

问题描述

我有一个非常规范化的数据库，并且我试图将两个联接表联接在一起.

I have a very normalized database, and I'm trying to join two join tables together.

我的目标是仅显示用户有权访问的文档.我正在使用Entity Framework，并为下表设置了几个外键:

My goal is to only show documents that the user has permission to. I'm using Entity Framework, and have several foreign keys set up for the following tables:

关系(外键)

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查询的故事.

I've heard of many stories about how Linq to EF alters the SQL query in a way that is sub-optimal for performance.

以下是是似乎最常见的.Union运算符示例适用于我在做什么.我可以简单地获取当前用户所属的组的列表，然后发出此查询的修改版本:

Here is is the .Union operator sample that seems most applicable to what I'm doing. I could simply get a list of groups the current user is a member of and issue a modified version of this query:

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); 
    } 
}

问题

• 如何查看EF生成的SQL查询?
• 解决此问题(如果有)的更好方法是什么

• How do I view the SQL query that EF generates?
• What is a better way to approach this problem (if any)

推荐答案

如果您具有所有键和外键的导航属性，则不带Intersect的替代查询将是:

If you have navigation properties for all your keys and foreign keys an alternative query without an Intersect would be:

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

(使用键= givenUserId过滤至少 个组中具有至少个用户的所有文档")

("Filter all documents which are in at least one group which has at least one user with the key = givenUserId")

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

I don't know if this will be better with respect to performance.

在EF 4.1中，您可以通过以下方式简单地检查生成的SQL:

In EF 4.1 you can inspect the generated SQL simply by:

var sql = query.ToString();

修改

我对模型的外观的了解如下:

My understanding how your model would look is the following:

三个具有相应表的实体:

Three entities with corresponding tables:

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之间也是如此:

And between User and Group a many-to-many relationship and between Group and XDocument as well:

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在内部管理这些表).

In this model and mapping the query described above should be possible. There is no need to access the join tables directly (and you can't actually access them via LINQ to Entities, EF manages those tables internally).

这篇关于如何使用Linq和Entity Framework来连接两个可连接对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！