代码之家 › 专栏 › 技术社区 › Kashif

从LINQ生成SQL到SQL

linq-to-sql c#

Kashif · 技术社区 · 16 年前

以下代码

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

生成以下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

为什么这是针对产品名称的案例陈述,因此我的结果集中只有0。它应该生成类似于SQL的以下内容(其中ProductName类似于“%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%'

谢谢。

1 回复 | 直到 16 年前

Oded 16 年前

这一行就是您遇到问题的地方:

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

p.ProductName.Contains("hotel") 也返回 true 或 false ,或在SQL中 1 或 0 .

移动 Contains 到 where 子句和在您的选择中,只需使用:

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

推荐文章

SpaceCowboy74 Mohammad Aghazadeh · 使用Fluent Validation比较数组中的值

1 年前