Support saving GUID values to STRING columns

[RonFrick]

Need a way to specify saving guid as text column. I would think adding a way to set BinaryGUID to false in the connection string would solve the issue.

[natemcmaster]

@RonFrick this is already possible by calling ToString() when saving the guid. Did you have something else in mind?

Example:

                var guid = Guid.NewGuid();
                var save = conn.CreateCommand();
                save.CommandText = "INSERT INTO tbl VALUES (@p1)";
                save.Parameters.AddWithValue("@p1", guid.ToString());
                save.ExecuteNonQuery();

Full example: https://gist.github.com/natemcmaster/db14848ed53ae520e8a2b8a6c4410d52

[RonFrick]

Hey thanks Nate! I am using EF core. Is there anyway to do the same thing using DbContext?

If not then I will certainly look at re writing my inserts manually and not use EF.

Thanks again for the help.

[RonFrick]

Would there be something I can do in this class to always force it to save guids as text.
Microsoft.Data.Sqlite.SqliteCommand

I already have my own version of this dll to solve other problems so I am fine for a one off fix on this issue as well. Any help is appreciated. Thanks Nate.

[natemcmaster]

Is there anyway to do the same thing using DbContext?

You would need to change your properties CLR type to System.String instead of System.Guid. EF wraps this layer with its own type mapping, but doesn't have any influence on how the CLR types are persisted by Microsoft.Data.Sqlite.

Curious, why do you need to save as TEXT instead of the default ( which BLOB)?

Would there be something I can do in this class to always force it to save guids as text.

There are several different ways it could be done if you want the change the default behavior in your fork. One place to look is binding in SqliteParameter.cs.

[RonFrick]

Ok...I will take a look and evaluate. Thank you!

The reason for needing to specify the behavior is because I am using a flavor of sync framework for sqlite here..

The sync is saving GUIDs as text. EF is saving it as binary, but EF is also saving it in a format that cannot be queried again. After I save the row if you try to query that same row, EF throws this... Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). I can attach an image of what it looks like with SQLite browser.

I was hoping it would be as simple as using my fork to change the connection string like so.

Example:
Data Source=c:\mydb.db;Version=3;BinaryGUID=False;

...but of course it's a no go.

[RonFrick]

Sure would be nice to just do this.

    public SqliteContext(string path)
        : base(new SQLiteConnection
        {
            ConnectionString = new SQLiteConnectionStringBuilder
            {
                DataSource = path,
                ForeignKeys = true,
                BinaryGUID = false,
            }.ConnectionString
        }, true)
    {
        _dbPath = path;
        Database.Log = Console.Write;
    }

https://gist.githubusercontent.com/flaub/1968486e1b3f2b9fddaf/raw/628f8521ed87c294849a2e5455772742ca1070ae/SqliteContext.cs

[bricelam]

Yes, when we implement this, we'll enable setting BinaryGUID=False in the connection string.

[bricelam]

Note to implementor: EF Core generates Guid literals in the SQL. It will have to be updated to take this setting into account.

[bricelam]

Marking for re-triage. Given the complexities involved regarding my previous comment; I'm not sure we should ever do this.

[bricelam]

To state the obvious: the workaround is to pass the the values to the provider as string (not Guid) objects.

[bricelam]

Our philosophy so far has been to be highly compatible on reads, but highly predictable on writes. We support many formats when reading DateTime, DateTimeOffset, Guid, and TimeSpan values, but we'll always write using an invariant, well-known, canonical format.

Adding this feature would change that.

Imagine a higher-level framework (like EF Core) that generates SQL. It may generate statements like the following.

SELECT $someGuidParam = x'SOME_BLOB_VALUE';
-- or
SELECT * FROM MyTable
WHERE SomeGuidColumn = x'SOME_BLOB_VALUE';

As it stands now, these will always work. However, if we allow changing the format of Guid values based on an application-defined parameter, the SQL would need to react by inserting TEXT literals instead of BLOB literals if this flag is set in the connection string.

Give the complexities this would introduce into frameworks that leverage Microsoft.Data.Sqlite, we've decided that we would not like to add this feature.