SQL to LINQ (to implement LEFT JOIN)

Question

I have one below SQL query, it is more complex having some GroupBy, Conditions, etc. which i skip here in post to make my question easy:

SELECT 
    SUBSTRING(TSL.ctg_name, 6, 100) AS 'TREKK', *

    FROM Filteredctg_timbersettlementline AS TSL 

    INNER JOIN Filteredctg_timbersettlement AS TS 
        ON TSL.ctg_timbersettlementid = TS.ctg_timbersettlementid 
    LEFT JOIN FilteredNew_property AS P 
        ON TS.ctg_propertyid = P.new_propertyid

    WHERE (TS.ctg_timbersettlementid = @TimberSettlementID) AND
          (TSL.ctg_reportgroup = 'TREKK')

I tried below Linq:

var Trekks = (from ts in XrmContext.ctg_timbersettlementSet

              join tsl in XrmContext.ctg_timbersettlementlineSet
                  on ts.Id equals tsl.ctg_timbersettlementid.Id
              join p in XrmContext.New_propertySet
                  on ts.ctg_propertyid.Id equals p.New_propertyId

              into temp
              from p in temp.DefaultIfEmpty()

              where ts.ctg_timbersettlementId == TimberSettlementGuid
              where tsl.ctg_reportgroup == "TREKK"
              select new
              {
                  tsl.ctg_name,
                  ts.ctg_BasisAllocatedForestryFund,
              }).ToList();

It throwing me an error: The method 'GroupJoin' cannot follow the method 'Join' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.

How can i achive LEFT JOIN?

[How implement Left Join Linq To SQL](http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join) — Szer, Mar 25 '15 at 15:16
You changed something between the two queries... In the original query there is a FROM, a JOIN with the FROM, a LEFT JOIN with the INNER JOIN, while in the LINQ query both JOIN are against the FROM. Try doing the query as in SQL — xanatos, Mar 25 '15 at 15:21
So start with ctg_timbersettlementlineSet, then join ctg_timbersettlementSet and in the end left join New_propertySet — xanatos, Mar 25 '15 at 15:24

score 0 · Answer 1 · answered Mar 25 '15 at 18:04

To achieve a left join, you need to use an intermediary DefaultIfEmpty() :

var query = from c in db.Customers
            join o in db.Orders
               on c.CustomerID equals o.CustomerID into sr
            from x in sr.DefaultIfEmpty()
            select new {
               CustomerID= c.CustomerID, ContactName=c.ContactName,
               OrderID = x.OrderID == null ? -1 : x.OrderID};

SQL to LINQ (to implement LEFT JOIN)

1 Answers1