SqlQuery cannot mapping C# byte to PostgreSQL smallint

[sasukevita]

i have "PG"."PG_Table" with column "TypeId" AS smallint NOT NULL, in C# i mapped it as byte

public partial class PG_Table { public int Id { get; set; } public byte TypeId { get; set; } }

when execute:
var firstlog1 = db.PG_Table.FirstOrDefault();// Works
var firstlog2 = db.Database.SqlQuery<PG_Table>("SELECT \"Id\",\"TypeId\" FROM \"PG\".\"PG_Table\" LIMIT 1").FirstOrDefault(); //Error
Error Exception:

System.InvalidOperationException: The 'TypeId' property on 'PG_Table' could not be set to a 'System.Int16' value. You must set this property to a non-null value of type 'System.Byte'.

using dbset firstlog1 is works but SqlQuery is failed to mapping smallint type

if i change the mapping in C# TypeId to short
public partial class PG_Table { public int Id { get; set; } public short TypeId { get; set; } }

when execute:
var firstlog2 = db.Database.SqlQuery<PG_Table>("SELECT \"Id\",\"TypeId\" FROM \"PG\".\"PG_Table\" LIMIT 1").FirstOrDefault(); //Works

how can i make SqlQuery to support mapping smallint to byte?

[sasukevita]

btw i test this using ef6, im not using core yet, hope will be fixed for ef6 too

[YohDeadfall]

@roji, will this be fixed or not?

[roji]

There's very little chance we'll be fixing this in EF6 - I'm not even sure the type mapping system would allow this.

However, it should be very easy to just use short instead of byte. If you want to expose the property as a byte in your application, add another unmapped property which does the byte<->short conversion.

We can leave this open in case someone wants to give it a try.

[sasukevita]

This is the first time i was migrating our project from sql server to postgre.

In sql server there is tinyint as byte in C#.
In postgre there is no tinyint, its only smallint.

While in npgsql supporting smallint to byte in c#, but SqlQuery function is not support.

I feel inconsistency, npgsql support mapping C# byte to smallint, only SqlQuery not.

[roji]

I feel inconsistency, npgsql support mapping C# byte to smallint, only SqlQuery not.

You're right that this is inconsistent, but there's a good reason for that. There are actually two different layers here: the low-level ADO.NET driver, which you use to send SQL to PostgreSQL, and the EF6 provider which sits on top of it. The ADO.NET does indeed support mapping .NET byte to smallint, which is why you can save and load entities. But at the EF6 level there's no such support, and it isn't certain whether EF6 is even flexible enough to allow it (unlike EF Core where it should definitely be possible).

EF6 simply wasn't designed with cross-database support in mind, so PostgreSQL support is sometimes limited or lacking. Having said that I haven't actually looked into this, so it may be possible - but I have no time to do so.