LINQ to Entities does not recognize the method 'System.String get_Item (System.String)',

asp.net-mvc, c#, linq-to-entities

Solution

Linq queries performed against a database are translated to SQL before they can be executed; but `collection["txtDtInicial"]` can't be translated to SQL, because there is no equivalent SQL syntax, and anyway the database doesn't have access to `collection`. You need to extract `collection["txtDtInicial"]` to a variable first, and use only this variable in the query.

Here's what I would do:

DateTime dtInicio = DateTime.MinValue;
DateTime dtFim = DateTime.MaxValue;
Int32 codStatus = 0;

if(!string.IsNullOrEmpty(collection["txtDtInicial"]))
    dtInicio = Convert.ToDateTime(collection["txtDtInicial"]);
if(!string.IsNullOrEmpty(collection["txtDtFinal"]))
    dtFim = Convert.ToDateTime(collection["txtDtFinal"]);
if (!string.IsNullOrEmpty(collection["StatusCliente"]))
    codStatus = Convert.ToInt32(collection["StatusCliente"]);

var listCLientResult = (from c in db.tbClientes
                       orderby c.id
                        where (c.effdt >= dtInicio) &&
                             (c.effdt <= dtFim) &&
                             (c.cod_status_viagem == codStatus)
                             select c);
return View(listCLientResult);

By initializing `dtInicio` and `dtFim` to MinValue and MaxValue, you don't need to check whether they are defined in the query.

Problem

Original source

Related problems

• Creating dynamic queries with entity framework
• LINQ to Entities does not recognize the method