Entity Framework query with multiple joins

c# entity-framework join linq linq-to-entities

Question

First-time poster, longtime lurker. I figured I would come here and see if I can get any assistance as I have previously received some excellent answers here.

I am using the Entity Framework for my object and am somewhat new to Linq. A.edmx file exists in my project.

To add this to my website, I first imported the using System.Linq.Dynamic class from the example pages that comes with Visual Studio 2010:

making use of System.Linq.Dynamic;

I don't believe my join is functioning effectively, which is the issue.

My current code is as follows:

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 such that it operates as it would in standard 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 must maintain the dynamic. clauses and when. 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 been looking for this on Google for a long, but to no avail.

We would appreciate any assistance.

This is my most recent inquiry. Although it works, it is a bit of a nightmare to observe.

Adapt to me. Even if it is a little excessive, I wanted to add everything here if I could.

For me, dynamic column selection is essential. Otherwise I might as well stick with my table adapters and stored procs. One of my objectives with this is to be able to refine my query such that it returns less information. I'm open to suggestions for improvements, so please share.

In SQL, when I join, all I have to do is use my SELECT statement to return the columns I want. I was unable to discover a solution to eliminate needing to select my joins into subitems.

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 executes the following 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
1
8
3/10/2012 6:00:12 PM

Accepted Answer

Not much of a response, but suggestions. Go get LinqPad first. It is really helpful while doing query tweaking. Second, I'm betting that utilizing is the reason you are receiving a large query. Join. Every time you do a join using Linq2Entities, projections (sub-queries) are automatically generated. Until I had the query I wanted, I would spend some quality time with LinqPad and my query.

4
3/8/2012 6:21:36 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow