Compare two querys

Question

I have a query that returns only the catechisms that 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 the 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 the query catequizandosI intend to return the attribute Estado "cheked" or "Uncheked" if found or not, the value of the query catequizandos

Answer 1

Dude, try replacing the line below by placing exists instead of containts:

!catequizandosCheked.Exists(x => x.CatequizandoID  == p.PessoaID) ? "unchecked" : "checked"

Answer 2

@Wickedone, When you call the method ToList<T>() of a IQueryable<T>, you enumerate the data and speak to the LINQ that you want to execute the query in the database.

In that case, if SQL Server, you tell the Provider who wants his Query Expression be transformed into a script SQL 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, LINQ/Provider cannot translate your LINQ for a script SQL and so it makes a mistake (I imagine that this is your case).

To not "fill up too much sausage" you just need to change your Query Expression for:

var catequizandosCheked = (from cat in db.Catequizando
                           where cat.Eventos.Any(e => e.EventoID == eventoID)
                           select new PresencaEventoViewModel
                           {
                               CatequizandoID = cat.CatequizandoID                                          
                           });

In that case, without the ToList<T>(), the variable catequizandosCheked becomes the type IQueryable<PresencaEventViewModel> and can still be translated into a script SQL and be used in the other Query Expression.

I hope I’ve helped.

Answer 3

Solution I obtained:

Estado = !catequizandosCheked.Contains(p.PessoaID) ? "unchecked" : "checked"