Compare Dayofweek in an ASP MVC query

Question

I’m trying to make a query where I check if the day week is chosen 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 past value checkQua is "Sim", and if it is I will get the dates where the day of the week is Wednesday. I think the error is from here, because when sending the data to the Viewmodel resultPlaneamento 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.

Answer 1

You cannot use the property DayOfWeek in a LINQ to Entities.

You’ll 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 the EntityFunctions.DiffDays with any Sunday of the past as indicated in one of the links of the reference:

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

http://blog.abodit.com/2009/07/entity-framework-in-net-4-0/

http://c-sharp-snippets.blogspot.com.br/2011/12/getting-dayofweek-in-linq-to-entities.html

Answer 2

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