linq to sql mulitiple joins

Question

I am trying to create a LINQ to SQL query and am really stumped.

I have a database with multiple tables and can create a query that successfully returns the result from a single join. The problem I am having is introducing the second join.

The SQL statement was easy enough to generate

USE InlandMarina
SELECT *
FROM   Slip s LEFT JOIN Lease l ON s.id = l.slipid 
WHERE  GETDATE() > l.EndDate OR ISNULL(l.startdate, '') = ''

I've generated two functional LINQ queries that individually return the desired results, but can't marry the two successfully.

var nulledSlips = from slips in theContext.Slips
                  join nulled in theContext.Leases on slips.ID equals nulled.SlipID into slipsNulled
                  from nulledEndDate in slipsNulled.Where(nulled => nulled.EndDate==null).DefaultIfEmpty()

Returns all slips that have no end date set in the database (null), they've never been leased.

from expiredSlips in theContext.Slips
                           join leased in theContext.Leases on slips.ID equals leased.SlipID into allSlips
                           from leased in allSlips
                           where leased.EndDate < DateTime.Today

Returns slips that the lease has expired on.

What I'd like to be able to do is combine the two queries somehow into one that returns all slips that have either never been leased out or have had their leases expire.

Any help would be greatly appreciated, I've been at this for two days and can't see the forest for the trees anymore.

Schema is four tables; Lease, Slip, Dock, Location. Lease PK ID FK SlipID

Slip PK ID FK DockID

Dock PK ID FK LocationID

Location PK ID

Revised query is:

var expiredSlips = from slips in theContext.Slips
                   join nulled in theContext.Leases on slips.ID equals nulled.SlipID into slipsNulled
                   from nulledEndDate in slipsNulled.Where(nulled => nulled.EndDate == null).DefaultIfEmpty()
                   join leased in theContext.Leases on slips.ID equals leased.SlipID into allSlips
                   from leased in allSlips.Where(leased=> leased.EndDate < DateTime.Today).DefaultIfEmpty()

Returns:

<SlipsDTO>
<SlipID>1000</SlipID> 
<Width>8</Width> 
<Length>16</Length> 
<DockID>1</DockID> 
<WaterService>true</WaterService> 
<ElectricalService>true</ElectricalService> 
<MarinaLocation>Inland Lake</MarinaLocation> 
<LeaseStartDate xsi:nil="true" /> 
<LeaseEndDate xsi:nil="true" /> 
</SlipsDTO>

Yielding everything. If I remove the last .DefaultIfEmpty() I get a result set of only the slips that have had a lease; current or expired.

Answer 1

if the FK's are in place, normally you don't have to do the 'join' steps yourself with linq-to-sql, you can just reference the navigation properties. Also, keep in mind that you can use 'let' to define things in the query that might make it easier for you to make a query.

Since the FK is by way of lease having a SlipID, then it appears a given slip could have any number (0 .. *) of leases associated with it, although your logic seems to indicate it would be just 0 or 1 - if that's true, a nullable FK LeaseID on the slip might make more sense, but that's a tangent. For now I'll stick with the original SQL logic which was "any associated lease is expired"

var query = 
from slip in context.Slips
let expiredLeases = slip.Leases.Where(lease => lease.EndDate < DateTime.Today)
where slip.Leases.Any() == false // no leases for this slip yet
   || expiredLeases.Any() // at least one associated lease is expired
select slip;

You could still construct a query without navigation properties, of course, but I find it easier and much less error-prone to use the generated navigation properties instead of manually specifying the join and its condition :)