Return null for non-nullable columns when no records found

Question

Entity types

public class Person
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public Guid TeamId { get; set; } // mandatory foreign key

    public virtual Team Team { get; set; } // navigation property

    // dozen other properties
}

public class Team
{
    public Guid Id { get; set; }

    public string Name { get; set; }
}

I have a method which returns team id for a given person id. Method's return type defined as Guid?. Nullable because there are possibility that given person id does not exist in database.

public Task<Guid?> GetTeamIdFor(Guid personId)
{
    using (var context = _createDbContext())
    {
        return await context.Persons
            .Where(p => p.Id == personId)
            .Select(p => p.TeamId)
            .FirstOrDefault();
    }
}

Issue is that FirstOrDefault() return default value of Guid type (type of TeamId column). So instead of returning null method return empty Guid 00000000-0000-0000-0000-000000000000.

I can achieve desired result by loading whole entity, but I try to avoid loading extra, not required data.

var person = await context.FindAsync(personId);
return person?.TeamId;

I can do check for empty Guid before returning value

var teamId = await context.Where(p => p.Id = personId).Select(p => p.TeamId).FirstOrDefaultAsync();
return teamId == Guid.Empy ? default(Guid?) : teamId;

Question: Is there a way to make query return nullable Guid without loading whole entity or without operations after result materialization?

Answer 1

You can cast TeamId to a nullable Guid, which allows FirstOrDefault to return null if nothing is found:

Guid? id = context.Persons
  .Where(p => p.Id == personId)
  .Select(p => (Guid?)p.TeamId)
  .FirstOrDefault();