Concurrency Tokens on SQLite in EF Core

[bricelam]

https://github.com/aspnet/EntityFrameworkCore/issues/12260#issuecomment-395509124

[ggirard07]

That's what I am trying to achieve. But in order to do so, I need to inject parameters in the SQL command, which always end-up with SQLite Error 1: 'near "@p0": syntax error'.

I tried to parameterized the command as follow:

db.Database.ExecuteSqlCommand($@"
    CREATE TRIGGER PatchRowVersionInsert{entity.TableName}
    AFTER INSERT ON {entity.TableName}
    BEGIN
        UPDATE {entity.TableName}
        SET {entity.RowVersionPropertyName} = randomblob(8)
        WHERE {string.Join(" AND ", entity.PrimaryKeyNames.Select(n => $"{n} = NEW.{n}"))};
    END
");

I tried to hardcode some of the variable in case parameters were not supported in the trigger name for example, but exception always happens on @p0. EnsureCreated() has already been called at that time and some seed has already happened in some tables.

Only solution I have found is to expand the string first, then execute the command. But this creates some nasty warnings related to SQL injection (EF1000).

I am using an in-memory SQLite database with ASP.Net Core 2.1 configured with implicit version.

[bricelam]

You’re hitting a quirk of ExecuteSqlComnand. Cast the string to a string:

db.Database.ExecuteSqlCommand((string)$@“...”);

[ggirard07]

Casting to string ends up in the same workaround I am currently using (expanding the string first then pass it to ExecuteSqlCommand), which result in the SQL injection warning.

Is there any proper fix for this, except than disabling the warning?

[bricelam]

We fixed it in EF Core 3.0