Query ODataV4 connected service with LINQ - Get last record from table

Question

Im trying to query my OData webservice from a C# application.

When i do the following:

var SecurityDefs = from SD in nav.ICESecurityDefinition.Take(1) 
                   orderby SD.Entry_No descending 
                   select SD;

i get an exception because .top() and .orderby is not supposed to be used together.

I need to get the last record in the dataset and only the last.

The purpose is to get the last used entry number in a ledger and then continue creating new entries incrementing the found entry no.

I cant seem to find anything online that explains how to do this.

Its very important that the service only returns the last record from the feed since speed is paramount in this solution.

Answer 1

i get an exception because .top() and .orderby is not supposed to be used together.

Where did you read that? In general .top() or .Take() should ONLY be used in conjunction WITH .orderby(), otherwise the record being retrieved is not guaranteed to be repeatable or predictable.

Probably the compounding issue here is mixing query and fluent expression syntax, which is valid, but you have to understand the order of precedence.
Your syntax is taking 1 record, then applying a sort order... you might find it easier to start with a query like this:

// build your query
var SecurityDefsQuery = from SD in nav.ICESecurityDefinition 
                        orderby SD.Entry_No descending 
                        select SD;
// Take the first item from the list, if it exists, will be a single record.
var SecurityDefs = SecurityDefsQuery.FirstOrDefault();
// Take an array of only the first record if it exists
var SecurityDefsDeferred = SecurityDefsQuery.Take(1); 

This can be executed on a single line using brackets, but you can see how the query is the same in both cases, SecurityDefs in this case is a single ICESecurityDefinition typed record, where as SecurityDefsDeferred is an IQueryable<ICESecurityDefinition> that only has a single record.

If you only need the record itself, you this one liner:

var SecurityDefs = (from SD in nav.ICESecurityDefinition 
                    orderby SD.Entry_No descending 
                    select SD).FirstOrDefault();

You can execute the same query using fluent notation as well:

var SecurityDefs = nav.ICESecurityDefinition.OrderByDescending(sd => sd.Entry_No)
                                            .FirstOrDefault();

In both cases, .Take(1) or .top() is being implemented through .FirstOrDefault(). You have indicated that speed is important, so use .First() or .FirstOrDefault() instead of .Single() or .SingleOrDefault() because the single variants will actually request .Take(2) and will throw an exception if it returns 1 or no results.

The OrDefault variants on both of these queries will not impact the performance of the query itself and should have negligble affect on your code, use the one that is appriate for your logic that uses the returned record and if you need to handle the case when there is no existing record.

If the record being returned has many columns, and you are only interested in the Entry_No column value, then perhaps you should simply query for that specific value itself:

Query expression:

var lastEntryNo = (from SD in nav.ICESecurityDefinition 
                   orderby SD.Entry_No descending 
                   select SD.Entry_No).FirstOrDefault();

Fluent expression:

var lastEntryNo = nav.ICESecurityDefinition.OrderByDescending(sd => sd.Entry_No)
                                           .Select(sd => sd.Entry_No)
                                           .FirstOrDefault();

---

If Speed is paramount then look at providing a specific custom endpoint on the service to either serve the record or do not process the 'Entry_No` in the client at all, make that the job of the code that receives data from the client and compute it at the time the entries are inserted.

Making the query perform faster is not the silver bullet you might be looking for though, Even if this is highly optimised, your current pattern means that X number of clients could all call the service to get the current value of Entry_No, meaning all of them would start incrementing from the same value.

If you MUST increment the Entry_No from the client then you should look at putting a custom endpoint on the service to simply return the Next Entry_No to use. This should be optimistic meaning that you don't care if the Entry_No actually gets used in the end, but you can implement the end point such that every call will increment the field in the database and return the next value.

Its getting a bit beyond the scope of your initial post, but SQL Server now has support for Sequences that formalise this type of logic from a database and schema point of view, using Sequence simplifies how we can manage these types of incrementations from the client, because we no longer rely on the outcome of data updates to be comitted to the table before the client can increment the next record. (which is what your TOP, Order By Desc solution is trying to do.