Join multiple tables having different context (database) using Linq in ASP.Net

Last Reply 2 months ago By dharmendr

Posted 2 months ago

how to join table with different context using linq (Lambda)

private ResourceEntities3 db3 = new ResourceEntities3();
private ResourceEntities db = new ResourceEntities();
var result = db3.cndPersonalDetails.Join(db.registrationDetails,
             a => a.id, b => b.createdFor, (a, b) => new { a, b }).
             Join(db.Registrations, r => r.b.createdBy, reg => reg.id, (r, reg) => new { r, reg })
             .Where(m => m.r.b.createdBy == 5).ToList();

how can i use joins using different context ?

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago Modified on 2 months ago

Hi varun3752,

Joining data from two different contexts is not possible in LINQ as both have their own connection to the database and a completely separate model.

It's not possible for EF to convert this into a SQL statement. You need to execute both queries separately and then join them in memory.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Code

protected void Page_Load(object sender, EventArgs e)
{
    NorthwindEntities db1 = new NorthwindEntities();
    NorthwindEntities1 db2 = new NorthwindEntities1();
    var id = db1.Customers.Where(x => x.CustomerID == "TOMSP").Select(x => x.CustomerID).FirstOrDefault();

    List<cndPersonalDetail> result = db2.Customers
                .Join(db2.Orders, c => c.CustomerID, o => o.CustomerID, (a, b) => new { a, b })
                .Join(db2.Order_Details, o => o.b.OrderID, od => od.OrderID, (r, reg) => new { r, reg })
                .Where(m => m.r.b.CustomerID == id)
                .Select(m => new cndPersonalDetail
                {
                    CustomerId = m.r.a.CustomerID,
                    ContactName = m.r.a.ContactName,
                    OrderID = m.r.b.OrderID,
                    Date = m.r.b.RequiredDate,
                    Price = m.reg.UnitPrice
                }).ToList();
}

public class cndPersonalDetail
{
    public string CustomerId { get; set; }
    public string ContactName { get; set; }
    public int OrderID { get; set; }
    public DateTime? Date { get; set; }
    public decimal Price { get; set; }
}

For more details refer below links.

https://stackoverflow.com/questions/30235972/linq-lambda-join-2-or-more-tables-with-2-dbcontext

https://www.c-sharpcorner.com/UploadFile/ee01e6/how-to-join-two-tables-from-different-database-using-entity/