LINQ: Get unique rows by selected ID's based on latest time

Question

I have managed to connect to my database using the current syntax below and running simple LINQ query's. I have a database containing around 20 unique SHIP_ID's that gets updated information regarding positions for all ships every 2 minutes. That means SHIP_ID and TIMESTAMP combined together are unique, and that my database has a lot of data in it so i want the load to be on the SQL server to return the correct data. And not the client to go through a response of 20k lines to find the value i want.

I want to retrieve the last position stored within the database for a group of select ships, lets says the ships with SHIP_ID's 1, 2 and 3. As of now the SHIP_ID's are stored in a string[].

            using (var scope = _scopeFactory.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<AisRecordContext>();
                var query = dbContext.SimpleAisRecords
                    //LINQ statements goes here to get intended result
                    .Where ?
                    .GroupBy ?
                    .Select? 
                    .OrderderByDecending ?

            }

How could I go forward making the correct LINQ statements to get the data i want? I also want the result to have the whole row it selects as data, for example LAT, LON, SPEED and HEADING that are some of my other columns.

I also wonder what is the best format to return the result. The result will be the row's that returns, the row where TIMESTAMP is newest for the SHIP_ID's i want to call. I will need all the columns that my database has (about 15 columns) since i will display the data in a map.
Would I use ToList? ToArray? or somehow just have my query be the result without modifying it after it gets added.

Thanks for your time (and sorry for a very specific question, could not find any articles getting multiply rows by selected ID's for their latest DATE)

See the answer for this post https://stackoverflow.com/questions/5344805/linq-orderby-descending-query for returning a list ordered by data — auburg, Feb 17 '20 at 17:21
Can you not add your timestamp column to OrderderByDecending clause? — auburg, Feb 17 '20 at 17:59

score 1 · Answer 1 · edited Feb 17 '20 at 21:19

1

I assume your SHIP_ID is of type int? If so:

var ships = new string[]{ "1","2","3"};
using (var scope = _scopeFactory.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<AisRecordContext>();
    var result = dbContext.SimpleAisRecords
        .Where(r => ships.Any(i => int.Parse(i) == r.SHIP_ID)) //Looks for any records matching any item in ships
        .OrderByDescending(r => r.TIMESTAMP); // Orders by time stamp descending...
}

After you could select the one that is the latest one (which will be the first one since we ordered by time stamp) like so: var latestShip = result.FirstOrDefault();

As far as what the type of collection to return, it depends on what you need to do after... having the original record will give you flexibility as you can updated, delete if required.

edited Feb 17 '20 at 21:19

Dharman

30,962
25
85
135

answered Feb 17 '20 at 18:03

Yosef Bernal

1,006
9
20

They are strings because the serviceprovider that give me ship positions have them stored that way. Will that be a problem you think? Thank you, i will update you tomorrow morning how it works out :) Been stuck for 7 hours so i went home haha. – KnutJohan Feb 17 '20 at 18:25
Do i need some sort of Method like .First() or something to add only the latest item to the result? – KnutJohan Feb 17 '20 at 18:34
The `int.Parse()` method will throw an exception if it is unable to parse the string, so just make sure your service provides consistent data. Secondly, yes, to grab the first item in a collection you do use `First()` or `FirstOrDefault()`. To learn the difference between the two please see [this post](https://stackoverflow.com/questions/1024559/when-to-use-first-and-when-to-use-firstordefault-with-linq). I strongly recommend you look at [LINQ](https://learn.microsoft.com/en-us/dotnet/standard/using-linq) and Lambda expressions in Microsoft's documentation. – Yosef Bernal Feb 18 '20 at 10:06

score 1 · Accepted Answer · answered Feb 17 '20 at 18:04

What makes this complicated is that you want to retrieve rows with the most recent timestamps for multiple ships. I think the only way to do this will be to make a request for each SHIP_ID, and populate a list with each result.

var lastLocations = new List<SimpleAisRecords>();
var shipIds = new string[] { "1", "2", "3" };

using (var scope = _scopeFactory.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<AisRecordContext>();
    foreach (var shipId in shipIds)
    {
        // Gets records for a SHIP_ID, orders by newest to oldest, and then grabs the first (newest) record
        var query = dbContext.SimpleAisRecords
                             .Where(ship => ship.SHIP_ID == shipId)
                             .OrderderByDecending(ship => ship.TIMESTAMP)
                             .FirstOrDefault();

        if (query != null)
        {
            lastLocations.Add(query);
        }
    }
}

By default it will return all columns from the associated table, so you'll have the LAT, LON, SPEED, HEADING, and all the other columns you need.

This looks pretty clean and does what i want! Thanks alot.. I dont know why it loks so simple when i see it now. I will update you tomorrow after i have tried it! Thanks alot — KnutJohan, Feb 17 '20 at 18:28

LINQ: Get unique rows by selected ID's based on latest time

2 Answers2