CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
thetipsyhacker
thetipsyhacker

Reputation: 1412

Combining Two Different DbContexts (LINQ) from Separate Databases

I am getting the error:

The specified LINQ expression contains references to queries that are associated with different contexts.

because I'm trying to combine two different DbContexts, each from separate databases.

I was trying to convert this SQL:

SELECT orderformdump.itemno,basedescription,info,upc,CAST(UNITPRICE AS DECIMAL(18,2)),caseqty, sum(qty) AS userquantity
FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] 
LEFT JOIN PPPLTD.dbo.ICPRICP ON replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],'-','') = ICPRICP.ITEMNO
LEFT JOIN PPPLTD.dbo.WEBITEMINFO ON ORDERFORMDUMP.ITEMNO = WEBITEMINFO.ITEMNO
LEFT JOIN pppltd.dbo.weboeordh ON [user] = @username
LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,'-','') and weboeordd.ORDUNIQ = weboeordh.orduniq
Left JOIN pppltd.dbo.weboeordsubmit ON weboeordsubmit.orduniq = weboeordd.ORDUNIQ and weboeordd.ORDUNIQ != weboeordsubmit.orduniq
LEFT JOIN PPPLTD.dbo.WEBLOGINACCESS ON WEBLOGINACCESS.[USER] = @username
LEFT JOIN PPPLTD.dbo.ARCUS ON ARCUS.IDCUST = WEBLOGINACCESS.CUSTID
where (allowinbc = 'Yes' or allowinab = 'Yes') 
AND [PRICELIST] = ARCUS.PRICLIST 
and [CURRENCY] = 'CDN' and DPRICETYPE = 1
and (itemgroup like '%' + @search + '%' or itemtype like '%' + @search + '%' or itembrand like '%' + @search + '%' 
or subcat  like '%' + @search + '%' or orderformdump.description  like '%' + @search + '%' or basedescription like '%'+ @search + '%' 
or orderformdump.ITEMNO like '%'+@search+'%' or UPC like '%'+@search+'%' or (select top 1 1 from pppltd.dbo.ICITEMO where OPTFIELD like 'UPC%' and VALUE like '%'+@search+'%' 
and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) 
group by ORDERFORMDUMP.ITEMNO,BASEDESCRIPTION,info,UPC,CAST(UNITPRICE AS DECIMAL(18,2)),caseqty
order by basedescription

to LINQ:

        var qty = db.WebOEOrdD.Sum(d => d.QTY);
        var unitPrice = db.IcPricP.Select(p => decimal.Round(p.UNITPRICE, 2));

        var query = from item in dbData.OrderFormDump
                    join icp in db.IcPricP on item.ITEMNO.Replace("-", "") equals icp.ITEMNO
                    join itemInfo in db.WebItemInfo on item.ITEMNO equals itemInfo.ITEMNO
                    join weboeordh in db.WebOEOrdH on username equals weboeordh.USER
                    join weboeordd in db.WebOEOrdD on new { itemno = item.ITEMNO.Replace("-", ""), orduniq = weboeordh.ORDUNIQ } equals new { itemno = weboeordd.ITEMNO, orduniq = weboeordd.ORDUNIQ }
                    join weboeordsubmit in db.WebOEOrdSubmit on weboeordd.ORDUNIQ equals weboeordsubmit.ORDUNIQ where weboeordsubmit.ORDUNIQ != weboeordd.ORDUNIQ
                    join webloginaccess in db.WebLoginAccess on username equals  webloginaccess.USER
                    join arcus in db.Arcus on webloginaccess.CUSTID equals arcus.IDCUST
                    where (item.ALLOWINBC == "Yes" && item.ALLOWINAB == "Yes")
                    && icp.PRICELIST == arcus.PRICLIST
                    && icp.CURRENCY == "CDN" && icp.DPRICETYPE == 1
                    && (item.BASEDESCRIPTION.Contains(searchword) || item.DESCRIPTION.Contains(searchword) || item.CATEGORY.Contains(searchword) || item.FOODACCSPEC.Contains(searchword) || item.ITEMBRAND.Contains(searchword) || item.ITEMGROUP.Contains(searchword) || item.ITEMNO.Contains(searchword) || item.ITEMSUBTYPE.Contains(searchword) || item.ITEMTYPE.Contains(searchword) || itemInfo.INFO.Contains(searchword) || item.UPC.Contains(searchword) || item.UPC.Substring(2, 10).Contains(searchword))
                    orderby item.BASEDESCRIPTION
                    group item by new { item.ITEMNO, item.BASEDESCRIPTION, itemInfo.INFO, item.UPC, unitPrice, item.CASEQTY, qty } into items
                    select new { items.Key.ITEMNO, items.Key.BASEDESCRIPTION, items.Key.INFO, items.Key.UPC, unitPrice, items.Key.CASEQTY, qty };

Am I converting it correctly? Do I have to create two separate queries and combine them? Lastly, can I combine two different databases into one dbContext? If so, how do I do this?

Thank you.

UPDATE

I tried adding tables from another database into my main dbContext and I'm getting this error:

The entity type ORDERFORMDUMP is not part of the model for the current context.

UPDATE #2

What I did to solve my problem doesn't really answer this question, however it is a good workaround.

What I did was I took the only table in my query that was part of a different DbContext and another database (ORDERFORMDUMP), and I copied that table to the main database in SQL Server. As a result, I was able to add that table to my main DbContext.

Upvotes: 1

Views: 798

Answers (3)

thetipsyhacker
thetipsyhacker

Reputation: 1412

What I did to solve my problem doesn't really answer this question, however it is a good workaround.

What I did was I took the only table in my query that was part of a different DbContext and another database (ORDERFORMDUMP), and I copied that table to the main database in SQL Server. As a result, I was able to add that table to my main DbContext.

Upvotes: 0

Cam Bruce
Cam Bruce

Reputation: 5689

There are a couple ways of accomplishing this.

I would recommending creating a view of your query, then bind it to a new linq object, and then you can query off of that. Trying to write your query fully in linq is possible, but it would just get nasty, given you are working with multiple data contexts.

Upvotes: 1

Tarek
Tarek

Reputation: 1279

You are going to have to have two different queries, materialize the query results, then do whatever operations you want to do on them in memory.

Upvotes: 0

Related Questions