Generated sql from LINQ to SQL
- by Muhammad Kashif Nadeem
Following code
ProductPricesDataContext db = new ProductPricesDataContext();
var products = from p in db.Products
where p.ProductFields.Count > 3
select new
{
ProductIDD = p.ProductId,
ProductName = p.ProductName.Contains("hotel"),
NumbeOfProd = p.ProductFields.Count,
totalFields = p.ProductFields.Sum(o => o.FieldId + o.FieldId)
};
Generated follwing sql
SELECT [t0].[ProductId] AS [ProductIDD],
(CASE
WHEN [t0].[ProductName] LIKE '%hotel%' THEN 1
WHEN NOT ([t0].[ProductName] LIKE '%hotel%') THEN 0
ELSE NULL
END) AS [ProductName],
( SELECT COUNT(*) FROM [dbo].[ProductField] AS [t2] WHERE [t2].[ProductId] = [t0].[ProductId] ) AS [NumbeOfProd],
( SELECT SUM([t3].[FieldId] + [t3].[FieldId]) FROM [dbo].[ProductField] AS [t3] WHERE [t3].[ProductId] = [t0].[ProductId]) AS [totalFields]
FROM [dbo].[Product] AS [t0]
WHERE (( SELECT COUNT(*) FROM [dbo].[ProductField] AS [t1] WHERE [t1].[ProductId] = [t0].[ProductId] )) > 3
Why is this CASE statement for ProductName and because of this instead of ProductName i am just getting 0 in my result set. It should generate sql like following, (where ProductName like '%hotel%'
SELECT [t0].[ProductId] AS [ProductIDD],
[ProductName],
( SELECT COUNT(*) FROM [dbo].[ProductField] AS [t2] WHERE [t2].[ProductId] = [t0].[ProductId] ) AS [NumbeOfProd],
( SELECT SUM([t3].[FieldId] + [t3].[FieldId]) FROM [dbo].[ProductField] AS [t3] WHERE [t3].[ProductId] = [t0].[ProductId]) AS [totalFields]
FROM [dbo].[Product] AS [t0]
WHERE (( SELECT COUNT(*) FROM [dbo].[ProductField] AS [t1] WHERE [t1].[ProductId] = [t0].[ProductId] )) > 3
AND t0.ProductName like '%hotel%'
Thanks.
