Silly behaviour with SQLite and EF6

entity framework transaction multiple savechanges
entity framework rollback after savechanges
entity framework begintransaction
entity framework transaction scope
transactionscope entity framework core
ef6 commit
entity framework transaction multiple contexts
entity framework transaction rollback

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.


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

And than it works as expected. Thanks.

Can't finnish ADO.NET Entity Data Model wizard SQlite EF6 VS , NET Entity Data Model for SQlite and when I finish the wizard I get an exception NET Framework Data Provider for SQLite (Entity Framework 6)" type="System. At least it's not just me doing something completely stupid! :)� In this post, a Data access layer is implemented using the repository pattern together with entity Framework 6 and SQLite as a database. The post continues on from Getting started, using SQLite with .NET UPDATE 02.03.2014: Using the new SQLite version 1.0.91.3 now with the SQLite provider.


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" />

Why you shouldn't use Entity Framework with Transactions (Example), That behavior is actually cool and very useful, right? NO. Absolutely not. full stop. Why not using .NET Transactions along with EntityFramework. It runs in the SQLite Entity Framework provider to convert a string into a Guid. The provider then emits a SQL query which compares the value in the column to the Guid. The problem is that the EF provider doesn't like the expression because the column type in EF is byte[] , but the thing being compared is a Guid.


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.

c#, Silly behaviour with SQLite and EF6. 发表于 2019-11-29 08:11:48. 活跃于 2019- 11-29 10:42:27. 查看60 次. c# entity-framework sqlite� "I have done somé testing, and the SQLite support for the EF Tools appear to be completely broken, due to an incomplete implementation of the EF6 provider, which is also not installed in GAC. I would file an issue with the System.Data.SQLite developers"


Simple Demo Entity Framework - the beast unleashed, The goal is to demonstrate every aspect of EF 6 Code First that I can recall. I will be following the KISS concept: "Keep it Simple Stupid. We got this exception because the default behaviour of the Database Initialisation does NOT ALLOW for Model Re: EF6 and SQLite (Most recent versions) - How to. NOTE: The extra "remove" element below is to prevent the design-time support components within EF6 from selecting the legacy ADO.NET provider for SQLite (i.e. the one without any EF6 support). It appears to only consider the first ADO.NET provider in the list within the resulting "app.config" or "web.config" file.


Save array of string EntityFramework Core - Kimserey's blog, This behaviour is great if our initial model has links to other objects but not so great when the array inside of model is only composed by Install EntityFramework Core Sqlite 2. Here I am creating a dumb model as example. [InvalidOperationException: No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SQLite’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file] This line could be missing in your configuration file (inside <providers>)


The Reformed Programmer – I am a freelance .NET Core back-end , When I first started using EF6 I did just that, but ended up with a lot of code! unless I want to change the delete behaviour (I talk about delete behaviour later). A database server, such as SQL Server, Sqlite, PostgreSQL… to break a nut” , or in software principle, KISS (Keep it Simple, Stupid). The connection string for SQLite database is simple. It has the format of Data Source=<Path of the file>. The connection string is the only difference from any other LINQ to SQL implementation. Entity Framework. Other than LINQ to SQL, Entity Framework is available for use. In web.config, the following configuration sections should be available.