C# ef core efficiently determine if there is only one row in a query-CodePudding

What is the most efficient way to check if a particular query has one row AND only one row? .FirstOrDefault() will not serve my purpose because I am interested in knowing if there are two or just one result.

is it

via count async

var record = await _FilteredContext.Context.Where(x => x.Criterion == FilterCondition).CountAsync();
var singular = record == 1;

var singular = false;
try
{
 var record = await _FilteredContext.Context.SingleOrDefault(x => x.Criterion == 
 FilterCondition);
 singular = true;
}
catch (Exception ex) {}

or something else entirely?

CodePudding user response：

You can use the same technique as any ORMs do - take two records and apply Count.

var count = await _FilteredContext.Context
        .Where(x => x.Criterion == FilterCondition)
        .Take(2)
        .CountAsync();
var singular = count == 1;

CodePudding user response：

You have three options, and it depends on if you want to know exactly what number of results you get, and if you want exceptions to direct the flow of logic.

First is to use Single (or SingleOrDefault). This will throw an exception if you get more than 1 result (SingleOrDefault will not throw with 0 results).

YourModelType resultingObject;
try
{
    resultingObject = await _FilteredContext.Context
        .Single(x => x.Criterion == FilterCondition);
}
catch
{
    // (assuming another error wasn't thrown)
    // do something if more than one result
}

Under the hood, at least for Sql Server, this translates to (roughly)

select top 2 *
from YourTable
where Criterion == <param>

The reason why this SQL is generated is to prevent fetching and searching extra rows. 2 is the minimum number to check for the row count to determine if you got "more than one" result. There's no point in passing back all rows to the client if passing back a maximum of 2 rows is sufficient.

The second option is to make this yourself. This will only work if you only care about if there are 0, 1, or "more than 1" results.

var queryCount = await _FilteredContext.Context
    .Where(x => x.Criterion == FilterCondition)
    .Take(2)
    .CountAsync();

You can swap out CountAsync with ToListAsync if you want the objects. Then you just need some logic to determine how many results you have, it's either going to be 0, 1, or 2.

Third option is to keep what you have. This is used if you want to know exactly how many records you get, even if it's "more than 1".