Need Help Converting Sql Query to EF

Question

SELECT DISTINCT Item.ItemId
, Item.ITEMNAME
FROM DP_AX_PROD.dbo.PRODTABLE Prod
INNER JOIN DP_AX_PROD.dbo.INVENTTABLE Item
ON Prod.ItemId = Item.ItemId
AND Prod.DataAreaid = Item.DataAreaID
WHERE LEFT(Item.ITEMGROUPID, 7) = 'RMTHFLM'
AND Prod.DataAreaid = 'PROD'

So far I have:

var query = (from prod in db.ProdTable
join item in db.InventTable on prod.ItemId equals item.ItemID
select prod).Distinct();

            return query.ToList();

Any help would be greatly appreciated. Thank you!

Answer 1

@Nick R , based on my test, you could refer to the following code to convert Sql Query to EF .

Code example:

 TestDBEntities db=new TestDBEntities();  
            var query = (from prod in db.ProdTables.ToList()  
                         join item in db.InventTables.ToList() on new { prod.ItemId, prod.DataAreaID } equals new { item.ItemId, item.DataAreaID }  
                         where item.ItemGroup.ToString().Substring(0, 7) == "RMTHFLM" && prod.DataAreaID == "Prod"  
                         select new  
                         {  
                             item.ItemId,  
                             item.ItemName  
                         }).Distinct().ToList() ;  

Result in sql and in ef:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.