EF Core Compress Decompress Column

Question

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.

You could try using a [conversion](https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions) But then you have to compress/decompress using c# code instead of SQL server methods. — Preben Huybrechts, Jul 23 '20 at 07:34
I considered that option, similarly, I could simply compress the value before storing it, however as SQL Server has an option to compress and decompress I was wondering if there would be a way to configure EF core. — Raul, Jul 23 '20 at 07:39
to whoever voted for a close, what exactly is not clear within this question? — Raul, Jul 23 '20 at 07:40
You could call `SELECT COMPRESS(@Data)` in your conversions, but I wouldn't suggest doing this. Because it will be a performance nightmare. Other options would be to have a view to query the decompressed data a and a storedprocedure to insert/update the compressed data. — Preben Huybrechts, Jul 23 '20 at 07:45
Take a look at this. It may help https://stackoverflow.com/a/31727711/5817136 — Iman, Jul 23 '20 at 07:50

Preben Huybrechts · Answer 1 · 2020-07-28T07:21:56.527

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.

EF Core Compress Decompress Column

1 Answers1