c# - 将 sql 查询重写为 LINQ。找不到错误

Question

这是一个 SQL 查询:

SELECT Website,VendorID,Name,LinkProduct,
            Link,Logo,Image,NameExtra as Industry,
            (SELECT [Percent] FROM Web_Promotion 
            WHERE Web_Promotion.VendorID=Web_Vendor.VendorID) 
            AS PercentOff  
            FROM Web_Vendor WHERE Active='1' AND 
            (VendorID IN (Select VendorID FROM Web_Promotion 
            WHERE VendorID<>'' AND Static='True' AND [Percent] <> '0' AND 
            ((Expires>=GETDATE()) OR (Expires IS NULL))) OR 
            VendorID IN (SELECT TOP 1 SC1 FROM NavItems 
            WHERE SC1=Web_Vendor.VendorID AND Promotion<>'' 
            AND ((PromotionStart<=GETDATE() AND PromotionEnd>=GETDATE()) 
            OR (PromotionStart<=GETDATE() AND PromotionEnd IS NULL))))
            ORDER BY NameExtra,Sequence 

我需要将其重写为 LINQ。所以这是我的 LINQ:

return await _db.Web_Vendor.
                    Where(x => !(x.WebPromotion.VendorID == string.Empty || x.WebPromotion.VendorID == null)
                    && x.WebPromotion.Static == true && x.WebPromotion.Percent != 0 &&
                    (x.WebPromotion.Expires >= DateTime.Now || x.WebPromotion.Expires == null)
                    ||
                    (_db.NavItems.Where(y => x.WebPromotion.VendorID == y.SC1
                        && !(y.Promotion == "" || y.Promotion == null)
                        && (y.PromotionStart <= DateTime.Now) && (y.PromotionEnd >= DateTime.Now || y.PromotionEnd == null))
                        .Select(g => g.SC1).Take(1).Contains(x.WebPromotion.VendorID)))
                    .Include(x => x.WebPromotion).Where(x => x.Active == true).OrderBy(x => x.NameExtra)
                    .ThenBy(x => x.Sequence).ToListAsync();

我花了大约三个时间，但找不到错误。原始 SQL 查询返回 16 行，但我的 LINQ 代码只返回 13 行。不幸的是，我只有一个导航属性 (Web_Vendor <-> Web_Promotion)。我认为查询的第二部分有错误:

||
                        (_db.NavItems.Where(y => x.WebPromotion.VendorID == y.SC1
                            && !(y.Promotion == "" || y.Promotion == null)
                            && (y.PromotionStart <= DateTime.Now) && (y.PromotionEnd >= DateTime.Now || y.PromotionEnd == null))
                            .Select(g => g.SC1).Take(1).Contains(x.WebPromotion.VendorID)))

任何专家都可以检查我的代码并帮助我吗？正确数据: http://prntscr.com/9a5xwuLinq 数据(不正确)包含与正确数据相同的数据，而不是 PercentOff 为 null 的值。主要问题是 LINQ 在这个地方生成内连接而不是左连接:http://prntscr.com/9a6stb

Answer 1

既然你说当 PercentOff = null 时你的 linq 数据没有出现这种情况，我会专注于此

我猜你在 Linq 中的“PercentOff”是 Percent 属性，我看到你在你的 where: "x.WebPromotion.Percent != 0"

这是一个可为 null 的值，还是您将 null 转换为默认属性类型，即 0？

不会是 null 被转换为 0 然后查询跳过它吧？