Convert this sql query to LINQ to Lambda Left Outer Join

Question

I have trying to convert this sql query to Linq to lambda and i have no success i only have the records that are in the table places that match the id on the table requireddocuments i want the nulls too. The sql query works fine but the lambda doesn't

SQL Query.

  SELECT Document, Place, Record
   FROM RequiredApplicationDocuments LEFT OUTER JOIN Places ON
     RequiredApplicationDocuments.Id = Places.RequiredApplicationDocumentId
       WHERE Places.SecondPlaceId = 4 OR Places.SecondPlaceId IS NULL

Lambda

Database.RequiredApplicationDocuments.Join(Database.Placess, 
            ra => ra.Id, fa => fa.RequiredApplicationDocumentId, (fa, ra) =>
            new {Places = fa, RequiredApplicationDocument = ra}).DefaultIfEmpty().toList().Select(fa => new Places
                           {
                  FileName = fa.RequiredApplicationDocument.FileName,
                  LoanApplicationId = fa.RequiredApplicationDocument.LoanApplicationId,
                  Name = fa.RequiredApplicationDocument.Name,
                  RequiredApplicationDocument = fa.RequiredApplicationDocument.RequiredApplicationDocument,
                  Id = fa.Places.Id,
                  CreationDate = fa.RequiredApplicationDocument.CreationDate,
                  Contents = fa.RequiredApplicationDocument.Contents,
                  RequiredApplicationDocumentId = fa.RequiredApplicationDocument.RequiredApplicationDocumentId,
                  LoanApplication = fa.RequiredApplicationDocument.LoanApplication,
                  Type = fa.RequiredApplicationDocument.Type 
            }).AsQueryable();

Is there a navigaion property like RequiredApplicationDocument.Places? — Gert Arnold
– Gert Arnold, Commented Dec 13, 2012 at 23:53

Ken Goodridge · Accepted Answer · 2012-12-13 23:12:32Z

4

use GroupJoin

This is more targeted to you SQL than to your LINQ

        var res = RequiredApplicationDocuments.GroupJoin(Places,
            p => p.Id,
            d => d.RequiredApplicationDocumentId,
            (d, places) => new
            {
                Document = d,

                Place = places.Where(p => p.SecondPlaceId == 4).FirstOrDefault(),

                // if don't want to exclude documents with "non-4" places only, 
                // remove this and last where clause
                // but this is what your SQL does
                HasNoPlaces = places.Count() == 0 

            }).Where(r => r.HasNoPlaces || r.Place != null);

edited Dec 13, 2012 at 23:12

answered Dec 13, 2012 at 22:38

Ken Goodridge

4,0111 gold badge22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert this sql query to LINQ to Lambda Left Outer Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related