Множественные соединения в запросе Entity Framework

Давний наблюдатель, первый постер. В прошлом я нашел здесь несколько хороших ответов, поэтому думаю, что я приду сюда и посмотрю, смогу ли я получить небольшую помощь!

Я новичок в Linq и использую Entity Framework для своего объекта. У меня есть файл .edmx в моем проекте.

Прежде всего я импортировал класс using System.Linq.Dynamic из примеров страниц, поставляемых с VS 2010, поэтому я могу добавить это на свою страницу:

using System.Linq.Dynamic;

Проблема в том, что я не думаю, что мое соединение работает хорошо.

Вот мой текущий код:

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

Как я могу написать его так, чтобы он работал так, как в обычном 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

Мне нужно сохранить динамические имена полей .Where и .Select. Проблема, как вы видите прямо сейчас, заключается в том, что мне нужно сделать u.u.CompanyName, чтобы вернуть поле u.CompanyName, поскольку оно дважды присутствует в моих соединениях.

Я уже некоторое время гуглил об этом, но пока не нашел.

Будем признательны за любую помощь!

РЕДАКТИРОВАТЬ - это мой текущий запрос. Это работает, но это что-то вроде кошмара.

Потерпите меня. Я хотел включить сюда все, если смогу, даже если это слишком много.

Динамический выбор столбцов для меня обязателен. В противном случае я мог бы также придерживаться своих настольных адаптеров и хранимых процедур. Возможность сократить мой запрос до возврата меньшего количества данных является одной из моих целей. Если кто-нибудь может предложить улучшения, я все уши?

Я не смог найти способ избавиться от необходимости выбирать свои объединения в подэлементы, в SQL, когда я присоединяюсь, мне просто нужно вернуть нужные столбцы с помощью моего оператора SELECT.

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

Вот что SQL работает в фоновом режиме:

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