EntityFramework database first - type mapping - map binary(8) from SQL to int in C#

Question

Inside SQL I have table that have primary key as binary(8). When I add that table to my model using Update Model from Database I can see that this column has type=Binary

and in C# I get that column as byte[].

Can I map that column to int?

I know I can create a view with CAST in SQL:

SELECT
    Client_Id,
    CAST(Client_Id AS INT) AS NewClient_Id,
    * /*other columns*/
FROM
    dbo.Clients

but this isn't a solution, because I must be able to write, not just read from that table. I know I can create stored procedure for inserts but I'd like to avoid that.

I'm usinf EntityFramewor 6.1.3.

Answer 1

You have 3 different solutions

x Stored procedures but you don't want them.

x Add a not mapped property to your class. The biggest problem about this solution is that you can't make queries using the not mapped property. You have to read all the data to the client then apply the condition on the non mapped property on the client (so your app is not scalable).

[NotMapped]
public long LongClientId
{
    get { return BitConverter.ToInt64(this.ClientId, 0); }
    set { this.ClientId = BitConverter.GetBytes(value); }
}

This query won't work

context.MyDbSet.Where(m => m.LongClientId == 12).ToList();

You need to change it in this way

context.MyDbSet.ToList().Where(m => m.LongClientId == 12);

The result of this query is that you load all table's records (transfer from dbms to your app) into a list than you take the one you need.

x Create a view (probably an indexed view) and use an INSTEAD OF trigger.