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.

Are you also going to try to fit a quart into a pint pot? binary(8) contains 8 bytes. an int in C# contains 4 bytes. Do you see the problem here? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 29, 2016 at 7:36
@Damien_The_Unbeliever sorry if I wrote something incorrect, but I'm trying to get that working. I thought that if I can do CAST in SQL then maybe EF can do that cast for me. I can convert that byte[] to int by myself in code, but I thought that EF has this build in, I just don't know how to turn that on or configure it. — Misiu
– Misiu, Commented Jul 29, 2016 at 7:40
You can write to a view. However you will need to provide triggers against your view in order for the value to be converted back. Also binary 8 is a long IIRC — Mark
– Mark, Commented Jul 29, 2016 at 7:41
@Mark I've tried inserting int value into that binary(8) and SQL is converting that value for me, if I insert 2 as ClientId in that table I'll have 0x0000000000000002. I'd like to avoid view and procedures because I want to use same approach (convert binary(8) to int) in existing tables without creating extra 20 views. — Misiu
– Misiu, Commented Jul 29, 2016 at 7:55
Two properties is possible solution? Convert value when mapping — Leon Pro
– Leon Pro, Commented Aug 8, 2016 at 8:43

bubi · Accepted Answer · 2016-08-11 09:47:42Z

2

+25

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.

answered Aug 11, 2016 at 9:47

bubi

6,5013 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Misiu Over a year ago

I've created issue in EF repository on GitHub, because what I'd like to get isn't currently possible in EF6 and EF Core. I'll probably choose third solution, because I don't want to load whole tables into memory (ToList does that). Stored procedures will do the same, so view looks like best solution for now. I'll leave this open, maybe there is a better solution.

gxclarke · Accepted Answer · 2016-08-08 19:24:25Z

1

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.

answered Aug 8, 2016 at 19:24

gxclarke

1,9733 gold badges21 silver badges42 bronze badges

2 Comments

bubi Over a year ago

It's the only way but you can't write LINQ to entity queries using ClientId field (i.e. context.MyTable.Where(e => e.ClientId == 1) ). You have to read all the table than apply the condition (i.e. context.MyTable.ToList().Where(e => e.ClientId == 1) )

Misiu Over a year ago

Thank You for Your suggestion, but I'd like to avoid loading whole table into memory

Richard Petheram · Accepted Answer · 2016-08-08 16:21:27Z

0

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.

answered Aug 8, 2016 at 16:21

Richard Petheram

84513 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related