2

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

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:

Relationships (Foreign Keys)

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

Table Definition

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

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

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

Question

  • How do I view the SQL query that EF generates?
  • What is a better way to approach this problem (if any)
makerofthings7
  • 60,103
  • 53
  • 215
  • 448
  • Oh, after your Edit I am confused now. Why doesn't `XDocumentSecurity.GroupID` refer to `Groups.GroupID` instead of `UserGroupMembership.GroupID`? I'm wondering how you can define that in SQL at all because there is no (full) primary key or unique column involved in that relationship (which is required - at least in SQL Server). – Slauma Nov 05 '11 at 00:24
  • @Slauma - You're right, I corrected the FK diagram. – makerofthings7 Nov 05 '11 at 00:53
  • What is `EntityObject`? Is that your own base class or is it the EntityObject class from EF? I'm wondering because your question is tagged with EF 4.1. If you are using `DbContext` then you cannot derive from EF's `EntityObject`. And yes, the additional columns in the join table are a problem if you want to define a many-to-many relationship. You basically have to create two one-to-many relationships for each many-to-many. Final question: Why doesn't your model have any navigation properties to define the relationships between the entities? – Slauma Nov 05 '11 at 13:19
  • @Slauma EntityObject is from EF. I'm not using Code First and I'm working with the model directly. No repository or anything like that. I'm looking at objects generated by the wizard. Also I realized that my install may not be the latest version of EF so I updated to EF 4.1 Update 1. There are navigation properties, I'll add them to the question. This exercise is helping me understand what I need up-front when asking a EF question in the future. – makerofthings7 Nov 05 '11 at 14:38

2 Answers2

3

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

("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.

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

var sql = query.ToString();

Edit

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

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

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).

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • I think I've been brainwashed into thinking this is the only way to select data: `from c in _entities.XDocuments where c.UserID == UserID.Value select c;` I'll try your technique, thanks! – makerofthings7 Nov 04 '11 at 23:34
  • Hmm; where is the reference to XDocumentSecurity? This is the part that I'm really stuck on – makerofthings7 Nov 05 '11 at 00:02
  • @makerofthings7: I was assuming that `XDocumentSecurity` is a join table between `Groups` and `XDocuments` because of the composite PK and the two FKs (many-to-many relationship between `Group` and `XDocument` entity and `XDocumentSecurity` isn't an entity, just a join table). Is that wrong? Can you show perhaps the entity classes you have and their navigation properties in your question. – Slauma Nov 05 '11 at 00:10
  • I have two join tables that join other tables. I added a "graphic" of the the relationships above. – makerofthings7 Nov 05 '11 at 00:44
  • @makerofthings7: Then you would not need to use `XDocumentSecurity` in the queries at all. See my Edit above, I have tried to clarify that. – Slauma Nov 05 '11 at 01:06
  • The ToString doesn't seem to work... see http://stackoverflow.com/q/8129970/328397 – makerofthings7 Nov 15 '11 at 00:32
3

You can also view the SQL generated by EF 4.1 using either

Although, at this time, you need to use the LINQPad beta for EF 4.1.

Regarding your second question, I believe your query would translate fine. Using LINQPad to check the SQL, the following query

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

translates to

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]

I think @Slauma's recommendation to use navigation proeprties is the way to go if your model supports it.

Still, get LINQPad - you won't regret it :)

Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127
  • One could load the documents instead of only the IDs in one query with `Intersect` (something along the lines of the first code snippet here: http://stackoverflow.com/questions/7821500/what-is-the-most-efficient-way-to-do-comparisons-involving-many-many-relationshi/7828307#7828307). This would also be LINQ or navigation property friendly, so to speak. But I really don't know what's better regarding SQL performance. I also have the feeling that the Where-Any-Any is better for big tables, but sure I am not. – Slauma Nov 04 '11 at 23:59