Linq 2 Left Outer Join With Multiple Columns in second one

SerenityNow

I have googled and try a lot of the sample found here but I can't get it to work.

I Have the following SQL Code

Declare @PO as table
(Id int,
 Date datetime,
 PoNumber nvarchar(10),
 CustomerName nvarchar(100))

 Insert Into @PO values (1, '2013-07-15', 'po-01', 'Customer AAA')
Insert Into @PO values (1, '2013-07-15', 'po-02', 'Customer BBB')
Insert Into @PO values (1, '2013-07-15', 'po-03','Customer CCC')

Declare @SH as table
(Id int,
Date datetime,
PoNumber nvarchar(10),
ShNumber nvarchar(10),
DocumentType int)

Insert into @SH  values (1, '2013-07-20', 'po-01', 'sh-01', 1)
Insert into @SH  values (2, '2013-07-20', 'po-03', 'sh-02', 1)


Declare @SD as table
(Id int,
 Qty int,
 DocumentType int,
 ShNumber nvarchar(10),
 ShippingDate datetime)


Insert Into @SD values (1,1,1,'sh-01', '2013-08-01')
Insert Into @SD values (2,2,2,'sh-01', '2013-08-02')
Insert Into @SD values (3,1,1,'sh-01', '2013-08-03')
Insert Into @SD values (4,2,2,'sh-01', '2013-08-04')
Insert Into @SD values (5,1,1,'sh-01', '2013-08-05')
Insert Into @SD values (6,3,1,'sh-02', '2013-08-06')
Insert Into @SD values (7,1,1,'sh-02', '2013-08-07')
Insert Into @SD values (8,2,2,'sh-02', '2013-08-08')
Insert Into @SD values (9,1,2,'sh-02', '2013-08-09')




Select PO.PoNumber, CustomerName, Isnull(SH.ShNumber,''), Max(ShippingDate) ShippingDate, Sum(Isnull(SD.Qty,0)) QuantitySold From @PO PO
left join @SH SH
on PO.PoNumber = SH.PoNumber
left join @SD SD on SH.ShNumber = SD.ShNumber and SH.DocumentType = SD.DocumentType
group by PO.PoNumber, CustomerName, SH.ShNumber
order by PO.PoNumber

which returns correctly

po-01   Customer AAA    sh-01   2013-08-05 00:00:00.000 3
po-02   Customer BBB        NULL    0
po-03   Customer CCC    sh-02   2013-08-07 00:00:00.000 4

Using the wonderful LinqPad, I try to do the same thing.

I created 3 classes

public class PO
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public string PoNumber { get; set; }
    public string CustomerName { get; set; }
}

public class SH
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public string PoNumber { get; set; }
    public string ShNumber { get; set; }
    public int Documenttype { get; set; }
}

public class SD
{
    public int Id { get; set; }
    public int Qty { get; set; }
    public int DocumentType { get; set; }
    public string ShNumber { get; set; }
    public DateTime ShippingDate { get; set; }
}

and then created the same test date that my sql add

var po = new List<PO>();
    var sh = new List<SH>();
    var sd = new List<SD>();

    po.Add(new PO() {Id=1,Date = new DateTime(2013,7,15), PoNumber = "po-01", CustomerName = "Customer AAA"});
    po.Add(new PO() {Id=2,Date = new DateTime(2013,7,15), PoNumber = "po-02", CustomerName = "Customer BBB"});
    po.Add(new PO() {Id=3,Date = new DateTime(2013,7,15), PoNumber = "po-03", CustomerName = "Customer CCC"});

    sh.Add(new SH() {Id=1, Date = new DateTime(2013,7,20), PoNumber = "po-01", ShNumber = "sh-01", Documenttype= 1});
    sh.Add(new SH() {Id=2, Date = new DateTime(2013,7,20), PoNumber = "po-03", ShNumber = "sh-02", Documenttype= 1});

    sd.Add(new SD() {Id=1, ShippingDate = new DateTime(2013,8,1), ShNumber = "sh-01", DocumentType = 1, Qty = 1});
    sd.Add(new SD() {Id=2, ShippingDate = new DateTime(2013,8,2), ShNumber = "sh-01", DocumentType = 2, Qty = 2});
    sd.Add(new SD() {Id=3, ShippingDate = new DateTime(2013,8,3), ShNumber = "sh-01", DocumentType = 1, Qty = 1});
    sd.Add(new SD() {Id=4, ShippingDate = new DateTime(2013,8,4), ShNumber = "sh-01", DocumentType = 2, Qty = 2});
    sd.Add(new SD() {Id=5, ShippingDate = new DateTime(2013,8,5), ShNumber = "sh-01", DocumentType = 1, Qty = 1});
    sd.Add(new SD() {Id=6, ShippingDate = new DateTime(2013,8,6), ShNumber = "sh-02", DocumentType = 1, Qty = 3});
    sd.Add(new SD() {Id=7, ShippingDate = new DateTime(2013,8,7), ShNumber = "sh-02", DocumentType = 1, Qty = 1});
    sd.Add(new SD() {Id=8, ShippingDate = new DateTime(2013,8,8), ShNumber = "sh-02", DocumentType = 2, Qty = 2});
    sd.Add(new SD() {Id=9, ShippingDate = new DateTime(2013,8,9), ShNumber = "sh-02", DocumentType = 2, Qty = 1});

However, when I try to reproduce the same logic to query that data, I get lost with the second left join and its group by

this is where i stop

var query = from p in po
                join h in sh
                on p.PoNumber equals h.PoNumber into j2
                from j3 in j2.DefaultIfEmpty()
                group j3 by new {CustomerName = p.CustomerName, PoNumber = p.PoNumber, DocumentType = j3 == null ? 0 : j3.Documenttype, ShNumber = j3 == null ? string.Empty : j3.ShNumber}
                into grouped
                join d in sd
                on new {grouped.Key.ShNumber, grouped.Key.DocumentType} equals new {d.ShNumber, d.DocumentType} into k2
                from k3 in k2.DefaultIfEmpty()
                group new {k3.ShippingDate, k3.Qty} by new {grouped.Key.ShNumber, grouped.Key.CustomerName, grouped.Key.PoNumber} into g2
                select new {
                                PoNumber = g2.Key.PoNumber,
                                CustomerName = g2.Key.CustomerName,
                                ShNumber = g2.Key.ShNumber,
                                Qty = g2.Sum(o => o.Qty),
                                ShippingDate = g2.Max(o => o.ShippingDate)
                            };

If anyone can help, that would be much appreciated.

SerenityNow

I figured it out. i have to check the null values when doing Sum and Max at the end

var query = from p in po
                join h in sh
                on p.PoNumber equals h.PoNumber into j2
                from j3 in j2.DefaultIfEmpty()
                group j3 by new {CustomerName = p.CustomerName, PoNumber = p.PoNumber, DocumentType = j3 == null ? 0 : j3.Documenttype, ShNumber = j3 == null ? string.Empty : j3.ShNumber}
                into grouped
                join d in sd
                on new {grouped.Key.ShNumber, grouped.Key.DocumentType} equals new {d.ShNumber, d.DocumentType} into k2
                from k3 in k2.DefaultIfEmpty()
                group k3 by new {ShNumber= grouped.Key.ShNumber, CustomerName = grouped.Key.CustomerName, PoNumber = grouped.Key.PoNumber} into g2
                select new {
                                PoNumber = g2.Key.PoNumber,
                                CustomerName = g2.Key.CustomerName,
                                ShNumber = g2.Key.ShNumber,
                                Qty = g2.Sum(o => o == null ? 0 : o.Qty),
                                ShippingDate = g2.Max(o => o == null ? DateTime.MinValue : o.ShippingDate)
                            };

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Linq left outer join with multiple condition

SQL left outer join on multiple columns

pyspark left outer join with multiple columns

Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

Left outer join on 2 columns not running properly

LINQ to SQL - Left Outer Join with multiple join conditions

Pandas left outer join multiple dataframes on multiple columns

LINQ to SQL: Left join on multiple columns

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

LINQ Left Outer Join Multiple Tables with Group Count and Row Concatenation

Complex joins in Linq with multiple tables and LEFT OUTER JOIN

EFCore Linq Left Outer Join

Equivalent of Left outer join in LINQ

Entity Framework - Left outer join on multiple columns with OR condition

Addition: How to perform an outer left join on multiple columns using a loop?

Multiple left outer join With Mongodb

Left outer join for multiple users

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

LINQ query left join multiple columns with null check failing

How to perform left outer join in Linq

Left outer join using LINQ -- understanding the code

How to use left outer join in Linq

Convert SQL (Left Outer Join's) to LINQ

Linq query of left outer join not properly working

Convert SQL (LEFT OUTER JOIN) to LinQ

Translate SQL to LINQ using LEFT OUTER JOIN

Outer Join 2 Data Frames in R two columns to one

Multiple LEFT OUTER JOIN on multiple tables

Outer join 2 data frames by multiple columns in R