C#•4y ago

EF Core SQL Generation with Custom Type [Answered]

Stroniax · 2022-09-15T17:47:22.217Z

I've got a custom type `AssetId`, which is an AK member of the type `Asset`. ``` class Asset { int Id; // PK AssetId? AssetId; // AK } struct AssetId { private int _value; static AssetId Parse(string s); string ToString(); } class AssetIdConverter : ValueConverter { AssetIdConverter(): base(e => e.ToString(), e => AssetId.Parse(e), new ConverterMappingHints(size: 8, unicode: false) {} } // inside DbContext.ConfigureConventions builder.DefaultTypeMapping ().HasConversion ().IsUnicode(false).HasMaxLength(8).IsFixedLength(); builder.Property ().HasConversion ().IsUnicode(false).HasMaxLength(8).IsFixedLength(); ``` The `AssetId` type is internally an `int`, but for a number of reasons including the fact that it logically contains two values, it is stored as `char(8)` in the db. No matter how I configure this type, it seems querying against it in EF is always generating SQL like the following: `CONVERT(Asset.AssetId as NVARCHAR(max)) = N'00-00-00'` (where 00-00-00 is an `AssetId.ToString()`). I do not want to convert the value to `NVARCHAR(max)` for this. It shouldn't have to change from `char(8)` at all.

I've got a custom type

AssetId

AssetId

, which is an AK member of the type

Asset

Asset

class Asset {
  int Id; // PK
  AssetId? AssetId; // AK
}
struct AssetId {
  private int _value;
  static AssetId Parse(string s);
  string ToString();
}
class AssetIdConverter : ValueConverter<AssetId, string> {
  AssetIdConverter(): base(e => e.ToString(), e => AssetId.Parse(e), new ConverterMappingHints(size: 8, unicode: false) {}
}

// inside DbContext.ConfigureConventions
builder.DefaultTypeMapping<AssetId>().HasConversion<AssetIdConverter>().IsUnicode(false).HasMaxLength(8).IsFixedLength();
builder.Property<AssetId>().HasConversion<AssetIdConverter>().IsUnicode(false).HasMaxLength(8).IsFixedLength();

class Asset {
  int Id; // PK
  AssetId? AssetId; // AK
}
struct AssetId {
  private int _value;
  static AssetId Parse(string s);
  string ToString();
}
class AssetIdConverter : ValueConverter<AssetId, string> {
  AssetIdConverter(): base(e => e.ToString(), e => AssetId.Parse(e), new ConverterMappingHints(size: 8, unicode: false) {}
}

// inside DbContext.ConfigureConventions
builder.DefaultTypeMapping<AssetId>().HasConversion<AssetIdConverter>().IsUnicode(false).HasMaxLength(8).IsFixedLength();
builder.Property<AssetId>().HasConversion<AssetIdConverter>().IsUnicode(false).HasMaxLength(8).IsFixedLength();

The

AssetId

AssetId

type is internally an

int

int

, but for a number of reasons including the fact that it logically contains two values, it is stored as

char(8)

char(8)

in the db.

No matter how I configure this type, it seems querying against it in EF is always generating SQL like the following:

CONVERT(Asset.AssetId as NVARCHAR(max)) = N'00-00-00'

CONVERT(Asset.AssetId as NVARCHAR(max)) = N'00-00-00'

(where 00-00-00 is an

AssetId.ToString()

AssetId.ToString()

). I do not want to convert the value to

NVARCHAR(max)

NVARCHAR(max)

for this. It shouldn't have to change from

char(8)

char(8)

at all.

Yawnder•9/15/22, 6:01 PM

@Stroniax That happens when you do something like right?

StroniaxOP•9/15/22, 6:02 PM

AssetId also implements the equality interfaces and operators.

StroniaxOP•9/15/22, 6:02 PM

And has an implicit string conversion.

SStroniax AssetId also implements the equality interfaces and operators.

Yawnder•9/15/22, 6:02 PM

That part is irrelevant since we're talking about SQL

StroniaxOP•9/15/22, 6:03 PM

Yes, wasn't sure if EF would map to sql with some specific logic because of that however.

Yawnder•9/15/22, 6:03 PM

The "problem" is that when EF sends it to SQL, it doesn't know that the

string

string

that gets converted into is guaranteed to be 8 char long.

StroniaxOP•9/15/22, 6:03 PM

Hmm, I might not actually be parsing the asset id that I put in that predicate, the implicit conversion might be getting me. I have fairly advanced predicate logic that I may have lost track of.

Yawnder•9/15/22, 6:04 PM

So it would compare

char(8)

char(8)

(your actual column) with (what a

string

string

has to be assumed to be)

YYawnder The "problem" is that when EF sends it to SQL, it doesn't know that the `string`...

StroniaxOP•9/15/22, 6:05 PM

Because I am (I think) passing another instance of

AssetId

AssetId

I would expect the SQL generator to know it is also

char(8)

char(8)

Yawnder•9/15/22, 6:05 PM

For that comparison, the

char(8)

char(8)

has to be converted, otherwise the result would be true if the first 8 chars of would match.

SStroniax Because I am (I think) passing another instance of `AssetId` I would expect the ...

Yawnder•9/15/22, 6:05 PM

I understand. The idea was good.

StroniaxOP•9/15/22, 6:05 PM

I am pretty sure I am using the implicit string conversion though...

StroniaxOP•9/15/22, 6:05 PM

Now that I look at that part of my code.

Yawnder•9/15/22, 6:06 PM

Oh, you think it happens outside the custom converter? You could verify with breakpoints.

StroniaxOP•9/15/22, 6:13 PM

Indeed it was my implicit conversion that got me. Thanks for helping me work through that one.

AccordAPP•9/15/22, 6:13 PM

This post has been marked as answered!