LINQ max date with join and group

Question

I can't figure it out and tested some answers from similare questions. Now i need a linq pro. :)

The following query with linq returns each device.storeID and device.deviceID with max date in DeviceList:

var query = (from device in db.DeviceList
        join store in db.stores on device.storeID equals store.id
        join type in db.devices on device.deviceID equals type.id
        group device by new { device.storeID, device.deviceID } into g
        select new
        {
            deviceID = g.Key.deviceID,
            storeID = g.Key.storeID,
            MaxDate = g.Max(d => d.Date)
        });

In device there is also device.amount, but i can't acess like:

var query = (from device in db.DeviceList
            join store in db.stores on device.storeID equals store.id
            join type in db.devices on device.deviceID equals type.id
            group device by new { device.storeID, device.deviceID } into g
            select new
            {
                amount = device.amount,
                deviceID = g.Key.deviceID,
                storeID = g.Key.storeID,
                MaxDate = g.Max(d => d.Date)
            });

Because it's not in group. But if i add in group:

group device by new { device.storeID, device.deviceID, device.amount } into g
select new
{
    amount = g.Key.amount,
    deviceID = g.Key.deviceID,
    storeID = g.Key.storeID,
    MaxDate = g.Max(d => d.Date)
});

I get more results back than i need. What seems logical to me, but can i get amount without group it? I don't need device.storeID and device.deviceID with max date for each amount in DeviceList.

Oh, and by the way: As you can see, i tried to join store and type to get the store/device name. How can i add:

select new
{
    deviceName = type.name,
    deviceID = g.Key.deviceID,
    storeID = g.Key.storeID,
    MaxDate = g.Max(d => d.Date)
});

???

Thank you for every useful hint!

@Rafal: Thank you! Perhaps there is a easier solution for my query. So i have to explain what i want to read from the database.

The table device contains:

device.id    device.deviceID    device.storeID    device.amount    device.Date
123               52                     20            10          2021-11-11
124               57                     20             5          2021-12-01
125               57                     20             2          2021-12-02
126               52                     20             8          2021-12-03
127               52                     21             3          2021-12-03

So, i need every different deviceID for every different storeID and amount from the last (max/highest) date:

   device.id    device.deviceID    device.storeID    device.amount    device.Date
    125               57                     20             2       2021-12-02
    126               52                     20             8       2021-12-03
    127               52                     21             3       2021-12-03

Answer 1

If you stop for a second and write same query in sql you will come the same realization that amount cannot be selected from those groups just because you have multiple values within your store&device group. As pointed by @Juharr you can make an aggregation over amount as most likely it makes sens that you want to know sum of those amounts rather than one random of them from the group? or maybe you know which one you need? the one with max date?

if the one with max date you are after than you need to join device after the group by and select it:

var query = (from device in db.DeviceList
        join store in db.stores on device.storeID equals store.id
       group device by new { device.storeID, device.deviceID } into g
        select new
        {
           
            deviceID = g.Key.deviceID,
            storeID = g.Key.storeID,
            MaxDate = g.Max(d => d.Date)
        }) into s
        let dev = db.devices.FirstOrDefault(x => 
         x.deviceID == s.deviceID 
         && x.storeID == s.storeID 
         && s.MaxDate == x.Date)
        join type in db.devices on dev.deviceID equals type.id
        select new {
            s.deviceID,
            s.storeID,
            s.MaxDate,
            dev.amount,
            type.name
         };

this is also not perfect as you can have multiple device records with the same date that happens to be max so it will choose one at (semi) random or you can add some order before that FirstOrDefault

Short answer is you did not define properly what you want to read from the database in regards of that amount or type.

---

Also as a pro tip do not use join syntax until you have to. Writing queries is this manner by default reduces EF capabilities to generate queries for you, increases your workload as you need to think about relations and foreign keys and not on what the requirements are. You should use Navigation Properites by default and when EF fails to create proper code you can fall back to join syntax and fix the query. More often than not EF query is good enough.