CODEWITHSUNDEEP
sqllinqt-sqllinq-to-sql
Adam Plocher
Adam Plocher

Reputation: 14233

LINQ right outer join with multiple conditions in join

I'm trying to convert the following SQL to LINQ:

select * from ClientCommands as cc 
    join ClientCommandTypes as cct on cc.ClientCommandTypeID = cct.ClientCommandTypeID
    right outer join ClientCommandSessionProcessed as ccsp 
        -- This next line is the one I'm having trouble with:
        on cc.ClientCommandID = ccsp.ClientCommandID and cc.ClientSessionID = @ClientSessionID
where
    ccsp.ClientCommandSessionProcessedID is null and
    cc.StartDate < getdate() and
    cc.DeactivatedDate > getdate() and
    (cc.ClientAppID is null or cc.ClientAppID == @ClientAppID)

Basically what it's doing is grabbing data from the ClientCommands table from the database unless a record exists in the ClientCommandSessionProcessed table. I'm doing a right outer join to the ClientCommandSessionProcessed table (with two conditions in the on) and checking that the result from that join is null - if there is a record in that table, the query shouldn't return a result because that means it has been processed by that Session ID.

I have it almost done in LINQ, my only problem is, I can't seem to use more than one condition in my right outer join, and I don't think this would work properly if I stick my second condition in the where clause.

Here's what I have so far for LINQ:

var clientCommands = from cc in db.ClientCommands
    join cct in db.ClientCommandTypes on cc.ClientCommandTypeID equals cct.ClientCommandTypeID
    join ccsp in db.ClientCommandSessionProcesseds 
        // How do I add a second condition here?
        on cc.ClientCommandID equals ccsp.ClientCommandID into ccspR
    from ccspRR in ccspR.DefaultIfEmpty()
    where 
    ccspRR == null &&
    cc.StartDate < DateTime.Now  && 
    cc.DeactivatedDate > DateTime.Now &&
    (cc.ClientAppID == null || cc.ClientAppID == clientApp.ClientAppId)
    select new { cc, cct };

Does anyone know if it's possible to add a second condition to a join? If not, is there a work-around for an issue like this?

Thank you!

Upvotes: 2

Views: 1463

Answers (1)

neontapir
neontapir

Reputation: 4736

You can do something like this:

var result = from x in table1
         join y in table2
         on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

Upvotes: 3

Related Questions