Multiple Joins in Entity Framework query

Question

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!

I am pretty new to Linq and I am using the Entity Framework for my object. I have an .edmx file in my project.

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:

using System.Linq.Dynamic;

The problem is, I don't think my join is working well.

This is my current code:

private void FetchData()
{
    using (var Context = new ProjectEntities())
    {
        var Query =
            Context.Users
            .Join(Context.UserStats,            // Table to Join
                u => u.msExchMailboxGuid,       // Column to Join From
                us => us.MailboxGuid,           // Column to Join To
                (u, us) => new                  // Alias names from Tables
                {
                    u,
                    us
                })
            .Join(Context.TechContacts,         // Table to Join
                u => u.u.UserPrincipalName,     // Column to Join From
                tc => tc.UPN,                   // Column to Join To
                (u, tc) => new                  // Alias names from Tables
                {
                    u = u,
                    tc = tc
                })
               .Where(u => 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();
    }
}

How can I write it so it works like this would in normal SQL?

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

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.

I've googled for this for a while now but no dice yet.

Any help much appreciated!

EDIT - this is my current query. It works but it is a bit of a nightmare to behold.

Bear with me. I wanted to include everything here if I can even if it is a bit much.

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?

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.

private void FetchData()
{
    using (var Context = new ProjectEntities())
    {
        string Fields = GetDynamicFields();

        var Query =
            Context.Users
            .Join(Context.UserStats,            // Table to Join
                u => u.msExchMailboxGuid,       // Column to Join From
                us => us.MailboxGuid,           // Column to Join To
                (u, us) => 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 => u.UserPrincipalName,       // Column to Join From
                tc => tc.UPN,                   // Column to Join To
                (u, tc) => 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 => 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();
    }
}

This is what SQL runs in the background:

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] > 120
ORDER BY [Project1].[CompanyName] ASC

Solution

Not much of an answer, but advice. First, go grab LinqPad. When doing query tuning, it's invaluable. Second, my bet is that you are getting a huge query as a result of using .Join. Linq2Entities has a nasty habit of creating projections (sub-queries) every time you do a join. I would spend some quality time with LinqPad and my query until I get the query I want.

Comments

[HungryHippos]

thanks, will have a play around with LinqPad and see what is what :)

[HungryHippos]

Oh man this just gets more fun. Will update my OP if I can with what I currently have.

[HungryHippos]

OK so i've toyed with LinqPad but it's not really any easier for me, as there are no cool features like autocomplete so i've no idea if what i'm typing is actually working. If you check my OP above you will see what I am currently running but it's noticeably slower than my current TableAdapter and Stored Proc methods.

[HungryHippos]

I'm going to take my question into a new one as things have somewhat evolved for me.

[Bobby D]

Haha, no problem! You can get autocomplete in LinqPad if you buy a license. I often have LinqPad and VS2010 open side-by-side to make things easier. I've found the biggest determining factor in how projections are built is how you use "where" clauses. Favor using "where" over joins - when possible