Silly behaviour with SQLite and EF6

Question

I can't beleave I'm the first person running into that problem, but didn't found any similar discussions in the net.

Here is the simple full code sample:

using SQLite.CodeFirst;
using System;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;

namespace ConsoleApp1
{
  class Program
  {
    static void Main(string[] args)
    {
      Entity entity = new Entity();
      Guid id = entity.Id;
      using (var context = new MyDbContext())
      {
        context.Entities.Add(entity);
        context.SaveChanges();

        // this finds an entry
        var item = context.Entities.Find(id);
      }

      using (var context = new MyDbContext())
      {
        // here it returns null
        var item = context.Entities.Find(id);
      }
    }
  }

  public class MyDbContext : DbContext
  {
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      Database.SetInitializer(new SqliteDropCreateDatabaseWhenModelChanges<MyDbContext>(modelBuilder));
    }

    public MyDbContext() : base("MyConnection") {}

    public DbSet<Entity> Entities { get; set; }
  }

  public class Entity
  {
    [Key]
    public Guid Id { get; set; } = Guid.Parse("D46D98F3-C262-468A-9C28-83D81080CF18");

    public string Name { get; set; } = "Test";
  }
}

The problem is marked in the code. The first "Find" returns the new added entry.

But getting a new instance of the context, the entry is not found.

Even, if I run the application a second time, skipping the code adding the entry to the table, it won't find the item. The problem doesn't seem to be the "Find" method, because T've tried several other linq statements with the same result.

When I first fetch all items from the table before searching, then it works with "Find", but not with linq.

Here is the sample:

        using (var context = new MyDbContext())
        {
            // this returns all items
            var allItems = context.Entities.ToArrayAsync().Result;
            // this finds the item
            var item1 = context.Entities.Find(id);
            // this doesn't find the item
            var item2 = context.Entities.Where(x => x.Id == id).FirstOrDefault();
        }

        using (var context = new MyDbContext())
        {
            // this doesn't find the item
            var item1 = context.Entities.Find(id);
            // this also doesn't find the item
            var item2 = context.Entities.Where(x => x.Id == id).FirstOrDefault();
        }

Does anyone have an explanation? Changing the key to string or int instead of GUID, it works as expected.

SQLite doesn't support GUIDs, you have to store the value as a string or BLOB. GUIDs must be stored as strings or BLOBs. You haven't provided the table schema though, so it's hard to reproduce anything. Have you tried reading the SQLite file after you close your application? EF doesn't communicate with the database itself, it uses ADO.NET and the configured providers — Panagiotis Kanavos, Nov 29 '19 at 08:23
`I can't beleave I'm the first person running into that problem,` that's a *very* strong indicator that the problem is in the code, not the libraries. There are previous discussions about [GUID in SQLite](https://stackoverflow.com/questions/18954130/can-we-use-guid-as-a-primary-key-in-sqlite-database). GUIDs are terrible keys, *especially* in an embedded database like SQLite though. They take 2 or 4 times as much space as a 64-bit long and unless you use a sequential GUID algorithm, they cause fragmentation — Panagiotis Kanavos, Nov 29 '19 at 08:28
You didn't mention that you use SQLite.CodeFirst either. Could it be that due to a misconfiguration or an in-memory connection string, each DbContext instance creates a *new* database? Have you tried reading from the database while running your program, or after it finishes? If you configure Write-Ahead Logging you can have multiple applications reading from the same database using a SQLite client application — Panagiotis Kanavos, Nov 29 '19 at 08:31
Looks like there are previous questions about SQLite, GUIDs, and EF [like this one from 2014](https://stackoverflow.com/questions/27279177/how-does-the-sqlite-entity-framework-6-provider-handle-guids). Back then, the trouble was caused by a bug in SQLite's own ADO.NET provider. Which provider are you using? What does your connection string look like? — Panagiotis Kanavos, Nov 29 '19 at 08:37
The problem is because the GUID is being converted into a BLOB because SQLite cannot handle GUIDs. This causes the SQL Query that's generated from LINQ to fail. That explains why loading it all into memory, and then querying it works, as once loaded into memory it can compare the GUIDs correctly — MindSwipe, Nov 29 '19 at 10:37

score 2 · Answer 1 · answered Nov 29 '19 at 10:36

2

The link upon the bug gave me the idea with the connctionstring to add "BinaryGUID=True;".

And than it works as expected. Thanks.

answered Nov 29 '19 at 10:36

Wernfried

206
3
9

score 0 · Answer 2 · answered Nov 29 '19 at 10:23

Ok, I will try to answer some of the remarks.

I know about Guid-usage in SQLite. But because I want to deal with foreign keys, I considered it to be easier to use Guids instead of DB generated keys.

A look into the table shows, the Guid is stored as a 16 byte BLOB as expected and the bytes in the DB ar corresponding to the Guid I use.

This is the table create statement for that table:

CREATE TABLE "Entities" ([Id] uniqueidentifier NOT NULL PRIMARY KEY, [Name] nvarchar)

Yes, I've read the DB after closing the app. The second code sample is the one I use therefor. And there I described, that i first usage block it finds the entry with the Find method, but not with Linq and only if I fetch the whole table before (see my comments). Not reading all entries before as in the second usage block none is found.

I know, that EF uses ADO.NET, but EF "generates" the SQL statements to query the DB and may be there is someting wrong/different between Find and linq and something is cached, since it seems to work, when I fetch the whole table before. Generating the SQL statements is part of EF and not CodeFirst. Thus I'm thinking the problem has nothing to do with CF. Even when I use the existing DB, I have the same behaviour without CF.

Ther is no in-memory DB, I run both using blocks in one app, with the same connection string. And if I change the Name field with an DB browser, I also read the changes.

I posted the whole code to test. May be someone invests some time and can reproduce the behaviour. Connection string is generic:

<add name="MyConnection" connectionString="Data Source=c:\test\test.s3db" providerName="System.Data.SQLite.EF6" />

Update the question. Don't post extra comments in an answer. Answers are meant to actually answer the question — Panagiotis Kanavos, Nov 29 '19 at 10:26

score 0 · Answer 3 · answered Nov 29 '19 at 10:42

The problem lies with the SQLite DB provider converting the GUID to a blob. This blob is then stored in the Database as a byte array, not as a string. The problem then manifests itself when you try to query the database with LINQ directly, because the

context.Entities.Where(x => x.Id == id).FirstOrDefault()

gets translated to

SELECT Key, Name
FROM Entity
WHERE Entity.Key = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

So it is comparing a string literal to a byte array, which will obviously return the wrong result. Loading all the entries into memory and then querying them will result in the GUIDs being cast from their byte array representation to actual GUIDs, which LINQ can the correctly compare and select the right one.

A solution to this problem is to add the BinaryGUID=True section in your connection string.

Silly behaviour with SQLite and EF6

3 Answers3