SubQuery in select result

Question

question

MuriloJunqueira-9599 asked May 28, '22 JackJJun-MSFT answered May 30, '22

SubQuery in select result

Hello everyone, I have a list that has a portal id and color, and another lists with the records I need, in this list of records I need to get the color of the portal, I'm trying to get this data in the result with a subquery, but a mistake.

         var pts = await _portaisRepositorio.ListarTudo();

         var regs = await _contexto.licitacoes.Where(l => l.datapublicacao >= System.DateTime.Now.Date.AddDays(-2))
             .GroupBy(l => new { l.nomeportal, l.idportal })
             .Select(l => new LicsTotalModel
             {
                 idportal = l.Key.idportal,
                 nomeportal = l.Key.nomeportal,
                 total = l.Count(),
                 cor = pts.Where(g => g.id == l.Key.idportal).Select(p => p.cor).FirstOrDefault(), // <= ERROR HERE

             }).ToListAsync();

         return regs;

I also tried:

                 cor = (from s in pts where s.id == l.Key.idportal select s.cor).FirstOrDefault(),

How can I do this? I just want to get the "color" field with the same id as the portal

Thanks!

dotnet-entity-framework-core

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-30T08:57:43.303Z

JackJJun-MSFT answered May 30, '22

@MuriloJunqueira-9599, Welcome to Microsoft Q&A, based on my test, I find that your code is almost correct. What error you get from the above code?
I used the following code to make a test:

 internal class Program
     {
         static void Main(string[] args)
         {
    
             List<Port> list1 = new List<Port>();
             list1.Add(new Port() { Id=1001, color=Color.Red});
             list1.Add(new Port() { Id = 1002, color = Color.Black });
             list1.Add(new Port() { Id = 1003, color = Color.DarkRed });
    
             List<LicsTotalModel> list2 = new List<LicsTotalModel>();
             list2.Add(new LicsTotalModel() { idportal=1002, datapublicacao= System.DateTime.Now.Date.AddDays(-2) });
    
             var result = list2.Where(l => l.datapublicacao >= System.DateTime.Now.Date.AddDays(-2))
                          .GroupBy(l => new { l.nomeportal, l.idportal })
                           .Select(l => new LicsTotalModel
                           {
                               idportal = l.Key.idportal,
                               nomeportal = l.Key.nomeportal,
                               color = list1.Where(g => g.Id == l.Key.idportal).Select(p => p.color).FirstOrDefault(), // <= ERROR HERE
                           }).ToList();
    
    
    
         }
     }
     public class Port
     {
         public int Id { get; set; } 
    
         public Color color { get; set; }   
     }
    
     public class LicsTotalModel
     {
         public int idportal { get; set; }
    
         public int nomeportal { get; set; }
    
         public DateTime datapublicacao { get; set; }
    
         public Color color { get; set; }
    
    
     }

I can get the result as the following:

image.png (20.1 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question