CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Misiu
Misiu

Reputation: 4919

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

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

enter image description here

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.

Upvotes: 5

Views: 2249

Answers (3)

bubi
bubi

Reputation: 6491

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.

Upvotes: 2

gxclarke
gxclarke

Reputation: 1973

You can handle the conversion internally within your model, as follows:

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

    [Column("ClientId")]
    public byte[] ClientIdBytes { get; set; }

The caller works with ClientId as a long, but this property is not mapped to the database. The getter and setter simply convert the value to a second property, which is persisted in the ClientId database column name.

Upvotes: 1

Richard Petheram
Richard Petheram

Reputation: 845

In my experience the mapping process is quite easy to confuse, especially when updating an existing map. For this reason I'd recommend you use 

long CurrentClientId = BitConverter.ToInt64(Rec.ClientId)

on read and 

Rec.ClientId = BitConverter.GetBytes(CurrentClientId)

on write. You may already have a wrapper to massage records as they are read into an internal structure, this would just be one more step.

Note this doesn't pay any attention to a byte order you may think you have in the byte array, but at least the process will round-trip correctly.

Upvotes: 0

Related Questions