I am working on .NET 5 CORE app with Entity Framework Core 5.0.6 Version.

Ultimately, I need to group my record. In EF CORE 3, I was able to use group by on script before applying ToList() but I believe is not possible in EF Core 5. So to achieve group by, I am intended to pull record and then apply group by on it.. Unless there is a better way?

var contraventionImages = (from contravention in db.Contraventions
                  join contraventionGuideImage in db.ContraventionGuideImages on contravention.ContraventionId equals contraventionGuideImage.ContraventionId into con_guide_img
                  from contraventionGuideImages in con_guide_img.DefaultIfEmpty()
                  join image in db.GuideImages on contraventionGuideImages.GuideImageId equals image.GuideImageId into guide_img
                  from guideImages in guide_img.DefaultIfEmpty()
                  where contravention.ContraventionId == ContraventionId
                  select new ContraventionGuideImageView
                  {
                     Contravention = contravention,
                     ContraventionGuideImages = contraventionGuideImages,
                     GuideImages = guideImages
                  })
                  .FirstOrDefault()
                  ;

So I have pulled the record, ending script with FirstOrDefault(), now in second script where I am trying to inspect and group Item; is Unable to do so. I am not able to apply select on query result I have in previous step

 public class ContraventionGuideImageView
{
    public Contravention Contravention { get; set; }
    public ContraventionGuideImage ContraventionGuideImages { get; set; }
    public GuideImage GuideImages { get; set; }
}

CodePudding user response：

OK, the edit has made it much clearer to see. It looks like your confusion is that after selecting the first record you're trying to do further queries on that record which:

1. Won't work
2. Seems pointless.

By doing the call to FirstOrDefault, you're getting an object (in your case ContraventionGuideImageView); you need to do any grouping/ordering on the queryable before you do the first or default, for example:

var contraventionImages = (from contravention in db.Contraventions
                  join contraventionGuideImage in db.ContraventionGuideImages on contravention.ContraventionId equals contraventionGuideImage.ContraventionId into con_guide_img
                  from contraventionGuideImages in con_guide_img.DefaultIfEmpty()
                  join image in db.GuideImages on contraventionGuideImages.GuideImageId equals image.GuideImageId into guide_img
                  from guideImages in guide_img.DefaultIfEmpty()
                  where contravention.ContraventionId == ContraventionId
                  select new ContraventionGuideImageView
                  {
                     Contravention = contravention,
                     ContraventionGuideImages = contraventionGuideImages,
                     GuideImages = guideImages
                  })
                  .ToList() //shouldn't make a difference, but is for the benefit of Toxic seeing the process.
                  .GroupBy(x => x.contravention)
                  .FirstOrDefault();

HOWEVER, the query is so complex that I'd argue it'd be better as a stored procedure.

CodePudding user response：

found the answer;

Option A:

   var t7 = (from contravention in db.Contraventions
                   .Include(x => x.ContraventionGuideImages)
                   .ThenInclude(x => x.GuideImage)
                  where contravention.ContraventionId == ContraventionId
                  select new
                  {
                      contravention = contravention,
                      GuideImages = contravention.ContraventionGuideImages.Select(x => x.GuideImage)
                  }).FirstOrDefault();

Option B:

 var contraventionImages = (from contravention in db.Contraventions
                  join contraventionGuideImage in db.ContraventionGuideImages on contravention.ContraventionId equals contraventionGuideImage.ContraventionId into con_guide_img
                  from contraventionGuideImages in con_guide_img.DefaultIfEmpty()
                  join image in db.GuideImages on contraventionGuideImages.GuideImageId equals image.GuideImageId into guide_img
                  from guideImages in guide_img.DefaultIfEmpty()
                  where contravention.ContraventionId == ContraventionId
                  select new { contravention, contraventionGuideImages, guideImages }).ToList();


var guideImagesList = (from contravention in contraventionImages.Select(x => x.contravention)
                  group contraventionImages.Select(x => x.guideImages ) by contravention into groupedItem
                  select new
                  {
                      contravention = groupedItem.Key,
                      contraventionGuideImages = groupedItem.ToList()

                  }).FirstOrDefault();