CODEWITHSUNDEEP
c#htmlasp.net-mvclinqentity-framework
2D3D
2D3D

Reputation: 353

An error while doing a join linq query

I'm trying to do this code: 

    public ActionResult JoinSupToPro()
    {

        SupplierDBContext dbS = new SupplierDBContext();
        var innerJoinQuery = from pro in db.Products join sup in dbS.Suppliers on pro.SupplierId equals sup.ID
        select new {Name= pro.Name,Price =pro.Price, SupplierName =sup.Name , Phone =sup.Phone};
        IndexModel m = new IndexModel();
        foreach (var item in innerJoinQuery)
        {
            SupplierProduct p = new SupplierProduct();
            p.SupplierName = item.SupplierName;
            p.Phone = item.Phone;
            p.Price = item.Price;
            p.ProductName = item.Name;
                m.MenuItems.Add(p);
        }

        return View(m.MenuItems.ToList());
    }

I'm getting this problem : The specified LINQ expression contains references to queries that are associated with different contexts. Any ideas?

Upvotes: 0

Views: 203

Answers (2)

2D3D
2D3D

Reputation: 353

While working I found that the solution to my answers is to separate the 2 queries and then join between them like so:

    public ActionResult JoinSupToPro()
    {

        List<Supplier> dbS = new SupplierDBContext().Suppliers.ToList();
        List<Product> prod = db.Products.ToList();
        var innerJoinQuery = from pro in prod join sup in dbS on pro.SupplierId equals sup.ID
        select new {Name= pro.Name,Price =pro.Price, SupplierName =sup.Name , Phone =sup.Phone};
        IndexModel m = new IndexModel();
        m.MenuItems = new List<SupplierProduct>();
        foreach (var item in innerJoinQuery)
        {
            SupplierProduct p = new SupplierProduct();
            p.SupplierName = item.SupplierName;
            p.Phone = item.Phone;
            p.Price = item.Price;
            p.ProductName = item.Name;
                m.MenuItems.Add(p);
        }

Upvotes: 0

Ali Adlavaran
Ali Adlavaran

Reputation: 3735

It looks like you are joining data from two different contexts: tms and entities.

This 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. (for all it knows, the tables might live in a different database)

You'd need to either move all your entities to a single context or execute both queries separately and then join them in memory. (use the first option if all tables are in the same DB, use the second if you have separate databases)

Upvotes: 3

Related Questions