CODEWITHSUNDEEP
c#entity-framework-core
Basin
Basin

Reputation: 1017

Return null for non-nullable columns when no records found

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?

Upvotes: 4

Views: 1427

Answers (3)

Giovanni Esposito
Giovanni Esposito

Reputation: 11156

Ciao, according to MSDN:

Sometimes the value of default(TSource) is not the default value that you want to use if the collection contains no elements. Instead of checking the result for the unwanted default value and then changing it if necessary, you can use the DefaultIfEmpty<TSource>(IEnumerable<TSource>, TSource)

So in your case:

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

Upvotes: 0

Jamie Burns
Jamie Burns

Reputation: 1318

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();

Upvotes: 6

Simon Price
Simon Price

Reputation: 3261

I had a similar issue recently and this is how I got around it.

 /// <summary>
 /// Gets or sets Telephone 2 Type.
 /// </summary>
 public int? TelephoneTwoTypeId { get; set; }

 /// <summary>
 /// Gets or sets Telephone 2 Type data structure.
 /// </summary>
 public TelephoneTypes? TelephoneTwoType { get; set; }

When doing the migration it should look something like this too

 protected override void Up(MigrationBuilder migrationBuilder)
 {
     migrationBuilder.AlterColumn<int>(
         name: "TelephoneTwoTypeId",
         schema: "dbo",
         table: "tableName",
         nullable: true,
         oldClrType: typeof(int),
         oldType: "int");
 }

 protected override void Down(MigrationBuilder migrationBuilder)
 {
     migrationBuilder.AlterColumn<int>(
         name: "TelephoneTwoTypeId",
         schema: "dbo",
         table: "tableName",
         type: "int",
         nullable: false,
         oldClrType: typeof(int),
         oldNullable: true);
 }

so, if you you make the items nullable this should work for you. Please also excuse the int as an Id in code first. I wasnt the original table designer

Upvotes: 0

Related Questions