SubQuery in select result

Question

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!

Answer 1

@Murilo Junqueira , 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: