CODEWITHSUNDEEP
c#linq-to-sqllambda
Bazinga
Bazinga

Reputation: 1014

How can I use LINQ to achieve the different joins SQL has to offer?

I need to convert this query:

SELECT *
FROM Table1 AS t1
left join Table2 t2 
    on t1.Column1 = t2.Column1
left join Table2 t2
    on t1.Column2 = t2.Column2 and t2.Column3 = 1

to LinqToSQL and Lambda.

I have been looking for a good interpretation of the SQL joins in LINQ but haven't had much luck in finding anything that clearly states how to take advantage of LINQ to achieve the different joins offered in SQL.

Can someone offer an explanation of how the different SQL joins can be taken advantage of in SQL.

Upvotes: 0

Views: 89

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

Try something like this:

AlternativeLearningPlanYears
.GroupJoin
(
    Leas,
    x => x.DistrictLeaId,
    y => y.LeaId,
    (x,y) => new {x,y}
)
.GroupJoin
(
    Leas,
    x => new {x.PrivateInstitutionId,1},
    y => new {y.PrivateInstitutionId,IsPrivateInstitution},
    (x,y) => new {x,y}
)
.Where
(
    z => z.x.SchoolYear == 23
    && z.x.Granted == 1
    && z.x.PrivateInstitutionId == null
    && z.x.DistrictName == null
)

I don't have your schema so there may be some errors, so post them and we'll work them out..

Upvotes: 1

Related Questions