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, Jul 29 '16 at 07: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, Jul 29 '16 at 07: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, Jul 29 '16 at 07: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, Jul 29 '16 at 07:55
Two properties is possible solution? [Convert value when mapping](http://stackoverflow.com/questions/19370104/convert-value-when-mapping) — Leon Pro, Aug 08 '16 at 08:43
@LeonPro I'd like to avoid that. BTW isn't this for Code-First? — Misiu, Aug 08 '16 at 08:46
Seems like there is no built-in solution: ["We plan to implement type conversions in the EF7 codebase. We are just not committed yet to doing it for the initial RTM and we are currently not actively working on it"](https://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/2639292-simple-type-mapping-or-mapped-type-conversion-supp?page=1&per_page=20) P.S. If you use pure "DB First" is not appropriate. We always describe the Context with tweaks and workarounds manually on the finished database. — Leon Pro, Aug 08 '16 at 09:17

score 2 · Answer 1 · answered Aug 11 '16 at 09:47

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.

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. — Misiu, Aug 11 '16 at 10:08

score 1 · Answer 2 · answered Aug 08 '16 at 19:24

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 08 '16 at 19:24

gxclarke

1,953
3
21
42

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) ) – bubi Aug 09 '16 at 07:12
Thank You for Your suggestion, but I'd like to avoid loading whole table into memory – Misiu Aug 11 '16 at 10:09

score 0 · Answer 3 · answered Aug 08 '16 at 14:00

0

I do know why you do not stick to either int or byte in both database structure and c# code of page

answered Aug 08 '16 at 14:00

Walid

29
2

score 0 · Answer 4 · answered Aug 08 '16 at 16:21

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.

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

4 Answers4

Linked