Nesting using Linq PredicateBuilder

Question

I use the 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 generated SQL looks like (just a 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 twice in SQL. Do not know why?

Answer 1

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

You close the loop variable. Instead, make a local copy of your id variable:

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

Since Linq is lazy, your predicate is Or and therefore id will only be evaluated when the query is executed, at which time the id value is the last element in the ids collection. The foreach behavior in this regard will be changed to C # 5, where this will no longer be a problem. For more information, read "Closing on a loop variable is considered harmful."

Answer 2

If everything you do and your list is not long, you do not need a predicate creator.

  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, you need to completely create the OR clause, then And all this at a time. In addition, you need to use the local variable id, otherwise it will close the iteration variable and get only its last value.

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