EF Left joining a table on two properties combined with a case statement

Question

I'm trying to write a query for a database that will left join a table to a look up table and the results will be returned based on a case statement.

In normal SQL the query would look like this:

SELECT chis_id, chis_detail, cilt.mhcatID, cilt.mhtID, 'TheFileName' =
CASE  
    WHEN cilt.mhcatID IS NOT NULL AND cilt.mhtID IS NOT NULL THEN chis_linked_filename
    END
FROM chis
    LEFT JOIN cilt on cilt.mhcatID = chis.mhcat_id AND cilt.mhtID = chis.mht_id 
WHERE cch_id = 50

chis is the table being queried, cilt is a look-up table and does not contain any foreign key relationships to chis as a result (chis has existing FK's to mht and mhcat tables by the mhtID and mhcatID respectively).

The query will be used to return a list of history updates for a record. If the join to the cilt lookup table is successful this means that the caller of the query will have permission to view the filename of any associated files for the history updates.

Whilst during my research I've found various posts on here relating on how to do case statements and left joins in Linq to Entity queries, I've not been able to work out how to join on two different fields. Is this possible?

Answer 1

You need to join on an anonymous type with matching field names like so:

var query = from x in context.Table1
            join y in context.Table2
            on new { x.Field1, x.Field2 } equals new { y.Field1, y.Field2 }
            select {...};

A full working example using the an extra from instead of a join would look something like this:

var query = from chis in context.Chis
            from clit in context.Clit
                                .Where(x => x.mhcatID = chis.mhcat_id)
                                .Where(x =>  x.mhtID = chis.mht_id)
                                .DefaultIfEmpty()
            select new
            {
              chis.id, 
              chis.detail, 
              cilt.mhcatID, 
              cilt.mhtID,
              TheFileName = (cilt.mhcatID != null && cilt.mhtID != null) ? chis.linked_filename : null
            };

Answer 2

Based on what Aducci suggested, I used a group join and DefaultIsEmpty() to get the results I wanted. For some reason, I couldn't get DefaultIfEmpty() didn't work correctly on its own and the resulting SQL employed an inner join instead of a left.

Here's the final code I used to get the left join working:

var query = (from chis in context.chis
             join cilt in context.cilts on new { MHT = chis.mht_id, MHTCAT = chis.mhcat_id } equals new { MHT = cilt.mhtID, MHTCAT = cilt.mhcatID } into tempCilts
             from tempCilt in tempCilts.DefaultIfEmpty()
             where chis.cch_id == 50
             select new { 
                             chisID = chis.chis_id, 
                             detail = chis.chis_detail, 
                             filename = chis.chis_linked_filename, 
                             TheFileName = (tempCilt.mhcatID != null && tempCilt.mhtID != null ? chis.chis_linked_filename : null), 
                               mhtID = chis.mht_id, 
                               mhtcatID = chis.mhcat_id 
                        }).ToList();