Compare two queries
I have a query that returns only the catechizers who went to the event:
var catequizandosCheked = (from cat in db.Catequizando
where cat.Eventos.Any(e => e.EventoID == eventoID)
select new PresencaEventoViewModel
{
CatequizandoID = cat.CatequizandoID
}).ToList();
And I have another query that returns all catechized:
var catequizandos = (from i in db.Inscricao
join c in db.Catequizando on i.CatequizandoID equals c.CatequizandoID
join p in db.Pessoa on c.CatequizandoID equals p.PessoaID
join g in db.Grupo on i.GrupoID equals g.GrupoID
where queryAnoPastoral.Contains(i.AnoPastoral)
select new PresencaEventoViewModel
{
Nome = p.Nome,
CatequizandoID = p.PessoaID,
AnoCatequese = i.AnoCatequese,
LetraGrupo = g.LetraGrupo,
Estado = !catequizandosCheked.Contains(p.PessoaID) ? "unchecked" : (catequizandosCheked.Contains(p.PessoaID) ? "checked" : null )
});
In Query catequizandos I intend to return the attribute Estado "cheked " or" Uncheked " if it is found or not, the value of the catechizandoid in query catequizandos
3 answers
Man, try replacing the line below by putting exists instead of containts:
!catequizandosCheked.Exists(x => x.CatequizandoID == p.PessoaID) ? "unchecked" : "checked"
@ WickedOne,
When you call the method ToList<T>() of a IQueryable<T>, you list the data and tell the LINQ you want to run the query in the database.
In this case, if it is SQL Server, you tell Provider that you want your Query Expression to be turned into a SQL script at the exact time of execution. Thus, the variable catequizandosCheked is of type List ' and is stored in memory.
When you try to mix Lambda expression with local data, the LINQ / Provider can't translate your LINQ into a SQL script and so it gives an error (I imagine that's your case).
In order not to "stuff too much sausage" you just need to change your Query Expression to:
var catequizandosCheked = (from cat in db.Catequizando
where cat.Eventos.Any(e => e.EventoID == eventoID)
select new PresencaEventoViewModel
{
CatequizandoID = cat.CatequizandoID
});
In this case, without the ToList<T>(), the variable catequizandosCheked becomes of type IQueryable<PresencaEventViewModel> and can still be translated into one script SQL and used in the other Query Expression.
I hope I helped.
Solution I got:
Estado = !catequizandosCheked.Contains(p.PessoaID) ? "unchecked" : "checked"