CODEWITHSUNDEEP
entity-frameworkwhere-clause
Keith Sirmons
Keith Sirmons

Reputation: 8411

Entity Framework with Multiple Where clauses incorrect results

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?

Upvotes: 1

Views: 634

Answers (1)

Matthew
Matthew

Reputation: 9949

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?

Upvotes: 1

Related Questions