Join method 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 = databaseContext.Accounts.Join(
databaseContext.Invoices,
account => account.Id,
invoice => invoice.AccountId,
(account, invoice) => 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 method as below:
using EntityFrameworkCore_ConsoleApp.Models;
namespace EntityFrameworkCore_ConsoleApp
{
public class Program
{
static void Main(string[] args)
{
using (var databaseContext = new DatabaseContext())
{
var invoices = databaseContext.Accounts.Join(
databaseContext.Invoices,
account => account.Id,
invoice => invoice.AccountId,
(account, invoice) => 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
}
).Where(invoice => invoice.Status == "Status 1").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 = databaseContext.Accounts.Join(
databaseContext.Invoices,
account => account.Id,
invoice => invoice.AccountId,
(account, invoice) => 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
}
)
.Join(
databaseContext.InvoiceDetails,
invoiceJoinAccount => invoiceJoinAccount.InvoiceId,
invoiceDetails => invoiceDetails.InvoiceId,
(invoiceJoinAccount, invoiceDetails) => new
{
InvoiceJoinAccount = invoiceJoinAccount,
InvoiceDetails = invoiceDetails
}
)
.Where(invoice => invoice.InvoiceJoinAccount.Status == "Status 1")
.ToList();
Console.WriteLine("Invoice List");
invoices.ForEach(invoice =>
{
Console.WriteLine("Invoice Id: " + invoice.InvoiceJoinAccount.InvoiceId);
Console.WriteLine("Invoice Name: " + invoice.InvoiceJoinAccount.InvoiceName);
Console.WriteLine("Created: " + invoice.InvoiceJoinAccount.Created.ToString("dd/MM/yyyy"));
Console.WriteLine("Status: " + invoice.InvoiceJoinAccount.Status);
Console.WriteLine("Payment: " + invoice.InvoiceJoinAccount.Payment);
Console.WriteLine("Account Id: " + invoice.InvoiceJoinAccount.AccountId);
Console.WriteLine("Full Name: " + invoice.InvoiceJoinAccount.FullName);
Console.WriteLine("Phone: " + invoice.InvoiceJoinAccount.Phone);
Console.WriteLine("Address: " + invoice.InvoiceJoinAccount.Address);
Console.WriteLine("Product Id: " + invoice.InvoiceDetails.ProductId);
Console.WriteLine("Price: " + invoice.InvoiceDetails.Price);
Console.WriteLine("Quantity: " + invoice.InvoiceDetails.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
--------------------------