Left Join Woes

[toddtsic]

Using EnitityFrameWork 7.0.0-beta5:

Struggling to get much needed functionality of a left join. I am doing null checking on output. Query works fine if not referencing data from left-joined table.

Query:

var x = 
(
    from t in db.teams
    join c in db.Customers on t.customerID equals c.customerID into c1
    from c2 in c1.DefaultIfEmpty()
    where t.jobID == cd.JobParams.id
    select new
    {
        teamID = t.teamID,
        active = t.active,
        teamName = t.teamName,
        customerName = (c2 == null) ? String.Empty : c2.customerName
    }
).ToList();

fails with:

Object reference not set to an instance of an object.

removing the retrieval clause that involves the left joined table: works fine

var x = 
(
    from t in db.teams
    join c in db.Customers on t.customerID equals c.customerID into c1
    from c2 in c1.DefaultIfEmpty()
    where t.jobID == cd.JobParams.id
    select new
    {
        teamID = t.teamID,
        active = t.active,
        teamName = t.teamName
    }
).ToList();

tried formatting the null check as per https://msdn.microsoft.com/en-us/library/Bb397895.aspx

customerName = (c2 == null) ? String.Empty : c2.customerName)

to no avail.

Any suggestions?

T

[maumar]

duplicate of https://github.com/aspnet/EntityFramework/issues/2310 and/or https://github.com/aspnet/EntityFramework/issues/1232

[toddtsic]

Sorry if I'm missing something (please point if so), but I am null checking... (that is the basis of the closing of the related #2310)

T

[maumar]

GroupJoin + DefaultIfEmpty -> LeftOuterJoin translation is not supported currently and the issues I linked touch the same space. Specifics might be different, but the underlying issue is the same.

[maumar]

I will reopen the issue, so that we keep track of this specific scenario - make sure it works when the LOJ translation is implemented

[toddtsic]

Oh no, that can be a problem (not supported). I'm bending over backwards to make do with given functionality (esp lack of spocs, to push an app to market using these tools). Do I have any alternatives for a left join short of a subquery (very inefficient)?

T

[maumar]

You can try latest nightly build. Basic GroupJoin translation support has been added in 7fd078577492601e3671663f06747ae66b70b969

[toddtsic]

Thanks Maurycy, I'm sticking with what was released with VS 2015 Community, can't afford otherwise. I came up with an efficient workaround for this particular issue...

T

On Mon, Aug 10, 2015 at 1:56 PM, Maurycy Markowski <notifications@github.com

wrote:

You can try latest nightly build. Basic GroupJoin translation support has been added in 7fd0785 https://github.com/aspnet/EntityFramework/commit/7fd078577492601e3671663f06747ae66b70b969

— Reply to this email directly or view it on GitHub https://github.com/aspnet/EntityFramework/issues/2809#issuecomment-129603716 .

[maumar]

Would you mind sharing the workaround, in case other people hit the similar problem before new beta is released?

[toddtsic]

Not sure it would be helpful for others, but I'll describe.

I run a sports administration portal...

Usually I'm managing teams that are part of each portal (each job), but in the case of a league a team can come from a different portal (or job).

So by default, a teams club is assumed to be that of the portal I'm managing. So say there is ACME Soccer Club. Any team created under that portal is assumed to be of "ACME Soccer Club" and the teams relevant property (customerID) is null.

But now say I'm managing the AWESOME SOCCER LEAGUES and one of ACME Soccer Club's teams is participating. In that case the team record under the league portal WOULD have a customerID (points to "ACME Soccer Club")

So now you see the need for the left join. When enumerating full descriptions of teams for a job, I have to be aware that the customer could be external and I have to left join team.customerID to custoemrs

By good fortune, each team record in a portal has a jobID attribute which when linked to the job record reveals a customerID attribute, so I can conclude that if there is no customerID in a team record, I can get my hands on by linking to jobs and customers.

This is a fortuitous observation, it actually creates a much faster query (one that doesn't left join).

This leaves me with:

As an aside, the impulse for least amount of code would push me to place queryprep inside of the calls for ListTeams, but that bombs for reasons I bet you understand better than I. I'm working with the mantra "get your data, then manipulate it", (I'm so used to doing both in one step).

The highlighted in yellow snippet is where I force an inner join by using the jobs customer id instead of the teams customerID in the case where the team doesn't have a customerid.

The resulting query is wicked fast.

var queryprep = ( from t in db.teams join j in db.Jobs on t.jobID equals j.jobID join c in db.Customers on t.customerID ?? j.customerID equals c.customerID join ag in db.agegroups on t.agegroupID equals ag.agegroupID join d in db.divisions on t.divID equals d.divID join r in db.Registrations on t.teamID equals r.assigned_teamID where t.jobID == cd.JobParams.id select new { teamID = t.teamID, customerID = t.customerID, customerName = c.customerName, active = t.active, teamName = t.teamName, agegroupName = ag.agegroupName, divName = d.divName, regID = r.RegistrationID } ).ToList();

            List<SelectListItem> ListTeams =
            (
                from record in queryprep
                group record by new
                {
                    record.teamID,
                    record.customerID,
                    record.customerName,
                    record.active,
                    record.agegroupName,
                    record.divName,
                    record.teamName
                } into g
                select new SelectListItem()
                {
                    Value = g.Key.teamID.ToString(),
                    Text = String.Format("{0}:{1}:{2}{3} ({4})",
                        g.Key.agegroupName,
                        g.Key.divName,
                        (g.Key.customerID == null)
                            ? g.Key.teamName
                            : String.Format("{0}:{1}",

g.Key.customerName, g.Key.teamName), ((bool)g.Key.active) ? "" : " (dropped)", g.Count() ) } ).ToList() ;

So the query gives me the options for a dropdown list, with the team id as value and full info on the team as text, including roster counts:

<select class="form-control" id="ddlTeamsSource" asp-items="ViewBag.ListTeams">

--select from list--

                        <option

value="add7b1ae-3cc5-49c8-9990-6589312068de">Boys Under 08:2:Robin/Leo (14) <option value="728a89d4-9d28-49ba-98e1-3b56d0090a36">Boys Under 08:4:Smith (14) <option value="06f9dafb-1939-4afc-9be5-22b8880ea852">Girls Under 08:2:Lillies/Michael Glass (13) <option value="f726b32a-f2d3-4602-9416-d50e7408e076">Boys Under 09:4:Blue Thunder/Blackburn/Siska (14) <option value="9f32c581-3455-4729-8b7f-e6b599c3bded">Boys Under 10:3:NAVY SEALS (15) <option value="28e32705-ca1a-4aa2-9fd9-7dca75d29a7b">Boys Under 14:1:Amigos 1 (16) <option value="468d3611-ccc2-4d11-8025-cfa518a6e664">Boys Under 13:1:BLUE CLAWS (19) <option value="7d67c80f-51c7-45f6-ac96-c84265b23a13">Girls Under 10:2:FALCONS (12) <option value="f7b7d7e2-752a-4b96-92b2-44d26736026c">Girls Under 13:1:JOY (16) <option value="11c5e841-9188-421d-b2ad-ae706eec9a1d">Girls Under 10:2:Dragons/Tim (14) <option value="8c9ac6c1-722b-4676-877d-81f68ec51043">Girls Under 14:2:Cougars (15) <option value="ae724458-9ea4-4ff9-b137-832a90d01404">Boys Under 10:4:Swope (15) <option value="2832d1e2-9016-456b-8ad6-45382f3a70cc">Boys Under 12:2:MeanMachine (18) <option value="1bc7e143-7775-4e73-9824-0b3dbf24199e">Boys Under 18:1:RAPTORS (21) <option value="aa152662-423e-4a73-9e69-c43ea654a032">Boys Under 13:1:Dominators/Rob (18) <option value="8aa46da1-89d9-48cd-addd-11343c9a8181">Boys Under 09:4:Braithwaite (18) <option value="95bd218a-2041-48af-ba89-8fa8c8b45177">Boys Under 08:3:Bob Landis (12) <option value="45da89dd-6aa6-e311-9c1b-f04da202060b">Girls Under 18:2:Dan Shearer II (1) <option value="a08c0336-4538-4652-9fcb-6458de53c89a">Boys Under 14:1:Jerry Mahone/Mustangs (20) <option value="0aeeec51-cc0e-40c6-8c07-9659f588834e">Boys Under 18:1:COBRAS (23) <option value="88880edd-51c9-4936-bfd6-16957306c2db">Boys Under 11:2:FALCONS (17) <option value="a229bc37-7310-4445-9d10-ca66fa2ad1c0">Girls Under 09:3:BLUE CLAWS/ROB ZUROWSKI (14) <option value="cc3f6a27-fa2c-4247-8e3e-516e3519c105">Girls Under 08:1:CANDY CRUSHERS/NATHAN HARVEY (13) <option value="90e69135-de16-4ee9-924d-89ab63d2e8fa">Girls Under 18:1:Dan Shearer (24) <option value="432a09f0-dacb-41dc-80ee-63fbdfd1f7b4">Girls County:Registrations:Girls U10 (3) <option value="fe52b2c1-7add-454d-8098-bad9904bd942">Boys Under 08:4:BLUE CLAWS (16) <option value="3e812241-fa7b-448a-b5fd-b6dcc8769a90">Girls Under 09:3:SEAN M/STOMPERS (14) <option value="f97da402-c90c-4bf1-8757-00778f935e69">Boys Under 09:5:Blue Claws/Shinn (17) <option value="e0a234c5-138c-4a21-a25c-78bd7d5c7276">Boys Under 09:3:Scorpions (12) <option value="0e33d19a-6945-4849-b55f-815b662674ef">Boys Under 10:1:George Grammas (15) <option value="38d3ed1a-6dcd-490b-a157-1d1083463189">Boys Under 11:1:Lightning (15) <option value="001b1fc5-4e46-4b50-b5b5-ce0d1e5243d4">Boys County:Registration:Boys U08 (1)

On Mon, Aug 10, 2015 at 2:08 PM, Maurycy Markowski <notifications@github.com

wrote:

Would you mind sharing the workaround, in case other people hit the similar problem before new beta is released?

— Reply to this email directly or view it on GitHub https://github.com/aspnet/EntityFramework/issues/2809#issuecomment-129606455 .

[maumar]

Thanks a lot for the detailed explanation! I think this might be quite useful to some people actually.

[maumar]

Initial support for LOJ added in c4e44471b6f41f96d37b1acfbbb4cc97cd11ee89 There are still some outstanding issues, see the checkin comments for more details