Databases
StackOverflow2013
Postsdbo
POMultiple Joins in Entity Framework query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Joins in Entity Framework query
    text
    copied!<p>Long time lurker, first time poster. I have found some good answers on here in the past so figure I would come here and see if I can get a little help!</p> <p>I am pretty new to Linq and I am using the Entity Framework for my object. I have an .edmx file in my project.</p> <p>First of all I imported the using System.Linq.Dynamic class from the sample pages that came with VS 2010, so I can add this into my page:</p> <p>using System.Linq.Dynamic;</p> <p>The problem is, I don't think my join is working well.</p> <p>This is my current code:</p> <pre><code>private void FetchData() { using (var Context = new ProjectEntities()) { var Query = Context.Users .Join(Context.UserStats, // Table to Join u =&gt; u.msExchMailboxGuid, // Column to Join From us =&gt; us.MailboxGuid, // Column to Join To (u, us) =&gt; new // Alias names from Tables { u, us }) .Join(Context.TechContacts, // Table to Join u =&gt; u.u.UserPrincipalName, // Column to Join From tc =&gt; tc.UPN, // Column to Join To (u, tc) =&gt; new // Alias names from Tables { u = u, tc = tc }) .Where(u =&gt; true) .OrderBy("u.u.CompanyName") .Select("New(u.u.CompanyName,tc.UPN,u.us.TotalItemSize)"); // Add Extra Filters if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text))) { Query = Query.Where("u.CompanyName.Contains(@0)", SearchCompanyNameTextBox.Text); } // Set the Record Count GlobalVars.TotalRecords = Query.Count(); // Add Paging Query = Query .Skip(GlobalVars.Skip) .Take(GlobalVars.Take); // GridView Datasource Binding GridViewMailboxes.DataSource = Query; GridViewMailboxes.DataBind(); } } </code></pre> <p>How can I write it so it works like this would in normal SQL?</p> <pre><code>SELECT u.Column1, u.Column2, us.Column1, tc.Column1 FROM Users AS u INNER JOIN UserStats AS us ON u.msExchMailboxGuid = us.MailboxGuid INNER JOIN TechContacts AS tc ON u.UserPrincipalName = tc.UPN </code></pre> <p>I need to keep the dynamic .Where clauses and .Select field names, the problem as you can see right now is that I need to do u.u.CompanyName to get back the u.CompanyName field as it's in my joins twice.</p> <p>I've googled for this for a while now but no dice yet.</p> <p>Any help much appreciated!</p> <p>EDIT - this is my current query. It works but it is a bit of a nightmare to behold.</p> <p>Bear with me. I wanted to include everything here if I can even if it is a bit much.</p> <p>Dynamic column selection is a must for me. Otherwise I might as well stick with my table adapters and stored procs. Being able to reduce my query to returning less data is one of my goals with this. If anyone can suggest improvements I am all ears?</p> <p>I couldn't find a way to stop having to select my joins into subitems, in SQL when I join I simply have to return the columns I want by way of my SELECT statement.</p> <pre><code>private void FetchData() { using (var Context = new ProjectEntities()) { string Fields = GetDynamicFields(); var Query = Context.Users .Join(Context.UserStats, // Table to Join u =&gt; u.msExchMailboxGuid, // Column to Join From us =&gt; us.MailboxGuid, // Column to Join To (u, us) =&gt; new // Declare Columns for the next Join { ObjectGuid = u.objectGuid, msExchMailboxGuid = u.msExchMailboxGuid, CompanyName = u.CompanyName, ResellerOU = u.ResellerOU, DisplayName = u.DisplayName, MBXServer = u.MBXServer, MBXSG = u.MBXSG, MBXDB = u.MBXDB, MBXWarningLimit = u.MBXWarningLimit, MBXSendLimit = u.MBXSendLimit, MBXSendReceiveLimit = u.MBXSendReceiveLimit, extensionAttribute10 = u.extensionAttribute10, legacyExchangeDN = u.legacyExchangeDN, UserPrincipalName = u.UserPrincipalName, Mail = u.Mail, lastLogonTimeStamp = u.lastLogonTimestamp, createTimeStamp = u.createTimeStamp, modifyTimeStamp = u.modifyTimeStamp, altRecipient = u.altRecipient, altRecipientBL = u.altRecipientBL, DeletedDate = u.DeletedDate, MailboxGuid = us.MailboxGuid, Date = us.Date, AssociatedItemCount = us.AssociatedItemCount, DeletedItemCount = us.DeletedItemCount, ItemCount = us.ItemCount, LastLoggedOnUserAccount = us.LastLoggedOnUserAccount, LastLogonTime = us.LastLogonTime, StorageLimitStatus = us.StorageLimitStatus, TotalDeletedItemSize = us.TotalDeletedItemSize, TotalItemSize = us.TotalItemSize, MailboxDatabase = us.MailboxDatabase }) .Join(Context.TechContacts, // Table to Join u =&gt; u.UserPrincipalName, // Column to Join From tc =&gt; tc.UPN, // Column to Join To (u, tc) =&gt; new // Declare Final Column Names { ObjectGuid = u.ObjectGuid, msExchMailboxGuid = u.msExchMailboxGuid, CompanyName = u.CompanyName, ResellerOU = u.ResellerOU, DisplayName = u.DisplayName, MBXServer = u.MBXServer, MBXSG = u.MBXSG, MBXDB = u.MBXDB, MBXWarningLimit = u.MBXWarningLimit, MBXSendLimit = u.MBXSendLimit, MBXSendReceiveLimit = u.MBXSendReceiveLimit, extensionAttribute10 = u.extensionAttribute10, legacyExchangeDN = u.legacyExchangeDN, UserPrincipalName = u.UserPrincipalName, Mail = u.Mail, lastLogonTimeStamp = u.lastLogonTimeStamp, createTimeStamp = u.createTimeStamp, modifyTimeStamp = u.modifyTimeStamp, altRecipient = u.altRecipient, altRecipientBL = u.altRecipientBL, DeletedDate = u.DeletedDate, MailboxGuid = u.MailboxGuid, Date = u.Date, AssociatedItemCount = u.AssociatedItemCount, DeletedItemCount = u.DeletedItemCount, ItemCount = u.ItemCount, LastLoggedOnUserAccount = u.LastLoggedOnUserAccount, LastLogonTime = u.LastLogonTime, StorageLimitStatus = u.StorageLimitStatus, TotalDeletedItemSize = u.TotalDeletedItemSize, TotalItemSize = u.TotalItemSize, MailboxDatabase = u.MailboxDatabase, // New Columns from this join UPN = tc.UPN, Customer_TechContact = tc.Customer_TechContact, Customer_TechContactEmail = tc.Customer_TechContactEmail, Reseller_TechContact = tc.Reseller_TechContact, Reseller_TechContactEmail = tc.Reseller_TechContact, Reseller_Name = tc.Reseller_Name }) .Where(u =&gt; true) .OrderBy(GlobalVars.SortColumn + " " + GlobalVars.SortDirection) .Select("New(" + Fields + ")"); // Add Extra Filters if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text))) { Query = Query.Where("CompanyName.StartsWith(@0)", SearchCompanyNameTextBox.Text); } // Set the Record Count GlobalVars.TotalRecords = Query.Count(); // Add Paging Query = Query .Skip(GlobalVars.Skip) .Take(GlobalVars.Take); // GridView Datasource Binding GridViewMailboxes.DataSource = Query; GridViewMailboxes.DataBind(); } } </code></pre> <p>This is what SQL runs in the background:</p> <pre><code>SELECT TOP (20) [Project1].[C1] AS [C1], [Project1].[objectGuid] AS [objectGuid], [Project1].[msExchMailboxGuid] AS [msExchMailboxGuid], [Project1].[CompanyName] AS [CompanyName], [Project1].[ResellerOU] AS [ResellerOU], [Project1].[DisplayName] AS [DisplayName], [Project1].[MBXServer] AS [MBXServer], [Project1].[MBXSG] AS [MBXSG], [Project1].[MBXDB] AS [MBXDB], [Project1].[MBXWarningLimit] AS [MBXWarningLimit], [Project1].[MBXSendLimit] AS [MBXSendLimit], [Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], [Project1].[extensionAttribute10] AS [extensionAttribute10], [Project1].[legacyExchangeDN] AS [legacyExchangeDN], [Project1].[UserPrincipalName] AS [UserPrincipalName], [Project1].[Mail] AS [Mail], [Project1].[lastLogonTimestamp] AS [lastLogonTimestamp], [Project1].[createTimeStamp] AS [createTimeStamp], [Project1].[modifyTimeStamp] AS [modifyTimeStamp], [Project1].[altRecipient] AS [altRecipient], [Project1].[altRecipientBL] AS [altRecipientBL], [Project1].[DeletedDate] AS [DeletedDate] FROM ( SELECT [Project1].[objectGuid] AS [objectGuid], [Project1].[msExchMailboxGuid] AS [msExchMailboxGuid], [Project1].[CompanyName] AS [CompanyName], [Project1].[ResellerOU] AS [ResellerOU], [Project1].[DisplayName] AS [DisplayName], [Project1].[MBXServer] AS [MBXServer], [Project1].[MBXSG] AS [MBXSG], [Project1].[MBXDB] AS [MBXDB], [Project1].[MBXWarningLimit] AS [MBXWarningLimit], [Project1].[MBXSendLimit] AS [MBXSendLimit], [Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], [Project1].[extensionAttribute10] AS [extensionAttribute10], [Project1].[legacyExchangeDN] AS [legacyExchangeDN], [Project1].[UserPrincipalName] AS [UserPrincipalName], [Project1].[Mail] AS [Mail], [Project1].[lastLogonTimestamp] AS [lastLogonTimestamp], [Project1].[createTimeStamp] AS [createTimeStamp], [Project1].[modifyTimeStamp] AS [modifyTimeStamp], [Project1].[altRecipient] AS [altRecipient], [Project1].[altRecipientBL] AS [altRecipientBL], [Project1].[DeletedDate] AS [DeletedDate], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[CompanyName] ASC) AS [row_number] FROM ( SELECT [Extent1].[objectGuid] AS [objectGuid], [Extent1].[msExchMailboxGuid] AS [msExchMailboxGuid], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ResellerOU] AS [ResellerOU], [Extent1].[DisplayName] AS [DisplayName], [Extent1].[MBXServer] AS [MBXServer], [Extent1].[MBXSG] AS [MBXSG], [Extent1].[MBXDB] AS [MBXDB], [Extent1].[MBXWarningLimit] AS [MBXWarningLimit], [Extent1].[MBXSendLimit] AS [MBXSendLimit], [Extent1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], [Extent1].[extensionAttribute10] AS [extensionAttribute10], [Extent1].[legacyExchangeDN] AS [legacyExchangeDN], [Extent1].[UserPrincipalName] AS [UserPrincipalName], [Extent1].[Mail] AS [Mail], [Extent1].[lastLogonTimestamp] AS [lastLogonTimestamp], [Extent1].[createTimeStamp] AS [createTimeStamp], [Extent1].[modifyTimeStamp] AS [modifyTimeStamp], [Extent1].[altRecipient] AS [altRecipient], [Extent1].[altRecipientBL] AS [altRecipientBL], [Extent1].[DeletedDate] AS [DeletedDate], 1 AS [C1] FROM [dbo].[Users] AS [Extent1] INNER JOIN [dbo].[UserStats] AS [Extent2] ON [Extent1].[msExchMailboxGuid] = [Extent2].[MailboxGuid] INNER JOIN [dbo].[TechContacts] AS [Extent3] ON [Extent1].[UserPrincipalName] = [Extent3].[UPN] ) AS [Project1] ) AS [Project1] WHERE [Project1].[row_number] &gt; 120 ORDER BY [Project1].[CompanyName] ASC </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload