LINQ Select Last & Unique Record from a DB using List

Question

I have a table where all vehicles are registered and another table where I have millions of pings for each registered vehicle.

I'm trying to select the last ping from each vehicle that has sent a ping in the last 30 minutes using the LINQ QUERY. I've done the code below through the "for each" idea, but I'm not sure if it is the best way to do.

I would like to know if there is any better way to select this using a single line? I know that I can "group by" them by vehicle_fleetNumber but I couldn't achieve the proper result as the TAKE() is limiting the final result.

var timeRestriction = DateTime.UtcNow.AddMinutes(-30);
var x = _db.Vehicles.Where(r=> r.isActive.Equals(true) && r.helperLastPing > timeRestriction);

foreach (var vehicle in x)
{
    var firstOrDefault = _db.Tracks.OrderByDescending(r => r.collectedOn)
        .FirstOrDefault(r => r.vehicle_fleetNumber.Equals(vehicle.fleetNumber));
}

return View();

Thank you,

score 2 · Answer 1 · answered Aug 23 '16 at 08:53

2

Yes, you should do it in the database by joining both tables and using GroupBy:

var query = from v in _db.Vehicles
            join t in _db.Tracks
            on v.fleetNumber equals t.vehicle_fleetNumber
            where v.isActive && v.helperLastPing > timeRestriction
            group t by t.vehicle_fleetNumber into vehicleGroup
            select vehicleGroup.OrderByDescending(x => x.collectedOn).First();

foreach(var track in query)
{
    // ...
}

Instead of the foreach you can also use query.ToArray or ToList, i don't know what you want to do with it.

answered Aug 23 '16 at 08:53

Tim Schmelter

450,073
74
686
939

Hi @Tim Schmelter. Thank you for your help. If you see the image https://postimg.org/image/pc3agbm75/, the LINQ selects the whole database and then applies the filter. It is taking around 7 seconds and it is because I have only 63884 rows there at the moment. Using the for each it is taking 1 second to select the whole data. Is there anyway to select only the last ping for each? – Canela Aug 23 '16 at 23:16

score 2 · Answer 2 · edited May 23 '17 at 10:34

2

If you get moreLinq from nuget you will find the .maxby() method: for example in a different context:

//get the correct exchange rate
var rateList = _db.lists_ExchangeRates.Where(
           rates => rates.Currency == currencyCode);
           Decimal? exRate = rateList.MaxBy(rates => rates.LastUpdated).ExchangeRate;

Also see below this gives additional info.

MoreLinq maxBy vs LINQ max + where

edited May 23 '17 at 10:34

Community

1
1

answered Aug 23 '16 at 08:56

Graham Bedford

71
1
3

I've tried it but I got the exception System.OutOfMemoryException as it is probably reading the whole table that has more than 65k rows. See the code below `var query = _db.Tracks.Where(r => r.Vehicle.isActive && r.Vehicle.helperLastPing > timeRestriction); var maxBy = query.MaxBy(track => track.vehicle_fleetNumber);` Thank you for your help – Canela Aug 23 '16 at 23:47
You could limit your results by introducing a view at the SQL server end and querying that. It should make working with the data much quicker and easier. – Graham Bedford Aug 24 '16 at 08:21

score 0 · Answer 3 · answered Aug 23 '16 at 09:15

0

In my case if I want the last data that has been save I use this method

var id = db.DPSlips.Max(item => item.Id);

So I thought this might work as will just try

var timeRestriction = DateTime.UtcNow.AddMinutes(-30); var x = _db.Vehicles.Max(a => a.isActive == true && a.helperLastPing > timeRestriction);

answered Aug 23 '16 at 09:15

Thanks mate It will only select the vehicles information and vehicle and ping have a different table. The `helperLastPing` column has the last ping information inside the vehicle table as it helps me to do other checks without selecting the `Tracks` table, but all the pings information are inside the Track table. Let's say I have the vehicle 1, 2 and 3 inside the Vehicle Table. The `Track` table may have 1 million pings or more for every vehicle. What I'm trying to do is select each last vehicle's ping without selecting everything as the track table will be huge. – Canela Aug 23 '16 at 23:56

LINQ Select Last & Unique Record from a DB using List

3 Answers3