CODEWITHSUNDEEP
linq
Keren
Keren

Reputation: 81

Left outer join with "and" in LINQ?

How do I write the following in Linq?

select * from CompetitionDealer d

left outer join CompetitionResult r 
on d.ID = r.fk_CompetitionDealer and     (r.fk_CompetitionParameter is null 
or r.fk_CompetitionParameter = ID1)

where fk_Competition = ID2

The problem is that there is an "AND" on the join. If I move it to the where than the result is completely different so I can't do that.

Upvotes: 0

Views: 181

Answers (2)

Arion
Arion

Reputation: 31239

Maybe something like this:

var ls= (
        from d in db.CompetitionDealer
        from r in db.CompetitionResult
            .Where(a => a.fk_CompetitionDealer == d.ID 
                       && (a.fk_CompetitionParameter == null 
                       || a.fk_CompetitionParameter == ID1)).DefaultIfEmpty()
        where d.fk_Competition == ID2
        select d
    );

where db is the linq data context

Upvotes: 1

Keren
Keren

Reputation: 81

I solved it by dividing it into two questions:

First I selected the following list:

select * from CompetitionResult r where r.fk_CompetitionParameter = ID1)

and than I crossed joined against the list above.

Upvotes: 0

Related Questions