Let EF fly for a while: How to connect to the SQLite database with entity Framework 6

Get SQLite

1. Can be obtained with NuGet package, it will also be downloaded automatically EF6

2. Download the corresponding version on the SQLite official website: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Note that there are two versions of each. NET Framework, one with the word bundle and one without. One of the installed DLLs contains SQLite.Interop.dll, and the other does not. If you run the code

"Unable to load SQLite.Interop.dll" error, copy the SQLite.Interop.dll from the installation file to the bin file. Or, there are programs in the Packages\system.data.sqlite.core.1.0.94.0\build that NuGet downloads.

Sample code

Model.cs

public class Person
    {
        public Int64 Id {get; set;} // Note to use Int64
        public string FirstName {get; set;}
        public string LastName {get; set;}
    }

    public class MyContext: DbContext
    {
        public DbSet <Person> Persons {get; set;}

        public MyContext ()
            : base ("SqliteTest")
        {

        }
    }
Program.cs

 static void Main (string [] args)
        {
            MyContext context = new MyContext ();
            var empList = context.Persons.OrderBy (c => c.FirstName) .ToList ();
            Console.WriteLine (empList.Count);

            Person people = new Person ()
            {
                FirstName = "Hello",
                LastName = "World"
            };
            context.Persons.Add (people);
            context.SaveChanges ();
            Console.ReadLine ();
        }
The sample code is very simple, it is to use EF to add and view the Person table.

Configuration config file
If you use NuGet to get Sqlite, it will automatically configure some related information in config.

<? xml version = "1.0" encoding = "utf-8"?>
<configuration>
  <configSections>
    <!-For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468->
    <section name = "entityFramework" type = "System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version = 6.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089" requirePermission = "false" />
  </ configSections>
   <connectionStrings>
    <add name = "SqliteTest" connectionString = "data source = SqliteTest.db" providerName = "System.Data.SQLite.EF6" />
  </ connectionStrings>
  <startup>
    <supportedRuntime version = "v4.0" sku = ". NETFramework, Version = v4.5" />
  </ startup>
  <system.data>
    <DbProviderFactories>
      <add name = "SQLite Data Provider" invariant = "System.Data.SQLite" description = ". NET Framework Data Provider for SQLite" type = "System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant = "System.Data.SQLite" />
      <remove invariant = "System.Data.SQLite.EF6" />
      <add name = "SQLite Data Provider (Entity Framework 6)" invariant = "System.Data.SQLite.EF6" description = ". NET Framework Data Provider for SQLite (Entity Framework 6)" type = "System.Data.SQLite. EF6.SQLiteProviderFactory, System.Data.SQLite.EF6 "/>
    </ DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type = "System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value = "v11.0" />
      </ parameters>
    </ defaultConnectionFactory>
    <providers>
      <provider invariantName = "System.Data.SqlClient" type = "System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName = "System.Data.SQLite.EF6" type = "System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </ providers>
  </ entityFramework>
</ configuration>
View Code
The data connection string is:

   <add name = "SqliteTest" connectionString = "data source = SqliteTest.db" providerName = "System.Data.SQLite.EF6" />
Note that the provider set is System.Data.SQLite.EF6.

But this configuration is still wrong.

If you run the program at this time, you will get an error:

Unable to determine the provider name for provider factory of type ‘System.Data.SQLite.SQLiteFactory’. Make sure that the ADO.NET provider is installed or registered in the application config.

Or Chinese error message:

The entity framework provider for the ADO.NET provider with the fixed name "System.Data.SQLite" was not found. Make sure that the provider is registered in the "entityFramework" section of the application configuration file.

It means that EF did not find the dll that provided System.Data.SQLite.SQLiteFactory. Let's look at the entityFramework node in the config:

  <entityFramework>
    <defaultConnectionFactory type = "System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value = "v11.0" />
      </ parameters>
    </ defaultConnectionFactory>
    <providers>
      <provider invariantName = "System.Data.SqlClient" type = "System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName = "System.Data.SQLite.EF6" type = "System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </ providers>
  </ entityFramework>
There are System.Data.SQLite.EF6 and System.Data.SqlClient, there is really no provider named System.Data.SQLite. Here I have never understood why SQLite will look for a provider named System.Data.SQLite, because the provider we configured in the connection string is also System.Data.SQLite.EF6.

Then we add a provider named System.Data.SQLite in the EF configuration node, but the type is still System.Data.SQLite.EF6. Final configuration

The red part is where the configuration has changed.

Just run the program here.

 

note:

1. Configuration of connection string.

 The data connection string can specify an absolute address or a relative address. Like my data source = SqliteTest.db, the SqliteTest.db should be in the Bin folder. If it is a web program, it can be configured in the App_Data file via Data Source = | DataDirectory | \ SqliteTest.db.

2. If the file name of the table in the database is not specified, the SQL tables generated by EF are expressed in plural. Like the entity name in my program is Person, but the name of the table that EF will look for will be People. So the table name defined in the database is People.

3. CodeFirst mode is not supported, you need to design the Sqlite table structure yourself.

 

Sample code (packages file is too large, so deleted): SqliteLinqTest.zip

Let EF fly for a while: how to connect Sqlite database with Entity Framework 6