C#•2y ago

EF Core SQL Generation with Custom Type [Answered]

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<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 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.

13 Replies

Yawnder•2y ago

@Stroniax That happens when you do something like dbContext.Assets.Where(e => e.AssetId == anotherAssetId.ToString()) right?

Stroniax•2y ago

assetId = AssetId.Parse(userInput); context.Assets.Where(e => e.AssetId == assetId) AssetId also implements the equality interfaces and operators. And has an implicit string conversion.

Yawnder•2y ago

That part is irrelevant since we're talking about SQL

Stroniax•2y ago

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

Yawnder•2y ago

The "problem" is that when EF sends it to SQL, it doesn't know that the string that assetId gets converted into is guaranteed to be 8 char long.

Stroniax•2y ago

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•2y ago

So it would compare char(8) (your actual column) with nvarchar(max) (what a string has to be assumed to be)

Stroniax•2y ago

Because I am (I think) passing another instance of AssetId I would expect the SQL generator to know it is also char(8)

Yawnder•2y ago

For that comparison, the char(8) has to be converted, otherwise the result would be true if the first 8 chars of nvarchar(max) would match. I understand. The idea was good.

Stroniax•2y ago

I am pretty sure I am using the implicit string conversion though... Now that I look at that part of my code.

Yawnder•2y ago

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

Stroniax•2y ago

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

Accord•2y ago

✅ This post has been marked as answered!

Gaming

Programming

EF Core SQL Generation with Custom Type [Answered]