Compare two querys

1

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

    
asked by anonymous 16.05.2016 / 19:58

3 answers

0

Solution I got:

Estado = !catequizandosCheked.Contains(p.PessoaID) ? "unchecked" : "checked"
    
19.05.2016 / 15:55
0

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

!catequizandosCheked.Exists(x => x.CatequizandoID  == p.PessoaID) ? "unchecked" : "checked"
    
16.05.2016 / 21:19
0

@WickedOne, When you call the ToList<T>() method of a IQueryable<T> , you enumerate the data and speak to 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 transformed into a SQL script at the very moment of execution. Therefore, the variable catequizandosCheked is of type List 'and is stored in memory.

When you try to mix Lambda expression with local data, LINQ / Provider can not translate its LINQ to a SQL script and therefore gives an error (I imagine that is your case).

To not "fill a lot of sausage" you only 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 ToList<T>() , variable catequizandosCheked becomes type IQueryable<PresencaEventViewModel> and can still be translated into a script SQL and be used in the other Query Expression .

I hope I have helped.

    
16.05.2016 / 23:52