Compare DayOfWeek in an ASP MVC query

4

I'm trying to make a query where I check if the week day is the chosen one to create a list of data that loads in a ViewModel.

Query:

var plan = db.Servicos.Where(s => (TecnicoResp.HasValue ? s.NumTransportado == TecnicoResp.Value : true)
                        && ((IdFornecedor.HasValue ? s.FornecedorID == IdFornecedor : true) && (estabelecimentosDrop.HasValue ? s.EstabelecimentoID == estabelecimentosDrop : true))
                        && (IdFornecedor.HasValue ? s.FornecedorID == IdFornecedor : true)
                        ).SelectMany(sc => sc.Planeamento).Where(p => (checkQua == "Sim" ? p.DataAssistenciaProgramada.Value.DayOfWeek == DayOfWeek.Wednesday:true));
//O erro encontra-se no where a seguir ao SelectMany
                List<FiltroSPlaneamentoViewModel> resultPlaneamento = FiltroSPlaneamentoViewModel.carregaListaSPlanPlanemaneto(plan.ToList());

So I see if the value passed checkQua is "Sim" , and if it is I'll get the dates where the day of the week is Wednesday. I think the error is from here, because when I send the data to the ViewModel resultPlaneamento I get the following error:

The specified type member 'DayOfWeek' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
    
asked by anonymous 18.02.2014 / 17:47

2 answers

2

You can not use the DayOfWeek property in a LINQ to Entities.

You will have to use the special function: SqlFunctions.DatePart("weekday", data) :

var diaDaSemanaDesejado = 1; // segunda
var r = db.Entidade.Where(
    e => SqlFunctions.DatePart("weekday", e.StartDateTime) == diaDaSemana);

Another alternative would be to use EntityFunctions.DiffDays with any past Sunday so, as indicated in one of the reference links:

var domingoNoPassado = new DateTime(1753, 1, 7);
var diaDaSemanaDesejado = 1; // segunda
var r = db.Entidade.Where(
    e => EntityFunctions.DiffDays(domingoNoPassado, e.StartDateTime) % 7 == diaDaSemana);

Reference

link

link

    
18.02.2014 / 18:12
0

This error means that LINQ can not interpret its expression in execution. You have to calculate the day of the week out of the expression before and then pass that calculated value by varable.

    
18.02.2014 / 18:11