Entity Framework Core Coalesce null value

Question

I have a database that I am unable to change, where the individual in that database has a gender which is (most of the time) 'M' / 'F'. There are some instances where the gender is null.

On the database (Oracle) this is defined as Gender CHAR(1 BYTE) column in the database.

The following query, allows me to do what I'd expect in SQL and wrap this column in a coalesce function

var result = from x in _context.Individuals where x.Id == 502429 select new { Sex = x.Sex ?? "U" };

Produces this SQL... (where I'm using "u" for "unknown")

SELECT COALESCE("x"."GENDER", N'U') "Gender" FROM "INDIVIDUAL" "x" WHERE "x"."Id" = 12345 FETCH FIRST 1 ROWS ONLY

But, the following doesn't allow me to specify a coalesce option...

var someone = await _context
    .Individuals
    .AsNoTracking()
    .Include(x=>x...)
    .Include(x=>x...)
    .Where(x => x.Id == id)
    .FirstOrDefaultAsync();

Instead this produces this SQL...

SELECT "Individual"."SEX", "..other columns.."
FROM "Individual" "x"
WHERE "x"."Id" = :id_0
FETCH FIRST 1 ROWS ONLY

I have tried specifying a type attribute [Column("sex", TypeName = "nvarchar(1)")], setting a default value on the modelbuilder modelBuilder.Entity().Property(x=>x.Sex).HasDefaultValue, column type on the modelBuilder HasColumnType,

How do I generate the SQL that I'd expect in this instance so that the database performs the work and I get back something I can rely on in my code?

My workaround is to have a string rather than a char type, but that opens a whole other bag of worms, I'd like to be able to use Coalesce with the above..

Entity Framework Core Coalesce null value

0 Answers