(window.webpackJsonp=window.webpackJsonp||[]).push([[1055],{1462:function(t,e,a){"use strict";a.r(e);var s=a(31),r=Object(s.a)({},(function(){var t=this,e=t.$createElement,a=t._self._c||e;return a("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[a("h1",{attrs:{id:"entity-framework-with-sqlite"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#entity-framework-with-sqlite"}},[t._v("#")]),t._v(" Entity Framework with SQLite")]),t._v(" "),a("p",[a("a",{attrs:{href:"https://www.sqlite.org/about.html",target:"_blank",rel:"noopener noreferrer"}},[t._v("SQLite"),a("OutboundLink")],1),t._v(" is a self-contained, serverless, transactional SQL database. It can be used within a .NET application by utilizing both a freely available .NET SQLite library and Entity Framework SQLite provider. This topic will go into setup and usage of the Entity Framework SQLite provider.")]),t._v(" "),a("h2",{attrs:{id:"setting-up-a-project-to-use-entity-framework-with-an-sqlite-provider"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#setting-up-a-project-to-use-entity-framework-with-an-sqlite-provider"}},[t._v("#")]),t._v(" Setting up a project to use Entity Framework with an SQLite provider")]),t._v(" "),a("p",[t._v("The Entity Framework library comes only with an SQL Server provider. To use SQLite will require additional dependencies and configuration. All required dependencies are available on NuGet.")]),t._v(" "),a("h3",{attrs:{id:"install-sqlite-managed-libraries"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#install-sqlite-managed-libraries"}},[t._v("#")]),t._v(" Install SQLite Managed Libraries")]),t._v(" "),a("p",[t._v("All of the mananged depedencies can be installed using the NuGet Package Manager Console. Run the command "),a("code",[t._v("Install-Package System.Data.SQLite")]),t._v(".")]),t._v(" "),a("p",[a("a",{attrs:{href:"https://i.stack.imgur.com/gQayv.png",target:"_blank",rel:"noopener noreferrer"}},[a("img",{attrs:{src:"https://i.stack.imgur.com/gQayv.png",alt:"Installing System.Data.SQLite"}}),a("OutboundLink")],1)]),t._v(" "),a("p",[t._v("As shown above, when installing "),a("code",[t._v("System.Data.SQLite")]),t._v(", all related managed libraries are installed with it. This includes "),a("code",[t._v("System.Data.SQLite.EF6")]),t._v(", the EF provider for SQLite. The project also now references the assemblies required to use the SQLite provider.")]),t._v(" "),a("p",[a("a",{attrs:{href:"https://i.stack.imgur.com/tgCyG.png",target:"_blank",rel:"noopener noreferrer"}},[a("img",{attrs:{src:"https://i.stack.imgur.com/tgCyG.png",alt:"Project references"}}),a("OutboundLink")],1)]),t._v(" "),a("h3",{attrs:{id:"including-unmanaged-library"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#including-unmanaged-library"}},[t._v("#")]),t._v(" Including Unmanaged Library")]),t._v(" "),a("p",[t._v("The SQLite managed libraries are dependent on an unmanaged assembly named "),a("code",[t._v("SQLite.Interop.dll")]),t._v(". It is included with the package assemblies downloaded with the SQLite package, and they are automatically copied into your build directory when you build the project. However, because it's unmanaged, it will not be included in your reference list. But make note, this assembly most be distributed with the application for the SQLite assemblies to work.")]),t._v(" "),a("p",[a("strong",[t._v("Note: This assembly is bit-dependent, meaning you will need to include a specific assembly for each bitness you plan to support (x86/x64).")])]),t._v(" "),a("h3",{attrs:{id:"editing-the-project-s-app-config"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#editing-the-project-s-app-config"}},[t._v("#")]),t._v(" Editing the project's App.config")]),t._v(" "),a("p",[t._v("The "),a("code",[t._v("app.config")]),t._v(" file will require some modifications before SQLite can be used as an Entity Framework provider.")]),t._v(" "),a("h3",{attrs:{id:"required-fixes"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#required-fixes"}},[t._v("#")]),t._v(" Required Fixes")]),t._v(" "),a("p",[t._v("When installing the package, the "),a("code",[t._v("app.config")]),t._v(" file is automatically updated to include the necessary entries for SQLite and SQLite EF. Unfortunately these entries contain some errors. They need to be modified before it will work correctly.")]),t._v(" "),a("p",[t._v("First, locate the "),a("code",[t._v("DbProviderFactories")]),t._v("element in the config file. It is within the "),a("code",[t._v("system.data")]),t._v(" element and will contain the following")]),t._v(" "),a("div",{staticClass:"language-cs extra-class"},[a("pre",{pre:!0,attrs:{class:"language-cs"}},[a("code",[a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("DbProviderFactories"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("remove")]),t._v(" invariant"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.EF6"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("add")]),t._v(" name"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"SQLite Data Provider (Entity Framework 6)"')]),t._v(" invariant"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.EF6"')]),t._v(" description"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('".NET Framework Data Provider for SQLite (Entity Framework 6)"')]),t._v(" type"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("remove")]),t._v(" invariant"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("add")]),t._v(" name"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"SQLite Data Provider"')]),t._v(" invariant"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite"')]),t._v(" description"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('".NET Framework Data Provider for SQLite"')]),t._v(" type"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.SQLiteFactory, System.Data.SQLite"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("DbProviderFactories"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n\n")])])]),a("p",[t._v("This can be simplified to contain a single entry")]),t._v(" "),a("div",{staticClass:"language-cs extra-class"},[a("pre",{pre:!0,attrs:{class:"language-cs"}},[a("code",[a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("DbProviderFactories"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("add")]),t._v(" name"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"SQLite Data Provider"')]),t._v(" invariant"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.EF6"')]),t._v(" description"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('".NET Framework Data Provider for SQLite"')]),t._v(" type"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.SQLiteFactory, System.Data.SQLite"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("DbProviderFactories"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n\n")])])]),a("p",[t._v("With this, we have specified the EF6 SQLite providers should use the SQLite factory.")]),t._v(" "),a("h3",{attrs:{id:"add-sqlite-connection-string"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#add-sqlite-connection-string"}},[t._v("#")]),t._v(" Add SQLite connection string")]),t._v(" "),a("p",[t._v("Connection strings can be added to the configuration file within the root element. Add a connection string for accessing an SQLite database.")]),t._v(" "),a("div",{staticClass:"language-cs extra-class"},[a("pre",{pre:!0,attrs:{class:"language-cs"}},[a("code",[a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("connectionStrings"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("add")]),t._v(" name"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"TestContext"')]),t._v(" connectionString"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"data source=testdb.sqlite;initial catalog=Test;App=EntityFramework;"')]),t._v(" providerName"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"System.Data.SQLite.EF6"')]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("connectionStrings"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n\n")])])]),a("p",[t._v("The important thing to note here is the "),a("code",[t._v("provider")]),t._v(". It has been set to "),a("code",[t._v("System.Data.SQLite.EF6")]),t._v(". This tells EF that when we use this connection string, we want to use SQLite. The "),a("code",[t._v("data source")]),t._v(" specified is just an example and will be dependent on the location and name of your SQLite database.")]),t._v(" "),a("h3",{attrs:{id:"your-first-sqlite-dbcontext"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#your-first-sqlite-dbcontext"}},[t._v("#")]),t._v(" Your first SQLite DbContext")]),t._v(" "),a("p",[t._v("With all the installation and configuration complete, you can now start using a "),a("code",[t._v("DbContext")]),t._v(" that will work on your SQLite database.")]),t._v(" "),a("div",{staticClass:"language-cs extra-class"},[a("pre",{pre:!0,attrs:{class:"language-cs"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("public")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("class")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token class-name"}},[t._v("TestContext")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(":")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token type-list"}},[a("span",{pre:!0,attrs:{class:"token class-name"}},[t._v("DbContext")])]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("{")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("public")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[t._v("TestContext")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(":")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("base")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"name=TestContext"')]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("{")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("}")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("}")]),t._v("\n\n")])])]),a("p",[t._v("By specifying "),a("code",[t._v("name=TestContext")]),t._v(", I have indicating that the TestContext connection string located in the "),a("code",[t._v("app.config")]),t._v("file should be used to create the context. That connection string was configured to use SQLite, so this context will use an SQLite database.")])])}),[],!1,null,null,null);e.default=r.exports}}]);