Rewrite sql query to LINQ. Can't find an error

Question

This is an SQL query:

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

I need to rewrite it to LINQ. So this is my 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();

I spent about three ours, but can't find an error. Original SQL query returns 16 rows, but my LINQ code returns only 13 of the. Unfortunately I have only one navigation property (Web_Vendor <-> Web_Promotion). I think that an error in the second part of my query:

||
                        (_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)))

Can any expert check my code and help me? Correct data: http://prntscr.com/9a5xwu Linq data (not correct) contains the same data as correct instead of values where PercentOff is null. The main problem is that LINQ generate inner join instead of left join in this place: http://prntscr.com/9a6stb

A big clue would be to see the data in the rows that are being correctly picked up by both, and the rows that are being missed by the query you think isn't working. (I don't know much about Linq, but it seems to me that there might be a difference between looking for the string value 'True' and looking for a boolean of `true`?) — Matt Gibson, Dec 04 '15 at 08:54
First I recommend you to compare your Linq query and SQL query. You may use SQL Profiler. https://www.youtube.com/watch?v=mJ8Dyv4Uk6E — zapoo, Dec 04 '15 at 09:04
Personally, I wouldn't do these sort of queries in LINQ, but rather have a stored procedure that I'd call. Though views on this differ: http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures — ManOnAMission, Dec 04 '15 at 10:08

score 0 · Answer 1 · answered Dec 04 '15 at 09:54

since you say that your linq data miss the case when PercentOff = null, i'd focus on that

I guess your "PercentOff" in Linq is Percent property, and i see that you have in your where: "x.WebPromotion.Percent != 0"

Is that a nullable value or you convert the null to the default property type, that is 0?

couldn't be that null is converted to 0 and then the query skip it?

Rewrite sql query to LINQ. Can't find an error

1 Answers1