CODEWITHSUNDEEP
c#linqjoin
DoIt
DoIt

Reputation: 3438

LINQ query not returning expected results

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?

Upvotes: 0

Views: 275

Answers (3)

Mhd
Mhd

Reputation: 2978

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")

Upvotes: 2

etuncoz
etuncoz

Reputation: 198

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();

Upvotes: 1

Tom Regan
Tom Regan

Reputation: 3841

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()

Upvotes: 1

Related Questions