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

Question

How can I solve this problem?

Here is my code:

    DateTime dtInicio = new DateTime();
    DateTime dtFim = new DateTime();
    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"]))
        Convert.ToInt32(collection["StatusCliente"]);

    var listCLientResult = (from c in db.tbClientes
                           orderby c.id
                            where (c.effdt >= dtInicio || string.IsNullOrEmpty(collection["txtDtInicial"]) &&
                                 (c.effdt <= dtFim || string.IsNullOrEmpty(collection["txtDtFinal"])) &&
                                 (c.cod_status_viagem == codStatus || string.IsNullOrEmpty(collection["StatusCliente"])))
                                 select c);
    return View(listCLientResult);

The error I am getting is:

LINQ to Entities does not recognize the method 'System.String get_Item (System.String)', which can not be converted into an expression of the repository.

Answer 1

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.

Answer 2

The Linq query is ultimately transformed into an SQL query and LINQ doesn't know what to do with Session["UserName"] (that gets the "UserName" item).

A common way to workaround this is just to use a local variable to which you'll assign Session["UserName"] and that you'll use in your Linq query...

like

string loggedUserName = Session["LogedUsername"].ToString();
var userdetail = dc.faculties.Where(a => a.F_UserName.Equals(loggedUserName)).FirstOrDefault();

reference http://mvc4asp.blogspot.in/