LINQ of the Day

Here's an interesting problem. A table has multiple foreign key references to another table. Each foreign key reference has a different meaning, so they're unique. I need a list of the items referenced, with context information so that I know the usage of the reference. If Table One references Table Two twice in the same row, that's two distinct usages, and should generate two separate records for output.

That's just enough difference to make life interesting, because the output needs information from both tables as well as a constant identifying context and usage. In T-SQL, this is a fairly simple set of four joins, combined with a union so we get all our output in one result set.

Here's the LINQ to do this:

IQueryable<MultiplierCustomer> StdCustomers =
    dc.Pm_VisualCustMultipliers
        .Where(vcm => vcm.RecordDeleted == false)
        .Where(vc => vc.StandardMultiplier == multiplierID)
        .Join(dc.Vm_Customers,
              (vcm => vcm.VisualCustomerID),
              (vc => vc.ID),
              ((vcm, vc) => new MultiplierCustomer
              {
                  VisualCustomerID = vcm.VisualCustomerID,
                  CompanyName = vc.NAME,
                  Brand = vcm.Website,
                  MultiplierType = "Standard"
              }));
IQueryable<MultiplierCustomer> QSCustomers =
    dc.Pm_VisualCustMultipliers
        .Where(vcm => vcm.RecordDeleted == false)
        .Where(vc => vc.QSMultiplier == multiplierID)
        .Join(dc.Vm_Customers,
              (vcm => vcm.VisualCustomerID),
              (vc => vc.ID),
              ((vcm, vc) => new MultiplierCustomer
              {
                  VisualCustomerID = vcm.VisualCustomerID,
                  CompanyName = vc.NAME,
                  Brand = vcm.Website,
                  MultiplierType = "QuickShip"
              }));
IQueryable<MultiplierCustomer> PartsCustomers =
    dc.Pm_VisualCustMultipliers
        .Where(vcm => vcm.RecordDeleted == false)
        .Where(vc => vc.PartsMultiplier == multiplierID)
        .Join(dc.Vm_Customers,
              (vcm => vcm.VisualCustomerID),
              (vc => vc.ID),
              ((vcm, vc) => new MultiplierCustomer
              {
                  VisualCustomerID = vcm.VisualCustomerID,
                  CompanyName = vc.NAME,
                  Brand = vcm.Website,
                  MultiplierType = "Parts"
              }));
IQueryable<MultiplierCustomer> StdBreakCustomers =
    dc.Pm_VisualCustMultipliers
        .Where(vcm => vcm.RecordDeleted == false)
        .Where(vc => vc.StdBreakMultiplier == multiplierID)
        .Join(dc.Vm_Customers,
              (vcm => vcm.VisualCustomerID),
              (vc => vc.ID),
              ((vcm, vc) => new MultiplierCustomer
              {
                  VisualCustomerID = vcm.VisualCustomerID,
                  CompanyName = vc.NAME,
                  Brand = vcm.Website,
                  MultiplierType = "Standard Break"
              }));
List<MultiplierCustomer> usageList =
    StdCustomers
    .Concat(QSCustomers)
    .Concat(PartsCustomers)
    .Concat(StdBreakCustomers)
    .ToList();

What's neat is that it really does build the SQL that you'd want, doing a UNION ALL on the individual LINQ queries, so that you send one query to the database. Here's the generated SQL:

SELECT [t10].[VisualCustomerID], [t10].[NAME] AS [CompanyName], [t10].[Website] AS [Brand], [t10].[value] AS [MultiplierType]
FROM (
    SELECT [t7].[VisualCustomerID], [t7].[NAME], [t7].[Website], [t7].[value]
    FROM (
        SELECT [t4].[VisualCustomerID], [t4].[NAME], [t4].[Website], [t4].[value]
        FROM (
            SELECT [t0].[VisualCustomerID], [t1].[NAME], [t0].[Website], @p1 AS [value]
            FROM [dbo].[pm_VisualCustMultipliers] AS [t0]
            INNER JOIN [dbo].[vm_Customers] AS [t1] ON [t0].[VisualCustomerID] = [t1].[ID]
            WHERE ([t0].[StandardMultiplier] = @p0) AND (NOT ([t0].[RecordDeleted] = 1))
            UNION ALL
            SELECT [t2].[VisualCustomerID], [t3].[NAME], [t2].[Website], @p3 AS [value]
            FROM [dbo].[pm_VisualCustMultipliers] AS [t2]
            INNER JOIN [dbo].[vm_Customers] AS [t3] ON [t2].[VisualCustomerID] = [t3].[ID]
            WHERE ([t2].[QSMultiplier] = @p2) AND (NOT ([t2].[RecordDeleted] = 1))
            ) AS [t4]
        UNION ALL
        SELECT [t5].[VisualCustomerID], [t6].[NAME], [t5].[Website], @p5 AS [value]
        FROM [dbo].[pm_VisualCustMultipliers] AS [t5]
        INNER JOIN [dbo].[vm_Customers] AS [t6] ON [t5].[VisualCustomerID] = [t6].[ID]
        WHERE ([t5].[PartsMultiplier] = @p4) AND (NOT ([t5].[RecordDeleted] = 1))
        ) AS [t7]
    UNION ALL
    SELECT [t8].[VisualCustomerID], [t9].[NAME], [t8].[Website], @p7 AS [value]
    FROM [dbo].[pm_VisualCustMultipliers] AS [t8]
    INNER JOIN [dbo].[vm_Customers] AS [t9] ON [t8].[VisualCustomerID] = [t9].[ID]
    WHERE ([t8].[StdBreakMultiplier] = @p6) AND (NOT ([t8].[RecordDeleted] = 1))
    ) AS [t10]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p1: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Standard]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [QuickShip]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p5: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Parts]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p7: Input NVarChar (Size = 14; Prec = 0; Scale = 0) [Standard Break]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Proving, once again, that there's no substitute for checking the generated SQL when you're doing something twitchy with LINQ.