Linq to Sql - Converting a join and sum from sql to linq....

Question

I have crawled over several of the various questions on Linq-to-SQL dealing with joins, counts, sums and etc.. But I am just having a difficult time grasping how to do the following simple SQL Statement...

SELECT     
   tblTechItems.ItemName, 
   SUM(tblTechInventory.EntityTypeUID) AS TotalOfItemByType
FROM         
   tblTechInventory 
INNER JOIN
   tblTechItems ON tblTechInventory.ItemUID = tblTechItems.ItemUID
GROUP BY 
   tblTechInventory.StatusUID, 
   tblTechItems.ItemName, 
   tblTechInventory.InventoryUID
HAVING      
   tblTechInventory.StatusUID = 26

Answer 1

Try this:

var query = from e in db.tblTechInventory
            join f in db.tblTechItems on e.ItemUID equals f.ItemUID into x
            from y in x
            group e by new { e.StatusUID, y.ItemName, e.InventoryUID } into g
            where e.StatusUID == 26
            select new {
                g.Key.ItemName,
                TotalOfItemByType = g.Sum(e => e.EntityTypeUID)
            };

Answer 2

I'll give it a shot...

var results = tblTechInventory
  .Join(tblTechItems, i=> i.ItemUID, o => o.ItemUID, (i,o) => new {i.ItemName, o.EntityTypeUID, o.StatusUID, i.ItemName, o.InventoryUID})
  .Where(o => o.StatusUID == 26)
  .GroupBy(g => new {g.StatusUID, g.ItemName, g.InventoryUID}, (gr, items) => new {gr.Key.ItemName, items.Sum(i => i.EntityTypeUID)});