Using variables in LINQ Entity Framework expression inside a loop gets the last value

Question

I am trying to create a SQL query using C# Entity framework LINQ which result in a similar SQL like this:

select * from centros
where SUBSTRING(UPPER(c_nombre),1,1)='M'
and (SUBSTRING(UPPER(c_nombre),2,1)='A' OR SUBSTRING(UPPER(c_nombre),2,1)='Á')
and SUBSTRING(UPPER(c_nombre),3,1)='L'
order by c_nombre
limit 10

This is my code:

public List<Entidad> SearchByDescServer(string desc, int limit)
{
    List<Entidad> entidades = new List<Entidad>();
    IQueryable<IEntidad> models = entitySet.AsNoTracking();
    for (int index = 0; index < desc.Length; index++) {
        string currentChar = desc.Substring(index,1).ToUpper();
        models = models.Where(e=>e.Descripcion.Substring(index,1).ToUpper()==currentChar);
    }
    models = models.OrderBy(e => e.Descripcion).Take(limit);            
    foreach (IEntidad m in models.ToList()) {
        entidades.Add(m.ToEntidad());
    }
    return entidades;
}

The thing is easy, I am adding where clauses to the IQueryable object in a loop. I am using a PostgreSQL database. The debug show me this:

Executed DbCommand (12ms) [
Parameters=[
@__8__locals1_index_0='3', 
@__currentChar_1='A' (Size = 150), 
@__8__locals1_index_2='3',
@__currentChar_3='A' (Size = 150), 
@__8__locals1_index_4='3',
@__currentChar_5='A' (Size = 150), 
@__p_6='10'
], CommandType='Text', CommandTimeout='30']
      SELECT e.c_id, e.c_activo, e.c_codigo, e.c_nombre
      FROM centros AS e
      WHERE ((UPPER(SUBSTRING(e.c_nombre, @__8__locals1_index_0 + 1, 1)) = @__currentChar_1) AND (UPPER(SUBSTRING(e.c_nombre, @__8__locals1_index_2 + 1, 1)) = @__currentChar_3)) AND (UPPER(SUBSTRING(e.c_nombre, @__8__locals1_index_4 + 1, 1)) = @__currentChar_5)
      ORDER BY e.c_nombre
      LIMIT @__p_6

So what is happening is that index parameters are always the last value of index variable after the loop. Can anyone explain me this behavior??

Thanks

Guru Stron · Accepted Answer · 2020-06-07T22:12:22.010

Introduce temporary variable and use it in lambda:

for (int index = 0; index < desc.Length; index++) 
{
    var tmp = index;
    string currentChar = desc.Substring(index, 1).ToUpper();
    models = models.Where(e=>e.Descripcion.Substring(tmp, 1).ToUpper()==currentChar);
}

As for why - this is how C# closures in for loop work. In short - for your anonymous function e=>e.Descripcion.Substring(i, 1).ToUpper()==currentChar compiler generates special class which will hold captured i variable which will be updated from the first to the last value of index:

var actions = new List<Action>();
for (int i = 0; i < 10; i++)
{   
    actions.Add(() => Console.WriteLine(i));    
}

// will print 10 times "10"
foreach (var a in actions)
{
    a();
}

Bear in mind that foreach loop behaves differently:

var actions = new List<Action>();
foreach (var i in Enumerable.Range(0, 10))
{
    actions.Add(() => Console.WriteLine(i));    
}

// prints from 0 to 9
foreach (var a in actions)
{
    a();
}

For more information you can read this answer by Jon Skeet and/or this article.

Using variables in LINQ Entity Framework expression inside a loop gets the last value

1 Answers1