CODEWITHSUNDEEP
.netsqllinq
Tom
Tom

Reputation: 1071

Convert SQL Left Join to Linq

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

Upvotes: 0

Views: 169

Answers (1)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47068

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)

Upvotes: 1

Related Questions