How to join three or more list with linq or lambda in c#?

Question

hi everybody

i have 3 list :

InvoiceList {invoiceid , invdate , shippingno , ....}
InvoiceFree { invoiceid , comission , insurance , ...}
InvoiceCompany { invoiceid , companyName , Fee , ...}

these list join togheher with invoiceid field

now i want to left join them into one list and works with jointet list in my code . i use this code for joint them but it doesn't works like a list.

what should id do?

var result = from i in invList
join f in invFreeList on i.InvoiceId equals f.InvoiceId into joininvfree
from jif in joininvfree.DefaultIfEmpty()
join c in invCompanyList on i.InvoiceId equals c.InvoiceId into joininvcompany
from jic in joininvcompany.DefaultIfEmpty()
select new
{
    Inv = i,
        Free = jif,
        Com = jic
};

Answer 1

Hi @Farshad Valizade , welcome to Microsoft Q&A.

This is ok, you can use linq to create a List that has Inv, Free and Com, Inv is from InvoiceList, Free is from InvoiceFree, Com is from InvoiceCompany.

But you should pay attention to the need to empty the empty part of the data on the connection or do corresponding processing.

Create three classes according to the code you provided: InvoiceList/Free/Company:

class InvoiceList
{
    public int InvoiceId { get; set; }
    public DateTime InvDate { get; set; }
    public string ShippingNo { get; set; }
    // Other properties
}

class InvoiceFree
{
    public int InvoiceId { get; set; }
    public decimal Commission { get; set; }
    public decimal Insurance { get; set; }
    // Other properties
}

class InvoiceCompany
{
    public int InvoiceId { get; set; }
    public string CompanyName { get; set; }
    public decimal Fee { get; set; }
    // Other properties
}

Create a list of objects of three classes and initialize them:

var invoiceList = new List < InvoiceList >
{
    new InvoiceList
    {
        InvoiceId = 1, InvDate = new DateTime(2023, 1, 1), ShippingNo = "No1"
    },
    new InvoiceList
    {
        InvoiceId = 2, InvDate = new DateTime(2023, 1, 2), ShippingNo = "No2"
    },
    new InvoiceList
    {
        InvoiceId = 3, InvDate = new DateTime(2023, 1, 3), ShippingNo = "No3"
    },
};
var invoiceFreeList = new List < InvoiceFree >
{
    new InvoiceFree
    {
        InvoiceId = 1, Commission = 0.09 M, Insurance = 0.04 M
    },
    new InvoiceFree
    {
        InvoiceId = 2, Commission = 0.05 M, Insurance = 0.06 M
    },
    new InvoiceFree
    {
        InvoiceId = 4, Commission = 0.03 M, Insurance = 0.08 M
    },
};
var invoiceCompanyList = new List < InvoiceCompany >
{
    new InvoiceCompany
    {
        InvoiceId = 1, CompanyName = "Company A", Fee = 5.0 M
    },
    new InvoiceCompany
    {
        InvoiceId = 2, CompanyName = "Company B", Fee = 8.0 M
    },
};

Use linq to connect into a list:

 var joinedList = from i in invoiceList
 join f in invoiceFreeList on i.InvoiceId equals f.InvoiceId into joininvfree
 from jif in joininvfree.DefaultIfEmpty()
 join c in invoiceCompanyList on i.InvoiceId equals c.InvoiceId into joininvcompany
 from jic in joininvcompany.DefaultIfEmpty()
 select new
 {
 	    InvoiceId = i.InvoiceId,
 		InvDate = i.InvDate,
 		ShippingNo = i.ShippingNo,
 		Commission = jif ? .Commission ? ? 0,
 		Insurance = jif ? .Insurance ? ? 0,
 		CompanyName = jic ? .CompanyName,
 		Fee = jic ? .Fee ? ? 0,
 };

The detection can be output like this:

foreach(var item in joinedList)
{
	Console.WriteLine("Invoice Id: {0}", item.InvoiceId);
	Console.WriteLine("Invoice Date: {0}", item.InvDate);
	Console.WriteLine("Shipping No.: {0}", item.ShippingNo);
	Console.WriteLine("Commission: {0:C}", item.Commission);
	Console.WriteLine("Insurance: {0:C}", item.Insurance);
	Console.WriteLine("Company Name: {0}", item.CompanyName);
	Console.WriteLine("Fee: {0:C}", item.Fee);
	Console.WriteLine();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.