CODEWITHSUNDEEP
c#entity-frameworklinqt-sqlentity-framework-6
amber
amber

Reputation: 1137

Recreating a large complex query with left outer joins in LINQ

I'm looking for some advice to tackle this problem. We have this large SQL query with many left outer joins.

Here is the query:

Select Top 1 WA.BHATUS_ID, WA.[CRMBHG_WORKINGACCOUNT_ID], BA.BHATUS_ID, BA.[CRMBHG_BILLINGACCOUNT_ID], BAD.BHATUS_ID, [BAD].[CRMBHG_BILLINGADDRESS_ID], SAD.BHATUS_ID, [SAD].[CRMBHG_SERVICEADDRESS_ID], C.BHATUS_ID, [C].[CRMBHG_CONTACT_ID]
From BillingHiBHory As BH With (NoLock)
    Left Outer Join WorkingAccount As WA With (NoLock)
        On WA.UniversalAccountId = BH.UniversalAccountId
            And WA.BillingRegion = BH.BillingRegion
    Left Outer Join BillingAccount As BA With (NoLock)
        On BA.UniversalAccountId = BH.UniversalAccountId
            And BA.BillingRegion = BH.BillingRegion
    Left Outer Join BillingAddress As BAD With (NoLock)
        On BAD.Grouping = BH.Grouping
            And BAD.SubGrouping = BH.SubGrouping
            And BAD.BillingRegion = BH.BillingRegion
    Left Outer Join dbo.SubScriberInfo SUB With (NoLock)
            On SUB.Grouping = BH.Grouping
            And SUB.SubGrouping = BH.SubGrouping
            And SUB.BillingRegion = BH.BillingRegion
    Left Outer Join ServiceAddress As SAD With (NoLock)
        On SAD.ControlNumberAssigned = SUB.ServceControlNum
            And SAD.BillingRegion = SUB.BillingRegion
    Left Outer Join Contact As C With (NoLock)
        On BH.AccountNum = C.AccountNum
            And BH.BillingRegion = C.BillingRegion
Where BH.Grouping = @Grouping
    And BH.SubGrouping = @SubGrouping
Order By BH.SequenceOrder, WA.StatusDate Desc, BA.StatusDate Desc, BAD.StatusDate Desc, SAD.StatusDate Desc, C.StatusDate Desc

Here's my first crack at the LINQ syntax...

var statusIds = (
    from wa in db.WorkingAccount
    from ba in db.BillingAccount
    from bad in db.BillingAddress
    from si in db.SubscriberInfo
    from sad in db.ServiceAddress
    from c in db.CRMSTG_CONTACT
    where wa.UniversalAccountId == UniversalSubscriberId && wa.BillingRegion == BillingRegion
    where ba.UniversalAccountId == UniversalSubscriberId && ba.BillingRegion == BillingRegion
    where bad.Grouping == Grouping && bad.SubGrouping == SubGrouping && bad.BillingRegion == BillingRegion
    where si.Grouping == Grouping && si.SubGrouping == SubGrouping && si.BillingRegion == BillingRegion
    where sad.ControlNumberAssigned  == si.ServceControlNum && sad.BillingRegion == BillingRegion
    where c.AccountNum == AccountNumber && c.BillingRegion == BillingRegion
    orderby wa.StatusDate descending, ba.StatusDate descending, bad.StatusDate descending, sad.StatusDate descending, c.StatusDate descending
    select new
    {
        workingAddressStatusId = wa.StatusId,
        billingAccountStatusId = ba.StatusId,
        billingAddressStatusId = bad.StatusId,
        serviceAddressStatusId = sad.StatusId,
        contactStatusId = c.StatusDate
    }
    ).FirstOrDefault();

This mostly works but feels like a big hack. Where this falls down is when one of the StatusId values comes back as null and statusIds ends up being null as well.

I'm sure there's a better way to do this. I'm open to suggestions.

As a note, yes, I'm aware of the risks associated with "With (NoLock)" option on the TSQL, we're a secondary app on a very busy database and we don't want to create blocking chains as billing and support staff do their work. For what we're doing, the NoLock is a non-issue. It's not like we're doing multiple unions of queries with aggregators. ;)

Upvotes: 1

Views: 240

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205599

Well, it's true that you have a lot of left outer joins, and also that left outer joins are not naturally supported in LINQ, but the pattern is well known - take a look at Left Outer Join and Composite Keys sections in join clause (C# Reference), so all you need is to follow the pattern:

from bh in db.BillingHiBHory

join wa in db.WorkingAccount
on new { bh.UniversalAccountId, bh.BillingRegion }
equals new { wa.UniversalAccountId, wa.BillingRegion }
into bh_wa from wa in bh_wa.DefaultIfEmpty() // this turns the above (inner) join into left outer join

join ba in db.BillingAccount
on new { bh.UniversalAccountId, bh.BillingRegion }
equals new { ba.UniversalAccountId, ba.BillingRegion }
into bh_ba from ba in bh_ba.DefaultIfEmpty()

join bad in db.BillingAddress
on new { bh.Grouping, bh.SubGrouping, bh.BillingRegion }
equals new { bad.Grouping, bad.SubGrouping, bad.BillingRegion }
into bh_bad from bad in bh_bad.DefaultIfEmpty()

join si in db.SubscriberInfo
on new { bh.Grouping, bh.SubGrouping, bh.BillingRegion }
equals new { si.Grouping, si.SubGrouping, si.BillingRegion }
into bh_si from si in bh_si.DefaultIfEmpty()

join sad in db.ServiceAddress
on new { si.ServceControlNum, si.BillingRegion }
equals new { sad.ControlNumberAssigned, sad.BillingRegion }
into si_sad from sad in si_sad.DefaultIfEmpty()

join c in db.CRMSTG_CONTACT
on new { bh.AccountNum, bh.BillingRegion }
equals new { c.AccountNum, c.BillingRegion }
into bh_c from c in bh_c.DefaultIfEmpty()

where bh.Grouping == Grouping
   && bh.SubGrouping == SubGrouping

// ... (the rest)

One detail to consider is to include nullable cast when selecting non nullable fields from the right part of the left outer join. For instance, if the StatusId type is int, then you need to use something like this:

select new 
{
    workingAddressStatusId = (int?)wa.StatusId,
    // ...
}

Upvotes: 1

Related Questions