Error: the specified linq expression contains references to queries that are associated with different context

Question

I have this linq query that returns the revenue associated with every colorwaysid present within the given dates but it throws the SQLException:

the specified linq expression contains references to queries that are associated with different context

using (var eshaktiDb = new eshaktiEntities())
{
    using (var corpDb = new eshakti_corpEntities())
    {

        var queryResult = 
            from product in eshaktiDb.Products
            join oivs in corpDb.OrderitemvalueSplits on product.ProductId equals oivs.Productid
            join order in corpDb.Orders on oivs.Orderid equals order.OrderID
            where product.ColorWaysId != null && order.CrDate >= fromDate && order.CrDate <= toDate
            group oivs by product.ColorWaysId into g
            select new ColorwayReportResponse
            {
                colorwayId = (int)g.Key,
                revenue = (decimal)g.Sum(o => o.ActItemvalue + o.Custom)
            };
        return queryResult.ToList();
    }
}

How to fix this ? Can somebody help me with the query that would fit in, also since the query involves two different databases, how can I get my desired result ?

Not with pure EF Core, but with extensions it is possible. If you interested in, I can post sample. — Svyatoslav Danyliv, May 10 '22 at 16:00
Well, rethinking my suggestion. It. will not work, because mapping between two different contexts is different. Better create View, which contains multi-database query. — Svyatoslav Danyliv, May 10 '22 at 18:36

score 0 · Answer 1 · answered May 10 '22 at 16:31

I would separate this out into 2 separate queries starting with your corpDb:

 var queryResult = (select * from corpDb.OrderitemvalueSplits  join order in corpDb.Orders on oivs.Orderid equals order.OrderID).ToArray();

Then I would use perform the corresponding join afterwards in the other context.

var products = select * from product in eshaktiDb.Products join oivs in queryResult.OrderitemvalueSplits on product.ProductId equals oivs.Productid where product.ColorWaysId != null && order.CrDate >= fromDate && order.CrDate <= toDate
        group oivs by product.ColorWaysId into g
        select new ColorwayReportResponse
        {
            colorwayId = (int)g.Key,
            revenue = (decimal)g.Sum(o => o.ActItemvalue + o.Custom)
        };

There are other possible solutions to this issue here.

I'm not sure the above would work exactly in your code but generally you are not allowed to join within 2 separate contexts in Entity framework but you are allowed to pass arrays and other values between requests to accomplish the same task.

Error: the specified linq expression contains references to queries that are associated with different context

1 Answers1