Entity Framework Core ExecuteSqlCommand delete with SQLite does not work

Question

I have the following model:

public class LogData
{
    public Guid ID { get; set; }
    public string Name { get; set; }
}

I use Entity Framework Core to save those models to an SQLite database, it works well.

I need to delete from the data (it is dynamic, I can't use objects), so I use the following command:

string command="DELETE FROM LogData WHERE ID IN ('ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7')";
context.Database.ExecuteSQLCommand(command);

According to the SQLite syntax, it is valid.

Unfortunately, as a result I get back 0, so no row was affected. When I remove the WHERE condition, it deletes the contents of the table.

I have a guessing that as the key column is a Guid and it is stored as a BLOB, the plain SQLite engine can't find it.

So I tried to alter the command to this:

string command="DELETE FROM LogData WHERE HEX(ID) IN ('ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7')";
context.Database.ExecuteSqlCommand(command);

Also tried this:

string command="DELETE FROM AuditLog WHERE HEX(ID) = 'ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7'";
context.Database.ExecuteSqlCommand(command);

This, too:

string command="DELETE FROM AuditLog WHERE ID = 'ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7'";
context.Database.ExecuteSqlCommand(command);

None of those helped.

What should I do about this?

@DavidG I get an exception if I use that: `SQLite Error 1: 'unrecognized token: "X'ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7'"` — Nestor, Apr 15 '19 at 16:39
OK, this might seen odd, but does this delete your row: `WHERE ID = X'2ab753eab24a884f8f1d0f96baa7cac7'`? — DavidG, Apr 15 '19 at 16:55
First of all, why is the ID stored as a BLOB? can you switch it to a UNIQUEIDENTIFIER instead? Also what does the data look like after being saved? You might be running to this issue described here https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/ — nimbleDonut, Apr 15 '19 at 16:36
@Mihai As the script works without the `WHERE` condition: yes, it is persisted to the database. — Nestor, Apr 15 '19 at 16:57
There seems to be a similar question [here](https://stackoverflow.com/questions/14740827/how-to-delete-a-row-with-a-guid-value-in-sqlite) but not any useful answer.. they say it works with `like` clause — Mihai, Apr 15 '19 at 17:01
@DavidG It gets deleted if I use that completely different ID... Why does THAT work? — Nestor, Apr 15 '19 at 17:02

score 2 · Accepted Answer · edited Apr 16 '19 at 06:33

The GUIDs are stored in the database as a binary BLOB meaning you need to pass in a binary value to compare against. To do this you use the X'...' notation. Additionally, you need to convert the endianness of the GUID to little endian. Fortunately, there's a handy extension method here to do your conversion:

public static Guid FlipEndian(this Guid guid)
{
    var newBytes = new byte[16];
    var oldBytes = guid.ToByteArray();

    for (var i = 8; i < 16; i++)
        newBytes[i] = oldBytes[i];

    newBytes[3] = oldBytes[0];
    newBytes[2] = oldBytes[1];
    newBytes[1] = oldBytes[2];
    newBytes[0] = oldBytes[3];
    newBytes[5] = oldBytes[4];
    newBytes[4] = oldBytes[5];
    newBytes[6] = oldBytes[7];
    newBytes[7] = oldBytes[6];

    return new Guid(newBytes);
}

And you use it like this:

//The source GUID
var source = Guid.Parse("ea53b72a-4ab2-4f88-8f1d-0f96baa7cac7");
//Flip the endianness
var flippedGuid = source.FlipEndian();

//Create the SQL
var command = $"DELETE FROM AuditLog WHERE ID = X'{flippedGuid.ToString().Replace("-", "")}'";

context.Database.ExecuteSqlCommand(command);

@Thanks, it really works :) I had no idea and could never find it out. I accept it as the answer. — Nestor, Apr 15 '19 at 17:25

Entity Framework Core ExecuteSqlCommand delete with SQLite does not work

1 Answers1