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.

Oded · Accepted Answer · 2010-06-17 19:53:58Z

3

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

answered Jun 17, 2010 at 19:53

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kashif Over a year ago

If I have to return ProductNames contains 'hotel' I need to add this in Where? And this line is just returning true or false that returned ProductName contains 'hotel' in it or not?

Oded Over a year ago

@Muhammad Kashif Nadeem - the Contains function returns a boolean. If you want to limit the results to those where the product name contains hotel, then it belongs in the where clause. When selecting, the result set will only contain results matching the where clause.

Collectives™ on Stack Overflow

Generated sql from LINQ to SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related