How to do a double left outer join in Linq query syntax(or fluent)

Andrew Day
var Claim   = new[] {   new { ClaimId = 10, Desc = "item 10" },
    new { ClaimId = 11, Desc = "item 11" },
    new { ClaimId = 12, Desc = "item 12" },
    new { ClaimId = 13, Desc = "item 13" }};
var Insured = new[] {   new { ClaimId = 10, IsPolicyHolder = true, IsInsured = true, Forename = "10A Pol TRUE Ins TRUE NoSiblings" },
    new { ClaimId = 11, IsPolicyHolder = true, IsInsured = false, Forename = "11A Pol TRUE Ins false NoSiblings" },
    new { ClaimId = 12, IsPolicyHolder = false, IsInsured = true, Forename = "12A Pol false Ins TRUE NoSiblings" },
    new { ClaimId = 13, IsPolicyHolder = true, IsInsured = false, Forename = "13A Pol TRUE Ins false has sibling" },
    new { ClaimId = 13, IsPolicyHolder = false, IsInsured = true, Forename = "13B Pol false Ins TRUE has sibling" }};

    Insured.Dump("Insured Table");

var leftJoinTry =
    from in1 in Insured where in1.IsPolicyHolder == true
    join p in Insured on in1.ClaimId equals p.ClaimId into ps 
    //where ps.IsInsured == true   ****ERROR****
    from p in ps.DefaultIfEmpty()
    select new { in1.ClaimId, in1.IsPolicyHolder, in1.IsInsured, in1.Forename, p_Forename = p.Forename};
leftJoinTry.Dump("leftJoinTry");    

var initialResults = from c in Claim
join pa in Insured on c.ClaimId equals pa.ClaimId where pa.IsPolicyHolder == true
join pb in Insured on pa.ClaimId equals pb.ClaimId where pb.IsInsured == true
select new { c.ClaimId, pa_Forename = pa.Forename, pb_Forename = pb.Forename, };

initialResults.Dump("initialResults");

var query =
    from in1 in Insured where in1.IsPolicyHolder == true
    join p in Insured on in1.ClaimId equals p.ClaimId into ps 
    //where ps.IsInsured == true
    from p in ps.DefaultIfEmpty()
    select new { Category = in1, ProductName = p /*== null ? "(No products)" : p.Forename*/ };
query.Dump("query");

The above in Linq pad gives the following result sets enter image description here

Note that the database used has a primary key of ClaimId(int) and IsPolicyHolder(bool), and there is a constraint that either of the fields IsPolicyHolder or IsInsured must be True.

What I actually want is all the records with IsPolicyHolder and IsInsured being True, and where there is one with IsPolicyHolder is True and IsInsured is False and the same ClaimId with IsPolicyHolder is False and IsInsured is True that the record be combined as ClaimId 13 below. Note that any record with no other ClaimId siblings which have one of IsPolicyHolder and IsInsured set at False and the other at True must also be included and set the unknown Forename(Italics below) to the known Forename(Bold below).

enter image description here

Andrew Day
var firstDate = new DateTime(2016,1,1);
var secondDate = new DateTime(2016,9,1);
var company = "Some_Co.";
var resultCombined = from c in Claims
join cc in ClaimCovers
on c.ClaimId equals cc.ClaimId
join cov in Covers
on cc.CoverId equals cov.CoverId
join pa in Insureds.Where(x => x.IsPolicyHolder == true) on c.ClaimId equals pa.ClaimId 
       into pas from pa in pas.DefaultIfEmpty() 
join pb in Insureds.Where(x => x.IsInsured == true) on c.ClaimId equals pb.ClaimId
       into pbs from pb in pbs.DefaultIfEmpty()
       join ci in ClaimIncidents
                              on c.ClaimId equals ci.ClaimId
                              where c.DateReported >= firstDate && c.DateReported <= secondDate
                                 where c.CompanyID == company
select new { c.ClaimId, PolicyRefernce = c.PolicyReference ?? "",
                                  c.ClaimReference,
                                  c.DateOfLoss,
                                  c.DateReported,
                                  cov.CoverCode,
                                  cov.Description, c.Status,
                                  ci.NotifiedBy,
                                  ci.HowNotified,
                                  ci.ClaimTypeCode,
                                  ci.CauseCode,
                                  ci.AtFault, 
                                  //pa is the first list where policyHolder = true
                                  PolicyHolderName = pa.Forename + " " + pa.Surname, pa_Postcode = pa.Postcode, 
                                  //pb is the second list where insured = true
                                  InsuredName = pb.Forename + " " + pb.Surname, pb_Postcode = pb.Postcode,};    

resultCombined.Dump();

Non matching records gives nulls which can be set to empty strings etc...

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

how to convert a sql query into linq LEFT OUTER JOIN

How to do a Linq Left Outer Join with a middle table?

Linq query of left outer join not properly working

How to perform left outer join in Linq

How to use left outer join in Linq

Unable to convert SQL Query to LINQ Query for Left Outer Join

How to create a JPA query with LEFT OUTER JOIN

How to Write Left Outer join for this SQL query?

What is the proper Linq expression for this left-outer-join query?

Multiple conditions on a left outer join clause in a LINQ query

How to do left outer join exclusion in pandas

How to do a Left Outer join with Laravel?

How to do LEFT OUTER JOIN with include

How to do a left outer join on inequality?

EFCore Linq Left Outer Join

Equivalent of Left outer join in LINQ

How do you perform a left outer join using linq extension methods

How can I do Left Outer Join with Linq when updating an object?

MySQL Query - How do I get a SUM with GROUP BY and WHERE condition and use LEFT OUTER JOIN?

How to do a left outer join in Entity Framework without using the query syntax?

Left Outer join in Ebean query

How to translate an advanced SQL statement with LEFT OUTER JOIN to LINQ?

Scala Slick: How to do an except join (left outer join with is null)?

left join in Linq query

How to implement LEFT/RIGHT OUTER JOIN to replace NOT IN in hive query?

Implementation difference between left outer join and right outer join in LINQ

Mysql double left outer join with count

Left outer join using LINQ -- understanding the code

Convert SQL (Left Outer Join's) to LINQ