Join two table in MVC Core

Question

below is two classess. Employee and Customer ,Employee is join with Customer

public partial class Employee {   
 
 public int Id { get; set; }     
 public string FirstName { get; set; } = null!;    
  public string? LastName { get; set; }      
  public string? Address { get; set; }

 } 


public partial class Customer
{
    public int CId { get; set; }

    [DisplayName("Name")]
    public string? CName { get; set; }
	[DisplayName("Address")]
	public string? CAddress { get; set; }


	[DisplayName("Employee")]
	public int? Id { get; set; }

}

 public async Task<IActionResult> Index()        
 {          
   return _context.Customers != null ?         
   View(await _context.Customers.ToListAsync()) : Problem("Entity Set 'Demo1Context.Customers'  is null.");                   
    } 		

Output

Answer 1

Hi @akhter hussain

Join two table in MVC Core

You can use left outer joins to join the Customer and Employee table and based on the Customer.Id and Employee.ID property.

Try to use the following query statements:

            var customerlist = (from c in _context.Customers
                                join e in _context.Employees on c.Id equals e.Id into ce
                                from sub in ce.DefaultIfEmpty()
                                select new CustomerViewModel()
                                {
                                    No = c.CId,
                                    Name = c.CName,
                                    Address = c.CAddress,
                                    Employee_Name = $"{sub.FirstName} {sub.LastName}",
                                }).ToList();

Or

           var customer = await  _context.Customers
                .GroupJoin(_context.Employees, cus=>cus.Id, emp=>emp.Id,(cus,emp)=>new { key = cus, employee = emp})
                .Select(c=> new CustomerViewModel() { 
                    No= c.key.CId, 
                    Name = c.key.CName,
                    Address = c.key.CAddress,
                    Employee_Name = $"{c.employee.FirstOrDefault().FirstName} {c.employee.FirstOrDefault().LastName}"
                }).ToListAsync();

After that return the result to the view page:

return _context.Customers != null ? View(customer) : Problem("Entity Set 'Demo1Context.Customers'  is null.");

I use the following test data:

The query result as below:

---

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.

Best regards,

Dillion