Define default value for SingleOrDefault Linq method

Question

I try to get a data record ID from Entity Framework:

var plantAreaID = await db.PlantAreas
  .Where(p => p.Plant.CompanyNo == companyNo && p.Plant.Abbreviation == companyAbbr)
  .Select(p => p.ID)
  .SingleOrDefaultAsync();

The problem is, that there is a PlantArea with the ID 0. SingleOrDefault returns 0, though, if it doesn't find any record. So how can I differenciate between ID 0 and the case, where no data was found?

My first idea was to provide a default value -1, but SingleOrDefault doesn't seem to support that in this constellation.

How can I do that?

Answer 1

null is the normal way to indicate missing values.

var plantAreaID = await db.PlantAreas
   .Where(p => p.Plant.CompanyNo == companyNo && p.Plant.Abbreviation == companyAbbr)
   .Select(p => new int?(p.ID))
   .SingleOrDefaultAsync();

If you want to use -1 to indicate a missing value, you can say

var plantAreaID = await db.PlantAreas
   .Where(p => p.Plant.CompanyNo == companyNo && p.Plant.Abbreviation == companyAbbr)
   .Select(p => new int?(p.ID))
   .SingleOrDefaultAsync() ?? -1;

Answer 2

I found another solution, that doesn't require casting:

int plantAreaID = await db.PlantAreas
  .Where(p => p.Plant.CompanyNo == companyNo && p.Plant.Abbreviation == companyAbbr)
  .Select(p => p.ID)
  .DefaultIfEmpty(-1)
  .SingleAsync();