Entity Framework with Multiple Where clauses incorrect results -

i writing feature user can type in few words search against database. when moved systest, code stopped working correctly.

in systest, last word of search terms used in query.

i brought relevant code linqpad see if replicate issue. works correctly against dev database , continues fail against systest database

var cleantextparts = regex.replace("foot pain", @"[^\w]", " ", regexoptions.none)     .tolower()     .split(' ')     .where(s => !string.isnullorempty(s));  cleantextparts.dump();  var query = clinicalfindings.asqueryable(); foreach (var s in cleantextparts)     {         query = query.where(code => code.description.tolower().contains(s));     }  var results = query.tolist(); results.dump(); 

when run against dev, sql being generated , ran:

exec sp_executesql n'select [t0].[clinicalfindingsid], [t0].[id], [t0].[description], [t0.[preferred] [clinicalfindings] [t0] (lower([t0].[description]) @p0) , (lower([t0].[description]) @p1)',n'@p0     varchar(8000),@p1 varchar(8000)',@p0='%pain%',@p1='%foot%' 

and here against systest:

 exec sp_executesql n'select [t0].[clinicalfindingsid], [t0].[id], [t0].[description], [t0.[preferred]  [clinicalfindings] [t0]  (lower([t0].[description]) @p0) , (lower([t0].[description]) @p1)',n'@p0     varchar(8000),@p1 varchar(8000)',@p0='%pain%',@p1='%pain%' 

notice difference in parameters used clause: @p0='%pain%',@p1='%pain%' vs @p0='%foot%',@p1='%pain%'

dev sql 2008 r2 systest sql 2005

i planning move database 2008 r2 instance in systest test if problem caused database versions.

how fix without moving different server?

this known (problem|scope issue|way works) lambda expressions inside loops. found following thread first time ran , lifesaver: https://stackoverflow.com/a/295597/1803682

basically due reusing key in lambda expression. linked answer excellent job of explaining. quick , dirty fix being copy variable first:

foreach (var s in cleantextparts) {     var tmp = s;     query = query.where(code => code.description.tolower().contains(tmp)); } 

i think reason don't see behaviour in linqpad appears no longer issue of c# 5.0. not being linqpad user, guessing using c# 5.0 in linqpad , 4.0 on test server?