Entity Framework with Multiple Where clauses incorrect results

Question

I am writing a feature where the user can type in a few words to search against the database. When I moved this to systest, it code stopped working correctly.

In systest, only the last word of the search terms is used in the query.

I brought the relevant code into LinqPad to see if I could replicate the issue. It works correctly against the dev database and continues to fail against the 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 I run it against Dev, this is the SQL that is being generated and ran:

exec sp_executesql N'SELECT [t0].[ClinicalFindingsID], [t0].[ID], [t0].[Description], [t0.[Preferred]
FROM [ClinicalFindings] AS [t0]
WHERE (LOWER([t0].[Description]) LIKE @p0) AND (LOWER([t0].[Description]) LIKE @p1)',N'@p0     varchar(8000),@p1 varchar(8000)',@p0='%pain%',@p1='%foot%'

And here it is against Systest:

 exec sp_executesql N'SELECT [t0].[ClinicalFindingsID], [t0].[ID], [t0].[Description], [t0.[Preferred]
 FROM [ClinicalFindings] AS [t0]
 WHERE (LOWER([t0].[Description]) LIKE @p0) AND (LOWER([t0].[Description]) LIKE @p1)',N'@p0     varchar(8000),@p1 varchar(8000)',@p0='%pain%',@p1='%pain%'

Notice the difference in the parameters used for the where clause: @p0='%pain%',@p1='%pain%' vs @p0='%foot%',@p1='%pain%'

Dev is SQL 2008 R2 Systest is SQL 2005

I am planning to move the database to a 2008 R2 instance in Systest to test if the problem truly is caused by the database versions.

How would I fix this without moving to a different server?

score 1 · Accepted Answer · edited May 23 '17 at 11:49

This is a known (problem|scope issue|way it works) for lambda expressions inside loops. I found the following SO thread the first time I ran into this and it was a lifesaver: https://stackoverflow.com/a/295597/1803682

Basically it is due to reusing the key in the lambda expression. The linked answer does an excellent job of explaining. The quick and dirty fix being to copy the variable first:

foreach (var s in cleanTextParts) {
    var tmp = s;
    query = query.Where(code => code.Description.ToLower().Contains(tmp));
}

I think the reason you don't see this behaviour in LinqPad is it appears this is no longer an issue as of C# 5.0. Not being a LinqPad user, I am guessing you were using C# 5.0 in LinqPad and 4.0 on your test server?

No.. I think you misread my statement about LinqPad. It continued to fail with LinqPad in systest. It failed in both .NET 3.5 and 4.0 versions for LinqPad. I still do not know what the difference is between my Dev workstation and Systest as far as .NET is concerned. They have the same service packs and versions installed. Thanks for your help. — Keith Sirmons, Sep 06 '13 at 17:39

Entity Framework with Multiple Where clauses incorrect results

1 Answers1