CODEWITHSUNDEEP
c#linqdatetimeasp.net-mvc-5
Grizzly
Grizzly

Reputation: 5953

Retrieve Record Based on TimeSpan with LINQ

I do not know if this will work or possible, but let me try and give a clear explanation.

Lets say I have a database table called SummaryTests that includes the following records:

ID    |          Day           |    Summary    |
------------------------------------------------
 1       05/27/2016 09:01:00         Test1
 2       05/27/2016 09:04:00         Test2
 3       05/27/2016 09:08:00         Test3

Now, if I want to add a record to this table at 05/27/2016 09:06:00, then the table would look like this:

ID    |          Day           |    Summary    |
------------------------------------------------
 1       05/27/2016 09:01:00         Test1
 2       05/27/2016 09:04:00         Test2
 3       05/27/2016 09:08:00         Test3
 4       05/27/2016 09:06:00         Test4  // added record

From my perspective, based on Day, that record should be between record 2 & record 3, but in SQL the order is based off of ID. So if I want to retrieve the record, based on Day, that comes before the record I just added (should be record 2) this is how I have been doing it:

[HttpPost]
public ActionResult Create([Bind(Include = "ID,Day,Summary")] SummaryTest summaryTest)
{
    int previousRecord = db.SummaryTests.Where(x.Day < summaryTest.Day).OrderByDescending(x => x.Day).FirstOrDefault().ID;
    SummaryTest prevSumTest = db.SummaryTests.Find(previousRecord);
}

But my question is, is there a way to just retrieve the record with the closest timespan that is before the record that the user is creating? I feel as though that would be better than having to sort the database then order it by descending order, then get the first element of that sequence.

Any help is appreciated.

Upvotes: 0

Views: 183

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205679

The way you are doing it is basically the only general database SQL way. If your table has an index on the Day column, there is absolutely no problem to satisfy efficiently your query.

The only thing that can be improved is to eliminate the second database query. Instead of retrieving the ID and then the record using that ID, you can simply use

var prevSumTest = db.SummaryTests
    .Where(x.Day < summaryTest.Day)
    .OrderByDescending(x => x.Day)
    .FirstOrDefault();

Upvotes: 2

Lews Therin
Lews Therin

Reputation: 3777

Don't worry about the order of records in the database, that's the server's job. If you want your data returned in a specific order, that is precisely what the ORDER BY clause is for. SQL will organize the records how it sees fit to optimize performance. You need to tell it what data you want, and how you want it in your query.

Upvotes: 1

Related Questions