c# – Compare DayOfWeek in an ASP MVC query

Question:

I'm trying to make a query where I check if the day of the 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 the value passed to checkQua is "Sim" , and if so, I'll 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:

You cannot 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 Sunday in the past, as indicated in one of the links in 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

Post Views: 0
Scroll to Top