How to deal with Hashtable property of entity in database using EntityFramework

Question

I have old project which used ADO.NET to access the persistent store. Currently, I want to migrate it to EF (6.1.3, if it matters), in order to support several DB providers with minimal code duplicate.

There is an entity, which contains Hashtable property:

public class Record
{
    ...
    public Hashtable data { get; set; }
}

With ADO.NET, the BinaryFormatter was used to convert this data property to the BLOB, and vice versa:

using (MemoryStream stream = new MemoryStream())
{
    BinaryFormatter formatter = new BinaryFormatter();
    formatter.Serialize(stream, data);
    result = stream.GetBuffer();
}

//----------

using (MemoryStream serializationStream = new MemoryStream((byte[])value))
{
    BinaryFormatter formatter = new BinaryFormatter();
    result = (Hashtable)formatter.Deserialize(serializationStream);
}

Now I need to tell EF how it should store and retrieve that property.

What have I tried

I could store one more property in the entity:

public class Record
{
    public byte[] dataRaw { get; set; }

    [NotMapped]
    public Hashtable data {
        get {/*deserialize dataRaw */ }
        set { /*Serialize to dataRaw*/}
    }
}

But this solution is prone to errors, and special workflow with that property must be followed.

P.S. Actually this question is not about the Hashtable only, but about every custom class which must be stored and retrived in a special way.

I honestly don't think this is possible. I don't think there are any hooks that would allow the value to be coerced into and out of a sql primitive. I am eagerly watching this question to see if there are any answers that come close. — Jim
– Jim, Commented Sep 15, 2016 at 7:09
there is a similar question stackoverflow.com/questions/16135642/…, it uses a backing field like yours. I don't think there is another way. — Jim
– Jim, Commented Sep 15, 2016 at 7:22

Community · Accepted Answer · 2017-05-23 10:34:12Z

5

Here is a complete solution based on the answer I mentioned above.
I have tested it in linqpad, and it works rather well.

You don't need a special workflow, as the property accessors take care of saving and loading the hash table when needed.

Main Method

void Main()
{
    using (var ctx = new TestContext())
    {
        var hash = new Hashtable();
        hash.Add("A", "A");
        ctx.Settings.Add(new Settings { Hash = hash });
        ctx.SaveChanges();

        // load them up...
        ctx.Settings.ToArray().Select(_ => _.Hash).Dump();
    }
}

Settings Class

public class Settings
{
    // a primary key is necessary.
    public int Id { get; set; }

    [NotMapped]
    public Hashtable Hash
    {
        get;
        set;
    }

    // the backing field can be protected, this helps 'hide' it.
    protected virtual byte[] _Hash
    {
        get
        {
            return Hash.ToBinary();
        }
        set     
        {
            Hash = value.FromBinary<Hashtable>();
        }
    }
}

Extensions to Convert the Values

public static class Extensions
{

    public static BinaryPropertyConfiguration BinaryProperty<T>(
        this EntityTypeConfiguration<T> mapper,
        String propertyName) where T : class
    {
        Type type = typeof(T);
        ParameterExpression arg = Expression.Parameter(type, "x");
        Expression expr = arg;

        PropertyInfo pi = type.GetProperty(propertyName,
            BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
        expr = Expression.Property(expr, pi);

        LambdaExpression lambda = Expression.Lambda(expr, arg);

        Expression<Func<T, byte[]>> expression = (Expression<Func<T, byte[]>>)lambda;
        return mapper.Property(expression);
    }

    public static byte[] ToBinary<T>(this T instance)
    {
        if (instance == null)
            return null;

        using (var stream = new MemoryStream())
        {
            var formatter = new BinaryFormatter();
            formatter.Serialize(stream, instance);
            return stream.ToArray();
        }
    }

    public static T FromBinary<T>(this byte[] buffer)
    {
        if (buffer == null)
            return default(T);

        using (var stream = new MemoryStream(buffer, false))
        {
            var formatter = new BinaryFormatter();
            var instance = formatter.Deserialize(stream);
            return (T)instance;
        }
    }
}

Data Context

public class TestContext : DbContext
{
    public DbSet<Settings> Settings { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder
            .Entity<Settings>()
            .BinaryProperty("_Hash")
            .HasColumnName("Hashtable");
    }       
}

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Sep 15, 2016 at 8:43

Jim

16.2k17 gold badges89 silver badges174 bronze badges

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

6 Comments

Jim Over a year ago

@hadi yea, it works, I have tried it, saved it to a varbinary(max) - which is what's generated, and loaded it back up - no problem.

stukselbax Over a year ago

@Jim, this is more elegant solution.

Sampath Over a year ago

@stukselbax if this is the solution then you can accept it no ?

Jim Over a year ago

@sampath - thanks for that, but perhaps the answer is lacking?

stukselbax Over a year ago

@Sampath I would like to wait for a while before accepting. It solves the problem, but I want to wait others to come to party.

|

Collectives™ on Stack Overflow

How to deal with Hashtable property of entity in database using EntityFramework

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related