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

Last Reply 19 days ago By dharmendr

Posted 19 days 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 19 days ago.
Posted 19 days ago Modified on 18 days 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/