EF6 query that behaves strangely using contains

Question

I have an EF6 SQL Server query that behaves strangely when it is supplied with a List of IDs to use. If bookGenieCategory = a value it works. If selectedAges is empty (count = 0) all is well. If the selectedAges contains values that exist in the ProductCategory.CategoryId column, the contains fails and NO rows are returned.

Note: AllocationCandidates is a view, which works properly on its own.

CREATE VIEW dbo.AllocationCandidate
AS
    SELECT         
        p.ProductID, p.SKU as ISBN, p.Name as Title, 
        pv.MSRP, pv.Price, pv.VariantID, pv.Inventory, 
        ISNULL(plt.DateLastTouched, GETDATE()) AS DateLastTouched, 
        JSON_VALUE(p.MiscText, '$.AgeId') AS AgeId, 
        JSON_VALUE(p.MiscText, '$.AgeName') AS AgeName
    FROM            
        dbo.Product AS p WITH (NOLOCK) 
    INNER JOIN
        dbo.ProductVariant AS pv WITH (NOLOCK) ON pv.ProductID = p.ProductID 
    LEFT OUTER JOIN
        dbo.KBOProductLastTouched AS plt WITH (NOLOCK) ON plt.ProductID = p.ProductID
    WHERE        
        (ISJSON(p.MiscText) = 1) 
        AND (p.Deleted = 0) 
        AND (p.Published = 1) 
        AND (pv.IsDefault = 1)
GO

Do I have a typo here or a misplaced parenthesis in the following query?

var returnList = (from ac in _db.AllocationCandidates
                  join pc in _db.ProductCategories on ac.ProductID equals pc.ProductID
                  where (bookGenieCategory == 0
                         || bookGenieCategory == pc.CategoryID)
                    &&
                    (selectedAges.Count == 0 ||
                     selectedAges.Contains(pc.CategoryID))
                  orderby ac.AgeId, ac.DateLastTouched descending
                  select ac).ToList();

Steve Py · Answer

Firstly, I would recommend extracting the conditionals outside of the Linq expression. If you only want to filter data if a value is provided, move the condition check outside of the Linq rather than embedding it inside the condition. This is generally easier to do with the Fluent Linq than Linq QL. You should also aim to leverage navigation properties for relationships between entities. This way an AllocationCandidate should have a collection of ProductCategories:

var query = _db.AllocationCandidates.AsQueryable();
if (bookGenieCategory != 0)
    query = query.Where(x => x.ProductCategories.Any(c => c.CategoryID == bookGenieCategory);

The next question is what does the selectedAges contains? There is an Age ID on the AllocationCandidate, but your original query is checking against the ProductCategory.CategoryId??

If the check should be against the AllocationCandidate.AgeId:

if (selectedAges.Any())
    query = query.Where(x => selectedAges.Contains(x.AgeID));

If the check is as you wrote it against the ProductCategory.CategoryId:

if (selectedAges.Any())
    query = query.Where(x => x.ProductCategories.Any(c => selectedAges.Contains(c.AgeID)));

Then add your order by and get your results:

var results = query.OrderBy(x => x.AgeId)
    .ThenByDescending(x => x.DateLastTouched);
    .ToList();

EF6 query that behaves strangely using contains

1 Answers