CODEWITHSUNDEEP
c#linq
Belliez
Belliez

Reputation: 5386

SQL Join table and SUM values

I have two tables:

Product and Stock

I currently select the contents of Products and make a 2nd call to the stock table to sum up the total stock for the productID which is very slow.

What I would like is to create a single call to the database to get the contents of the Product table and also sum the total of StockInHand in the stock table (linked to ProductID) something like below:

If someone can show me how I can successfully Join the tables and sum the QtyInHand of the Stock table in the same call for ProductID I would be really greatful.

My original code:

            var query = from products in data.Products
                        where products.Deleted == false
                        orderby products.FullPath
                        select new
                        {
                            ProductID = products.ProductID,
                            Description = products.Description,
                            Price = products.RetailPrice ?? 0,
>>>> VERY SLOW!             StockLevel = cProducts.GetStockTotalForProduct(products.ProductID), 
                            FullPath = products.FullPath
                        };

            if (query != null)
            {
                dgv.DataSource = query;
            }

I understand that I need to JOIN the tables, but I am unsure of the syntax to do this using LINQ:

    var query = 
                from product in data.Products
                join stock in data.ProductStocks on product.ProductID equals stock.ProductID
 DO SOMETHING CLEVER HERE!!
                select new
                {
                    ProductID = product.ProductID,
                    Description = product.Description,
                    Price = product.RetailPrice ?? 0,
                    StockLevel = >>>>>>>> CALL TO THE OTHER TABLE IS VERY SLOW,
                    FullPath = products.FullPath
                };

            if (query != null)
            {
                dgv.DataSource = query;
            }

Upvotes: 0

Views: 69

Answers (2)

Laurence
Laurence

Reputation: 10976

I think you are looking for a group by to do the sum:

var query = from p in data.Products
            join s in data.ProductStocks on p.ProductID equals s.ProductID
            group s by p into g
            select new {
                ProductID = g.Key.ProductID,
                Description = g.Key.Description,
                Price = g.Key.Price ?? 0,
                FullPath = g.Key.FullPath,
                StockLevel = g.Sum(s => s.StockInHand)
            };

Upvotes: 1

user1261620
user1261620

Reputation: 377

I think this should work:

 var query = from product in data.Products
                join stock in data.ProductStocks on product.ProductID equals stock.ProductID
                select new
                {
                    ProductID = product.ProductID,
                    Description = product.Description,
                    Price = product.RetailPrice ?? 0,
                    StockLevel = stock.StockLevel,
                    FullPath = products.FullPath
                };

Upvotes: 1

Related Questions