Join is used to merge the data from various tables like one or more tables with the column match between those tables.
using EntityFrameworkCore_ConsoleApp.Models;
namespace EntityFrameworkCore_ConsoleApp
{
public class Program
{
static void Main(string[] args)
{
using (var databaseContext = new DatabaseContext())
{
var invoices = (from account in databaseContext.Accounts
join invoice in databaseContext.Invoices
on account.Id equals invoice.AccountId
select new
{
InvoiceId = invoice.Id,
InvoiceName = invoice.Name,
Created = invoice.Created,
Status = invoice.Status,
Payment = invoice.Payment,
AccountId = account.Id,
FullName = account.FullName,
Phone = account.Contact.Phone,
Address = account.Contact.Address
}).ToList();
Console.WriteLine("Invoice List");
invoices.ForEach(invoice =>
{
Console.WriteLine("Invoice Id: " + invoice.InvoiceId);
Console.WriteLine("Invoice Name: " + invoice.InvoiceName);
Console.WriteLine("Created: " + invoice.Created.ToString("dd/MM/yyyy"));
Console.WriteLine("Status: " + invoice.Status);
Console.WriteLine("Payment: " + invoice.Payment);
Console.WriteLine("Account Id: " + invoice.AccountId);
Console.WriteLine("Full Name: " + invoice.FullName);
Console.WriteLine("Phone: " + invoice.Phone);
Console.WriteLine("Address: " + invoice.Address);
Console.WriteLine("--------------------------");
});
}
}
}
}
Output
Invoice List
Invoice Id: 1
Invoice Name: Invoice 1
Created: 20/10/2023
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
--------------------------
Invoice Id: 2
Invoice Name: Invoice 2
Created: 21/11/2022
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
--------------------------
Invoice Id: 3
Invoice Name: Invoice 3
Created: 11/04/2022
Status: Status 2
Payment: Payment 2
Account Id: 2
Full Name: Name 2
Phone: 145232
Address: Address 2
--------------------------
Invoice Id: 4
Invoice Name: Invoice 4
Created: 16/07/2021
Status: Status 2
Payment: Payment 2
Account Id: 2
Full Name: Name 2
Phone: 145232
Address: Address 2
--------------------------
Invoice Id: 5
Invoice Name: Invoice 5
Created: 25/11/2023
Status: Status 3
Payment: Payment 3
Account Id: 3
Full Name: Name 3
Phone: 232343
Address: Address 3
--------------------------
Invoice Id: 6
Invoice Name: Invoice 6
Created: 18/11/2023
Status: Status 3
Payment: Payment 3
Account Id: 3
Full Name: Name 3
Phone: 232343
Address: Address 3
--------------------------
You can use combination of Join and Where as below:
using EntityFrameworkCore_ConsoleApp.Models;
namespace EntityFrameworkCore_ConsoleApp
{
public class Program
{
static void Main(string[] args)
{
using (var databaseContext = new DatabaseContext())
{
var invoices = (from account in databaseContext.Accounts
join invoice in databaseContext.Invoices
on account.Id equals invoice.AccountId
where invoice.Status == "Status 1"
select new
{
InvoiceId = invoice.Id,
InvoiceName = invoice.Name,
Created = invoice.Created,
Status = invoice.Status,
Payment = invoice.Payment,
AccountId = account.Id,
FullName = account.FullName,
Phone = account.Contact.Phone,
Address = account.Contact.Address
}).ToList();
Console.WriteLine("Invoice List");
invoices.ForEach(invoice =>
{
Console.WriteLine("Invoice Id: " + invoice.InvoiceId);
Console.WriteLine("Invoice Name: " + invoice.InvoiceName);
Console.WriteLine("Created: " + invoice.Created.ToString("dd/MM/yyyy"));
Console.WriteLine("Status: " + invoice.Status);
Console.WriteLine("Payment: " + invoice.Payment);
Console.WriteLine("Account Id: " + invoice.AccountId);
Console.WriteLine("Full Name: " + invoice.FullName);
Console.WriteLine("Phone: " + invoice.Phone);
Console.WriteLine("Address: " + invoice.Address);
Console.WriteLine("--------------------------");
});
}
}
}
}
Output
Invoice List
Invoice Id: 1
Invoice Name: Invoice 1
Created: 20/10/2023
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
--------------------------
Invoice Id: 2
Invoice Name: Invoice 2
Created: 21/11/2022
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
--------------------------
You can also join multiple tables as below:
using EntityFrameworkCore_ConsoleApp.Models;
namespace EntityFrameworkCore_ConsoleApp
{
public class Program
{
static void Main(string[] args)
{
using (var databaseContext = new DatabaseContext())
{
var invoices = (from account in databaseContext.Accounts
join invoice in databaseContext.Invoices
on account.Id equals invoice.AccountId
join invoiceDetails in databaseContext.InvoiceDetails
on invoice.Id equals invoiceDetails.InvoiceId
where invoice.Status == "Status 1"
select new
{
InvoiceId = invoice.Id,
InvoiceName = invoice.Name,
Created = invoice.Created,
Status = invoice.Status,
Payment = invoice.Payment,
AccountId = account.Id,
FullName = account.FullName,
Phone = account.Contact.Phone,
Address = account.Contact.Address,
ProductId = invoiceDetails.ProductId,
Price = invoiceDetails.Price,
Quantity = invoiceDetails.Quantity
}).ToList();
Console.WriteLine("Invoice List");
invoices.ForEach(invoice =>
{
Console.WriteLine("Invoice Id: " + invoice.InvoiceId);
Console.WriteLine("Invoice Name: " + invoice.InvoiceName);
Console.WriteLine("Created: " + invoice.Created.ToString("dd/MM/yyyy"));
Console.WriteLine("Status: " + invoice.Status);
Console.WriteLine("Payment: " + invoice.Payment);
Console.WriteLine("Account Id: " + invoice.AccountId);
Console.WriteLine("Full Name: " + invoice.FullName);
Console.WriteLine("Phone: " + invoice.Phone);
Console.WriteLine("Address: " + invoice.Address);
Console.WriteLine("Product Id: " + invoice.ProductId);
Console.WriteLine("Price: " + invoice.Price);
Console.WriteLine("Quantity: " + invoice.Quantity);
Console.WriteLine("--------------------------");
});
}
}
}
}
Output
Invoice List
Invoice Id: 1
Invoice Name: Invoice 1
Created: 20/10/2023
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
Product Id: 9
Price: 4.5
Quantity: 20
--------------------------
Invoice Id: 1
Invoice Name: Invoice 1
Created: 20/10/2023
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
Product Id: 10
Price: 11
Quantity: 21
--------------------------
Invoice Id: 1
Invoice Name: Invoice 1
Created: 20/10/2023
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
Product Id: 11
Price: 20
Quantity: 3
--------------------------
Invoice Id: 2
Invoice Name: Invoice 2
Created: 21/11/2022
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
Product Id: 9
Price: 4.5
Quantity: 20
--------------------------
Invoice Id: 2
Invoice Name: Invoice 2
Created: 21/11/2022
Status: Status 1
Payment: Payment 1
Account Id: 1
Full Name: Name 1
Phone: 123456
Address: Address 1
Product Id: 13
Price: 6
Quantity: 11
--------------------------