LINQ query not returning expected results

Question

I am trying to join three SQL tables in LINQ c# for the below SQL

SELECT
    rpp.*
FROM dbo.Orgs ao
LEFT JOIN dbo.Afflia rpa
    ON rpa.AccountId = ao.ID
INNER JOIN dbo.reports rpp
    ON rpp.Id = rpa.reporttId
WHERE ao.Name like '%xyz%'

above query returns data but the equivalent LINQ query doesn't as in below

from a in context.Orgs
join aff in context.Afflia on a.ID equals aff.AccountId
join prescriber in context.Reports on aff.reportId equals prescriber.Id
where a.ORG_NAME.Contains("xyz")

May I know where the mistake is?

Are you sure this is the equivalent Linq query? I usually use the method syntax, but just by looking at it, I'd say this creates all inner joins. Can you maybe check with a profiler if they really are the same? — Akos Nagy, Aug 28 '17 at 19:31
Because you're trying to do inner join instead of left join. You can take a look [here](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) how to do it properly. — Vitali, Aug 28 '17 at 19:32
Please use meaningful variable names in both your SQL and LINQ queries! — AJ X., Aug 28 '17 at 19:32

score 2 · Answer 1 · answered Aug 28 '17 at 20:25

In LINQ you did INNER join but In SQL, you did LEFT join.

Try this instead:

from a in context.Orgs
join aff in context.Afflia on a.ID equals aff.AccountId into affs
from aff in affs.DefaultIfEmpty()
join prescriber in context.Reports on aff.reportId equals prescriber.Id
where a.ORG_NAME.Contains("xyz")

score 1 · Accepted Answer · answered Aug 28 '17 at 19:35

1

In your SQL you are doing a LEFT join to dbo.Afflia, but in your LINQ you are doing an inner join. You need to add "DefaultIfEmpty(), eg

from aff in context.Afflia.Where(join condition here).DefaultIfEmpty()

answered Aug 28 '17 at 19:35

Tom Regan

3,580
4
42
71

score 1 · Answer 3 · answered Aug 28 '17 at 19:40

You could do:

        var prescribers = (from a in context.Orgs
                           from aff in context.Afflia.Where(aff => aff.AccountId == a.ID)
                           from prescriber in context.Reports.Where(pres => pres.Id == aff.reportId)
                           where a.ORG_NAME.Contains("xyz")
                           select prescriber)
                           .ToList();

LINQ query not returning expected results

3 Answers3