Convert SQL Left Join to Linq

Question

I'm having some difficulty converting this SQL Statement to LINQ:

Declare @ind date, @outd date
set @ind = '2012-03-17'
set @outd = '2012-03-18'

SELECT k.id
FROM Kennels k
LEFT JOIN (SELECT * 
            FROM Reservations 
            WHERE (DateIn >= @ind and DateIn < @outd) or  (DateOut > @ind and DateOut <= @outd)) r ON k.id = r.Kennel_ID
WHERE r.kennel_id is null

Linq:

Dim available = From k In Kennels _
                    Group Join r In db.Reservations.Where(Function(fx) (fx.DateIn >= Date_From And fx.DateIn < Date_To) Or (fx.DateOut > Date_From And fx.DateOut <= Date_To)) On k Equals r.Kennel Into Group _
                    From r In Group.DefaultIfEmpty
                    Where r.Kennel.ID Is Nothing
                    Select k

The problem with my Linq statement is when r is nothing, the "Where r.Kennel.ID" part of the query falls over.

I'd appreciate some help rewriting this query!

Thanks,

Tom

check out this link: http://stackoverflow.com/questions/525194/linq-inner-join-vs-left-join — sazh, Mar 08 '12 at 04:54

score 1 · Accepted Answer · answered Mar 08 '12 at 04:55

1

You could change Where r.Kennel.ID Is Nothing to Where r Is Nothing

But in c# I would write

var available =
    from k in db.Kennels
    where !k.Reservations.Any(r => r.DateIn >= Date_From && r.DateIn < Date_To) || (r.DateOut > Date_From && r.DateOut <= Date_To)
    select k;

as I find it easier to read.
Sorry for my VB beeing a bit rusty, but I think you can translate it easily (|| is Or, && is And and ! is Not)

answered Mar 08 '12 at 04:55

Albin Sunnanbo

46,430
8
69
108

Thanks for your help! I used a combination of this and my existing query and got it to work. It was essentially the r is nothing which got it. – Tom Mar 08 '12 at 14:58

Convert SQL Left Join to Linq

1 Answers1