error with EF and Mysql query

Question

I have the following consultation:

 var produto = (from a in context.ItensReceita
                join b in context.Receita on a.t0080_id_receita equals b.t0080_id_receita
                join c in context.Medicamento on a.t0080_id_receita equals c.t0051_id_medicamento     
                join d in context.Pessoa on b.t0031_id_paciente equals d.t0031_id_pesso
                   where b.t0020_id_empresa == idEmpresa
                   && Convert.ToDateTime(b.t0080_dt_venda) >= Convert.ToDateTime("2017/01/01")
                   && Convert.ToDateTime(b.t0080_dt_venda) <= Convert.ToDateTime("2017/01/31")
                   select new { b.t0080_dt_venda, d.t0031_nome, c.t0051_nome, a.t0081_lote, a.t0081_qtde, b.t0080_status });

but make that mistake:

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.

is not accepting conversation of dates, how can I solve it ?

Answer 1

Linq does not support the method ToDateTime

What you can do is create a variable and then use it in your query

var data1 = Convert.ToDateTime("2017/01/01");
var data2 = Convert.ToDateTime("2017/01/31");

and use these two variables in your query

var produto = (from a in context.ItensReceita
                join b in context.Receita on a.t0080_id_receita equals b.t0080_id_receita
                join c in context.Medicamento on a.t0080_id_receita equals c.t0051_id_medicamento     
                join d in context.Pessoa on b.t0031_id_paciente equals d.t0031_id_pesso
                   where b.t0020_id_empresa == idEmpresa
                   && DbFunctions.TruncateTime(b.t0080_dt_venda) >= data1
                   && DbFunctions.TruncateTime(b.t0080_dt_venda) <= data2
                   select new { b.t0080_dt_venda, d.t0031_nome, c.t0051_nome, a.t0081_lote, a.t0081_qtde, b.t0080_status });

One detail, is that you will have to use the DbFunctions.TruncateTime that is part of the namespace System.Data.Entity