How do I write this crosstab type query in Linq (tables & data provided)

Question

This is a simple sort of crosstab query. I am having difficulty transferring my SQL knowledge to Linq and because it is hard to test (I have not gotten linqpad running properly for WP7 development) I can't really "play" easily. CREATE TABLE Store ( StoreID INT NOT NULL, StoreName VARCHAR(10) NOT NULL ) INSERT INTO Store (StoreID,StoreName) VALUES (1,'Store A') INSERT INTO Store (StoreID,StoreName) VALUES (2,'Store B') CREATE TABLE ProductType ( ProductTypeID INT NOT NULL, ProductTypeName VARCHAR(20) NOT NULL ) INSERT INTO ProductType (ProductTypeID,ProductTypeName) VALUES (1,'Clothing') INSERT INTO ProductType (ProductTypeID,ProductTypeName) VALUES (2,'Food') CREATE TABLE Product ( ProductID INT NOT NULL, ProductTypeID INT NOT NULL, ProductName VARCHAR(20) NOT NULL ) INSERT INTO Product (ProductID,ProductTypeID,ProductName) VALUES (1,1,'Hat') INSERT INTO Product (ProductID,ProductTypeID,ProductName) VALUES (2,1,'Shirt') INSERT INTO Product (ProductID,ProductTypeID,ProductName) VALUES (3,1,'Pant') INSERT INTO Product (ProductID,ProductTypeID,ProductName) VALUES (4,2,'Orange') INSERT INTO Product (ProductID,ProductTypeID,ProductName) VALUES (5,2,'Apple') CREATE TABLE Purchase ( PurchaseID INT NOT NULL, ProductID INT NOT NULL, StoreID INT NOT NULL, Quantity INT NULL, Amount INT NULL ) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (1,1,1,5,10) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (2,2,1,4,12) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (3,3,1,1,10) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (4,4,1,3,16) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (5,5,1,7,12) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (6,1,2,10,22) INSERT INTO Purchase (PurchaseID,ProductID,StoreID,Quantity,Amount) VALUES (7,2,2,8,26) SELECT s.StoreName ,SUM(CASE WHEN t.ProductTypeID=1 THEN Amount ELSE 0 END ) as ClothingAmount ,SUM(CASE WHEN t.ProductTypeID=2 THEN Amount ELSE 0 END )AS FoodQuantity ,SUM(CASE WHEN t.ProductTypeID=1 THEN Quantity ELSE 0 END ) as ClothingAmount ,SUM(CASE WHEN t.ProductTypeID=2 THEN Quantity ELSE 0 END )AS FoodQuantity FROM Purchase u INNER JOIN Product r ON u.ProductID=r.ProductID INNER JOIN ProductType t ON r.ProductTypeID=t.ProductTypeID INNER JOIN Store s ON s.StoreID=u.StoreID GROUP BY s.StoreName JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

The following is a 1:1 translation of your SQL query statement in LINQ: from u in Purchases join r in Products on u.ProductID equals r.ProductID join t in ProductTypes on r.ProductTypeID equals t.ProductTypeID join s in Stores on u.StoreID equals s.StoreID group new {Purchase = u, Product = r, ProductType = t, Store = s} by s.StoreName into grouped select new { StoreName = grouped.Key, ClothingAmount = grouped.Sum(entry => entry.ProductType.ProductTypeID == 1 ? entry.Purchase.Amount : 0), FoodAmount = grouped.Sum(entry => entry.ProductType.ProductTypeID == 2 ? entry.Purchase.Amount : 0), ClothingQuantity = grouped.Sum(entry => entry.ProductType.ProductTypeID == 1 ? entry.Purchase.Quantity : 0), FoodQuantity = grouped.Sum(entry => entry.ProductType.ProductTypeID == 2 ? entry.Purchase.Quantity : 0) } which gets translated into the following SQL: SELECT SUM( (CASE WHEN [t2].[ProductTypeID] = @p0 THEN [t0].[Amount] ELSE @p1 END)) AS [ClothingAmount], SUM( (CASE WHEN [t2].[ProductTypeID] = @p2 THEN [t0].[Amount] ELSE @p3 END)) AS [FoodAmount], SUM( (CASE WHEN [t2].[ProductTypeID] = @p4 THEN [t0].[Quantity] ELSE @p5 END)) AS [ClothingQuantity], SUM( (CASE WHEN [t2].[ProductTypeID] = @p6 THEN [t0].[Quantity] ELSE @p7 END)) AS [FoodQuantity], [t3].[StoreName] FROM [Purchase] AS [t0] INNER JOIN [Product] AS [t1] ON [t0].[ProductID] = [t1].[ProductID] INNER JOIN [ProductType] AS [t2] ON [t1].[ProductTypeID] = [t2].[ProductTypeID] INNER JOIN [Store] AS [t3] ON [t0].[StoreID] = [t3].[StoreID] GROUP BY [t3].[StoreName] Note however that it is highly recommended to set proper primary and foreign keys on the tables. Once that's done (and the Model is updated accordingly) the same query can be reduced to: from purchase in Purchases group purchase by purchase.Store.StoreName into grouped select new { StoreName = grouped.Key, ClothingAmount = grouped.Sum(purchase => purchase.Product.ProductTypeID == 1 ? purchase.Amount : 0), FoodAmount = grouped.Sum(purchase => purchase.Product.ProductTypeID == 2 ? purchase.Amount : 0), ClothingQuantity = grouped.Sum(purchase => purchase.Product.ProductTypeID == 1 ? purchase.Quantity : 0), FoodQuantity = grouped.Sum(purchase => purchase.Product.ProductTypeID == 2 ? purchase.Quantity : 0) }