Generated sql from LINQ to SQL

Question

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.

Answer 1

This line is where you are having the problem:

ProductName = p.ProductName.Contains("hotel"),

p.ProductName.Contains("hotel") returns either true or false, or in SQL 1 or 0.

Move the Contains to the where clause and in your select simply use:

ProductName = p.ProductName,

Alltogether:

ProductPricesDataContext db = new ProductPricesDataContext();

var products = from p in db.Products
           where p.ProductFields.Count > 3
           where p.ProductName.Contains("hotel")
           select new
            {
                ProductIDD = p.ProductId,
                ProductName = p.ProductName,
                NumbeOfProd = p.ProductFields.Count,
                totalFields = p.ProductFields.Sum(o => o.FieldId + o.FieldId)
            };