EF Core Compress Decompress Column

user_3205951 0
Viewed 1870

As an example take a simple poco with an ID and a string property called data.

What would be the best way to call COMPRESS(data) before saving the entity using EF Core and calling DECOMPRESS(data) on loading it. The database column of course would be VARBINARY.

Detailed:

Writing custom SQL to achieve this (based on the above example) would look like this:

SELECT Id, DECOMPRESS(@Data) as [Data]
FROM table

for selecting and like this for inserting an entity:

INSERT INTO table
VALUES(@Id, COMPRESS(@Data))

Notes:

It's SQL Server.

The current string length can go up to a few thousand, as such a fixed-length NVARCHAR using PAGE or ROW compression on the DB side are not an option.

entity-framework-core sql-server c#
1 Answers

Using a conversion seems the best way. Since the sooner you compress the data, the less network I/O your going to generate. Also compressing will burn precious SQL server CPU cycles and may impact the performance of the SQL server.

public class YourEntityTypeConfigruation : IEntityTypeConfiguration<YourEntity>
{
    public void Configure(EntityTypeBuilder<YourEntity> builder)
    {
        builder.Property(e => e.Data)
            .HasConversion(
                v => Zip(v),
                v => Unzip(v));
    }
    
    public static void CopyTo(Stream src, Stream dest) {
        byte[] bytes = new byte[4096];

        int cnt;

        while ((cnt = src.Read(bytes, 0, bytes.Length)) != 0) {
            dest.Write(bytes, 0, cnt);
        }
    }

    public static byte[] Zip(string str) {
        var bytes = Encoding.UTF8.GetBytes(str);

        using (var msi = new MemoryStream(bytes))
        using (var mso = new MemoryStream()) {
            using (var gs = new GZipStream(mso, CompressionMode.Compress)) {
                //msi.CopyTo(gs);
                CopyTo(msi, gs);
            }

            return mso.ToArray();
        }
    }

    public static string Unzip(byte[] bytes) {
        using (var msi = new MemoryStream(bytes))
        using (var mso = new MemoryStream()) {
            using (var gs = new GZipStream(msi, CompressionMode.Decompress)) {
                //gs.CopyTo(mso);
                CopyTo(gs, mso);
            }

            return Encoding.UTF8.GetString(mso.ToArray());
        }
    }
}

Using the SQL server methods is possible when you create a view like

CREATE VIEW MyView
AS
SELECT Id, DECOMPRESS(Data) as [Data]
FROM table

And mapping it in EF Context

For insert/updates you would need stored procedures to compress the data. Beating the purpose of using entity framework to track changes to entities.

user_6398290
Related