linqto - sql:忽略WHERE子句的空参数。

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.

下面的查询应该返回在ownerGroupIds中提供匹配Id的记录或匹配ownerUserId的记录。然而，ownerUserId是空的，我希望查询的这一部分被忽略。

    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
                 )
                 &&
                 ( // 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.

但是，当为ownerUserId传入null时，我将得到以下错误:Nullable对象必须具有一个值。

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

我可能需要用一个lambda表达式在这个例子中?

4 个解决方案

#1

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

您的问题是，您没有传递一个空值int，您正在传递一个空值。

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

#2

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

你和OwnerUserId null有联系吗?如果是,c。OwnerUserId可以是null，并且在c.OwnerUserId.Value中没有任何值

#3

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

有条件地将where子句添加到表达式树中怎么样?

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

#4

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.

问题:“&”和“||”被转换为“AndCondition(a, b)”,所以”!。HasValue | |。值== b“变成”或条件(!a。HasValue,。值= = b);“这样做的原因可能是为了让通用解决方案同时适用于代码和SQL语句。所以，用“?”:“符号。

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

更多信息，请参见我的博客: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();
}

#1