Linq-to-SQL: Ignore null parameters from WHERE clause

The query below should return records that either have a matching Id supplied in ownerGroupIds or that match ownerUserId. However is ownerUserId is null, I want this part of the query to be ignored.

    public static int NumberUnderReview(int? own开发者_如何学PythonerUserId, List<int> ownerGroupIds)
    {
        return ( from c in db.Contacts
                 where 
                 c.Active == true 
                 &&
                 c.LastReviewedOn <= DateTime.Now.AddDays(-365) 
                 &&
                 ( // Owned by user
                    !ownerUserId.HasValue || 
                    c.OwnerUserId.Value == ownerUserId.Value
                 )
                 &&
                 ( // Owned by group
                    ownerGroupIds.Count == 0 ||
                    ownerGroupIds.Contains( c.OwnerGroupId.Value )
                 )
                 select c ).Count();
    }

However when a null is passed in for ownerUserId then I get the following error: Nullable object must have a value.

I get a tingling I may have to use a lambda expression in this instance?

your issue is that your are not passing in a nullable int, you are passing in a null.

try this:

Print(null);

private void Print(int? num)
{
     Console.WriteLine(num.Value);
}

and you get the same error.

It should work if you do this:

var q = ( from c in db.Contacts
                 where 
                 c.Active == true 
                 &&
                 c.LastReviewedOn <= DateTime.Now.AddDays(-365) 
                 &&
                 ( // Owned by group
                    ownerGroupIds.Count == 0 ||
                    ownerGroupIds.Contains( c.OwnerGroupId.Value )
                 )
                 select c );

if(ownerUserId != null && ownerUserId.HasValue)
     q = q.Where(p => p.OwnerUserId.Value == ownerUserId.Value);

return q.Count();

Have you some contacts with OwnerUserId null? If yes, c.OwnerUserId could be null and not having any value in c.OwnerUserId.Value

What about conditionally adding the where clause to the expression tree?

public static int NumberUnderReview(int? ownerUserId, List<int> ownerGroupIds)
    {

    var x = ( from c in db.Contacts
                 where 
                 c.Active == true 
                 &&
                 c.LastReviewedOn <= DateTime.Now.AddDays(-365) 
                 &&
                 ( // Owned by group
                    ownerGroupIds.Count == 0 ||
                    ownerGroupIds.Contains( c.OwnerGroupId.Value )
                 )
                 select c );

    if (ownerUserId.HasValue) {
        x = from a in x
            where c.OwnerUserId.Value == ownerUserId.Value
    }

    return x.Count();
    }

PROBLEM: "&&" and "||" is converted to a method like "AndCondition(a, b)", so "!a.HasValue || a.Value == b" becomes "OrCondition(!a.HasValue, a.Value == b);" The reason for this is probably to get a generic solution to work for both code and SQL statements. So instead, use the "?:" notation.

For more, see my blog post: http://peetbrits.wordpress.com/2008/10/18/linq-breaking-your-logic/

// New revised code.
public static int NumberUnderReview(int? ownerUserId, List<int> ownerGroupIds)
{
    return ( from c in db.Contacts
             where 
             c.Active == true 
             &&
             c.LastReviewedOn <= DateTime.Now.AddDays(-365) 
             &&
             ( // Owned by user
                // !ownerUserId.HasValue || 
                // c.OwnerUserId.Value == ownerUserId.Value
                ownerUserId.HasValue ? c.OwnerUserId.Value == ownerUserId.Value : true
             )
             &&
             ( // Owned by group
                // ownerGroupIds.Count == 0 ||
                // ownerGroupIds.Contains( c.OwnerGroupId.Value )
                ownerGroupIds.Count != 0 ? ownerGroupIds.Contains( c.OwnerGroupId.Value ) : true
             )
             select c ).Count();
}