How to write a linq query for below Scenerio [closed]-CodePudding

I am trying to write the convert the SQL query to Linq but I am not sure if there is a way to write this query. Also, This query grab only matching value from CartonDetails table but i need all the value from Carton table.

select      Carton.Id,
            Carton.CartonNumber,
            COUNT(CartonDetail.CartonId) as TotalCount
from        Carton
Inner Join  CartonDetail on CartonDetail.CartonId = Carton.Id
Group by    Carton.Id, Carton.CartonNumber

That is what I have so far.I am new to the Linq. Thanks in advance

            var apv = db.Cartons.Where(c => c.CartonDetails.Select(cd => cd.CartonId).Contains(c.Id)).GroupBy(c => c.Id, c => c.CartonNumber).Select(c => new CartonViewModel
            {
                Id = c.Key,
                EquipmentCount = c.Count(),
                // How can i select the Carton Number here.
            });

            return View(cartons);
        }

CartonDetail.cs

[Table("CartonDetail")]
    public partial class CartonDetail
    {
        public int Id { get; set; }

        public int CartonId { get; set; }

        public int EquipmentId { get; set; }

        public Carton Carton { get; set; }

        public Equipment Equipment { get; set; }
    }

CodePudding user response：

Based on @GertArnold comment the final linq query should be like this:

var apv = db.Cartons
.Select(c => new CartonViewModel
{
    Id = c.Id,
    CartonNumber = c.CartonNumber,
    EquipmentCount = c.CartonDetails.Count()
});

 return View(cartons);

CodePudding user response：

If you use an anonymous type of those properties to group, it will be the grouping key. You can then get the values out of it:

var apv = db.Cartons
    .Join(db.CartonDetails,
        c => c.Id,
        cd => cd.CartonId,
        (c, cd) => new {c, cd})
    .GroupBy(temp => new {temp.c.Id, temp.c.CartonNumber})
    .Select(c => new CartonViewModel
    {
        Id = c.Key.Id,
        CartonNumber = c.Key.CartonNumber,
        EquipmentCount = c.Count(),
    });

This should get an almost exact translation of your original query.