Nesting OR using Linq PredicateBuilder

Question

I am using predicate builder to write the following code:

IEnumerable<int> ids= new List<int> { 47, 48 };

var predicate = PredicateBuilder.False<Customer>();

predicate = predicate.And(x => x.CreatedAt >= fromDate && x.CreatedAt <= toDate);

foreach (var id in ids)
{
    predicate = predicate.Or(x => x.Source.Id == id);
}

var result = Database.Set<Customer>().AsExpandable()
                                     .Where(predicate)
                                     .ToList();

The SQL generated looks like (just the WHERE clause):

WHERE ([Filter6].[SourceId] IN (@p__linq__0,@p__linq__1))
AND ([Filter6].[CreatedAt] >= @p__linq__2)
AND ([Filter6].[CreatedAt] <= @p__linq__3)',
N'@p__linq__0 int,
@p__linq__1 int,
@p__linq__2 datetime2(7),
@p__linq__3 datetime2(7)',
@p__linq__0=48,
@p__linq__1=48,
@p__linq__2='2012-02-07 21:59:55.0437985',
@p__linq__3='2012-02-07 22:04:55.5748288'

It looks like id 48 got assigned twice in the SQL. Not sure why?

Pachisi

Answer 1

foreach (var id in ids)
{
    predicate = predicate.Or(x => x.Source.Id == id);
}

You are closing over the loop variable. Make a local copy of your id variable instead:

foreach (var id in ids)
{
    int localId = id;
    predicate = predicate.Or(x => x.Source.Id == localId);
}

Since Linq is lazy your Or predicate and hence id will only be evaluated when you execute the query and at that time the value of id is the last item in the ids collection. The behavior of foreach in this regard will be changed in C# 5 where this will not be a problem anymore. For more info read "Closing over the loop variable considered harmful"

Trigonometry

Answer 2

If that's all you're doing and your list isn't long, you don't need the predicate builder at all.

 var result = Database.Set<Customer>().AsExpandable() 
                                      .Where( x => x.CreatedAt >= fromDate
                                                   && x.CreatedAt <= toDate
                                                   && ids.Contains( x.Source.Id ) ) 
                                      .ToList();

If you are going to use the predicate builder then you need to build the OR clause completely, then AND all of it in one go. In addition, you need to use a local id variable otherwise it will close over the iteration variable and only get its last value bound.

// This should translate to True AND (u AND x) AND (FALSE OR y OR z)
var predicate = PredicateBuilder.True<Customer>();

predicate = predicate.And(x => x.CreatedAt >= fromDate && x.CreatedAt <= toDate);      

var idPredicate = PredicateBuilder.False<Customer>();      
foreach (var id in ids)      
{   
    var localId = id;   
    idPredicate = idPredicate.Or(x => x.Source.Id == localId);      
}

predicate = predicate.And( idPredicate );

Kohlrabi