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

jason · Accepted Answer · 2010-10-13 21:25:29Z

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)
            };

edited Oct 13, 2010 at 21:25

answered Oct 13, 2010 at 21:09

jason

243k36 gold badges437 silver badges532 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danijels Over a year ago

There is no "tblTechInventory" table mentioned there.

jason Over a year ago

@danijels: My mistake; it was difficult to read his code before it was reformatted to not be on a single line. Please see my edit.

danijels · Accepted Answer · 2010-10-13 21:21:46Z

1

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)});

answered Oct 13, 2010 at 21:21

danijels

5,3214 gold badges30 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related