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)