Getting max value on server (Entity Framework)-CodePudding

I'm using EF Core but I'm not really an expert with it, especially when it comes to details like querying tables in a performant manner...

So what I try to do is simply get the max-value of one column from a table with filtered data.

What I have so far is this:

protected override void ReadExistingDBEntry()
{
    using Model.ResultContext db = new();

    // Filter Tabledata to the Rows relevant to us. the whole Table may contain 0 rows or millions of them
    IQueryable<Measurement> dbMeasuringsExisting = db.Measurements
                                                     .Where(meas => meas.MeasuringInstanceGuid == Globals.MeasProgInstance.Guid 
                                                                    && meas.MachineId == DBMatchingItem.Id);


    if (dbMeasuringsExisting.Any())
    {
        // the max value we're interested in. Still dbMeasuringsExisting could contain millions of rows
        iMaxMessID = dbMeasuringsExisting.Max(meas => meas.MessID);
    }
}

The equivalent SQL to what I want would be something like this.

select max(MessID) 
from Measurement 
where MeasuringInstanceGuid = Globals.MeasProgInstance.Guid 
  and MachineId = DBMatchingItem.Id;

While the above code works (it returns the correct value), I think it has a performance issue when the database table is getting larger, because the max filtering is done at the client-side after all rows are transferred, or am I wrong here?

How to do it better? I want the database server to filter my data. Of course I don't want any SQL script ;-)

CodePudding user response：

This can be addressed by typing the return as nullable so that you do not get a returned error and then applying a default value for the int. Alternatively, you can just assign it to a nullable int. Note, the assumption here of an integer return type of the ID. The same principal would apply to a Guid as well.

int MaxMessID = dbMeasuringsExisting.Max(p => (int?)p.MessID) ?? 0;

There is no need for the Any() statement as that causes an additional trip to the database which is not desirable in this case.