Dynamic Linq Query between two contexts

Asked

Viewed 1,953 times

1

I’m trying to return the query sql sequinte but without success.

SQL=
select IDEstado, COUNT(IDEstado) from PessoaEstado w 
join Nucleo.dbo.Estado e on e.ID = w.IDEstado
where IDPessoaMaster = 46
group by IDEstado

Code=
var dbApp = new App();

var dbNucleo = new Nucleo();
var estados = (from e in dbNucleo.Estado
                select new { e.ID, e.Nome }).ToArray();

var result = (from w in db.PessoaEstado
              join e in estados on w.IDEstado equals e.ID
              where w.IDPessoaMaster == IDPessoaMaster
              group new { w, e } by new
              {
                  e.Nome
              } into z
              select new
              {
                nome = z.Key.Nome;
                qtd = z.Count()
              }).OrderByDescending(x => x.qtd);

return result.ToList<dynamic>();

returns the error:

Could not create a constant value of type 'Anonymous Type'. Only primitive types or enumeration types are supported in this context.

1 answer

0


Do Linq to Objects

You’re working in contexts (DbContext) and the best solution would be to bring in object and then relate to Linq To Objects, another point to note is to bring the data and not put in the Trancking (Cache) of DbContext(AsNoTracking)

Like

IList<Estado> Estados = dbNucleo.Estado.AsNoTracking().ToList();
IList<PessoaEstado> PessoasEstados = db.PessoaEstado.AsNoTracking().ToList();

Linq

var resultado = PessoaEstado
                .Where(x=>x.IDPessoaMaster == 46)
                .Join(Estado, p => p.IDEstado, e => e.ID, (p, e) => new { p, e })
                .GroupBy(x => x.p.IDEstado)                
                .Select(s => new
                {
                    IDEstado = s.Key,
                    CountEstado = s.Count()
                }).ToList();

Running Example - Fictitious Data

IList<PessoaEstado> PessoaEstado = new List<PessoaEstado>();
PessoaEstado.Add(new PessoaEstado() { IDEstado = 1, IDPessoaMaster = 46 });
PessoaEstado.Add(new PessoaEstado() { IDEstado = 2, IDPessoaMaster = 46 });
PessoaEstado.Add(new PessoaEstado() { IDEstado = 1, IDPessoaMaster = 46 });

IList<Estado> Estado = new List<Estado>();
Estado.Add(new Estado() { ID = 1 });
Estado.Add(new Estado() { ID = 2 });


var resultado = PessoaEstado
   .Where(x=>x.IDPessoaMaster == 46)
   .Join(Estado, p => p.IDEstado, e => e.ID, (p, e) => new { p, e })
   .GroupBy(x => x.p.IDEstado)                
   .Select(s => new
   {
        IDEstado = s.Key,
        CountEstado = s.Count()
   }).ToList();

inserir a descrição da imagem aqui

  • I think he’ll want the answer more within his code.

  • 1

    @Ciganomorrisonmendez, the one that picks up the code proposed by him the most that in SQL he’s grouping by Idestado and in Linq she’s grouping by Name, I thought, to make a minimal example so that he had the vision that different contexts need to work with Linq to Objects, maybe (supposition) him putting ToArray() along those lines var result = (from w in db.PessoaEstado.ToArray() ... and continue the same way I get a solution but, it falls on what I showed myself. Well he hasn’t done interaction yet, I’m on hold or any issue in your question, thanks for the tip ...

Browser other questions tagged

You are not signed in. Login or sign up in order to post.