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

score 3 · Accepted Answer · answered Dec 19 '11 at 08:49

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

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

1 Answers1