How to write a linq query for below Scenerio

Question

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; }
    }

Does this answer your question? [Group By Multiple Columns](https://stackoverflow.com/questions/847066/group-by-multiple-columns) — Yong Shun, Sep 12 '21 at 04:52
Side note: `COUNT(CartonDetail.CartonId)` will count non-null values of `CartonId`. If it is a non-nullable column, that is the same as `COUNT(*)` or `COUNT(1)` — Charlieface, Sep 12 '21 at 04:56
Why do you operate under [two accounts](https://stackoverflow.com/q/69147825/861716)? — Gert Arnold, Sep 12 '21 at 08:27
You don't need the grouping if you remove the join from your LINQ query and turn it into a navigation property. Please read how to do that [here](https://learn.microsoft.com/en-us/ef/core/modeling/relationships). It will simplify everything you do in EF. — Gert Arnold, Sep 12 '21 at 08:38

score 1 · Answer 1 · edited Sep 12 '21 at 09:08

1

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);

edited Sep 12 '21 at 09:08

Gert Arnold

105,341
31
202
291

answered Sep 12 '21 at 05:10

sa-es-ir

3,722
2
13
31

Include does nothing here because of `Select ... new`. So it doesn't "get matched CartonDetails". – Gert Arnold Sep 12 '21 at 08:34
@GertArnold Yes you are right. ``Include`` mostly converted to T-Sql using ``Left join`` in case of one-to-many relations (As OP asks for all ``Cartons`` with or without ``CartonDetails``) and also, I think ``Include`` is more readable. – sa-es-ir Sep 12 '21 at 08:42
What I mean is: EF ignores the Include because of the projection. Even if it would include it in the query, it would be an outer join and have no filtering effect. Test your code before posting, even when it seems simple. Also, the count isn't correct. – Gert Arnold Sep 12 '21 at 08:45
@GertArnold Oh yes when use ``Count(CartonDetail.CartonId)`` it just count the not nulls records. answer updated. – sa-es-ir Sep 12 '21 at 08:49
@GertArnold Thank you for sharing knowledge as the StackOwerflow is for. – sa-es-ir Sep 12 '21 at 08:59

Charlieface · Answer 2 · 2021-09-12T08:58:59.383

0

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.

edited Sep 12 '21 at 08:58

answered Sep 12 '21 at 04:54

Charlieface

52,284
6
19
43

You're right, didn't realize it was starting with the other table and not joining – Charlieface Sep 12 '21 at 08:59

How to write a linq query for below Scenerio

2 Answers2