file based SQLite with EF6 NOT NULL Constraint failed on Insert

Question

I want to create a program that can be used just with the .exe so I needed to create a file based database from inside my program. I'm trying to use SQLite with Entity Framework so I set up following classes:

Program.cs

class Program
{
    static void Main(string[] args)
    {
        using (MyContext context = new MyContext())
        {
            context.Documents.Add(new Document() { Id = 1, CategoryId = 2, Description = "test", Keywords = "test,TEST,", Text = "TEST test Test" });
            Console.WriteLine(context.Documents.Single(x => x.Id == 1).Text);
            Console.ReadKey();
        }
    }
}

class MyContext : DbContext
{
    public DbSet<Document> Documents { get; set; }
}

class Document
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string Text { get; set; }
    public string Keywords { get; set; }
    public string Description { get; set; }
}

upon running the code it throws me this exception:

Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName

But I specified the Connection string properly as far as I can tell in my app.config:

<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>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="MyContext" connectionString="Data Source=db.sqlite" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

I've tried setting up the DB on my own in the Context Constructor (using SQLiteConnection and SQLiteCommand), but even though it creates the db and table successfully EF still gives me the same error. It doesn't create the file when I comment out the constructor.

Is there a way to achieve what I want?

I don't know much about SQLite. But is it possible that your need to give the exact path to the db file?. Have the information from: https://damienbod.com/2015/08/30/asp-net-5-with-sqlite-and-entity-framework-7/ and https://www.connectionstrings.com/sqlite/ — Sebastian Münster, Sep 16 '18 at 18:37
Everything in the config file is geared to SQL Server. Did you install a Sqlite connector through NuGet? — Gert Arnold, Sep 16 '18 at 19:37
@GertArnold yes, I tried both `Microsoft.Data.SQLite` and `System.Data.SQLite` — lenny, Sep 17 '18 at 05:35

Tejus · Answer 1 · 2018-09-17T06:14:55.677

1

Your connection string is incomplete.

<add name="MyContext" connectionString="data source=db.sqlite;initial catalog=XXXXXXXXXXXX" providerName="System.Data.SQLite"/>

The initial catalog should contain your database name. The data source is just the instance name.

edited Sep 17 '18 at 06:14

answered Sep 17 '18 at 04:16

Tejus

694
1
7
19

Now I recieve this error: `This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection` How do I get credentials for master database? I never set them – lenny Sep 17 '18 at 05:45
Change connection string to connectionString="data source=db.sqlite;initial catalog=XXXXXXXXXXXX;integrated security=True;MultipleActiveResultSets=True" – Tejus Sep 17 '18 at 05:47
It seems to think I want to use SQL Server... how exactly does EF figure out the data provider class? – lenny Sep 17 '18 at 06:03
Check out this link. I think this may help. https://stackoverflow.com/questions/38557170/simple-example-using-system-data-sqlite-with-entity-framework-6 You may want to concentrate on the connection string used. – Tejus Sep 17 '18 at 06:23
I'm using EF Core now, and it works flawlessly. Thanks for the effort though – lenny Sep 17 '18 at 06:45

score 1 · Accepted Answer · answered Sep 17 '18 at 05:39

1

You should use System.Data.SQLite instad of System.Data.SqlClient. The provider will use Microsoft's managed SQLite wrapper project, Microsoft.Data.SQLite rather than the System.Data.SQLite project.

<connectionStrings>
<add name="MyContext"
      connectionString="Data Source=|DataDirectory|db.sqlite"
      providerName="System.Data.SQLite" />
</connectionStrings>

Add this constructor to you DB context class because sometime default constructor didn't work properly.

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

}

Hopefully it works for you.

answered Sep 17 '18 at 05:39

habib

2,366
5
25
41

in the connection string, is `|DataDirectory|` some kind of variable? How do I set it? right now I'm just specifying `Data Source=db.sqlite` because to be honest right now I don't really care where the file is stored. – lenny Sep 17 '18 at 05:47
I added the constructor, and I specified an ´initial catalog´ in the connection string. I'm using ´System.Data.SQLite´ package from NuGet. I get following error: ´This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection´ – lenny Sep 17 '18 at 05:50
Just put the directory where you have to store your DB Or let it if you have to store into debug directory. – habib Sep 17 '18 at 05:50
Why are you using EF 6? You can do the same thing with EF Core very easily. – habib Sep 17 '18 at 05:56
I can? But the NuGet package `System.Data.SQLite` automatically installs EF6. Should I just install EF Core as well? I can't uninstall EF6. also now it seems to think I'm using SQL Server.... the error now is something about it not being able to connect to SQL Server.. – lenny Sep 17 '18 at 05:59
oh, or should I use EF Core with the `Microsoft.Data.SQLite` package? – lenny Sep 17 '18 at 05:59
EF Core have its own database provider. Please see this https://learn.microsoft.com/en-us/ef/core/providers/sqlite/ – habib Sep 17 '18 at 06:02
I use EF Core 1.0 with SQLite3 in windows forms application 2 years ago. Still its working fine for me. – habib Sep 17 '18 at 06:03
https://learn.microsoft.com/en-us/ef/core/get-started/uwp/getting-started Here is complete documentation to use EF Core with SQLite. – habib Sep 17 '18 at 06:06
I'm working on the link you sent, me, thanks btw. But do I need to specify anything in connection string provider name attribute in app.config? – lenny Sep 17 '18 at 06:12
No. In EF Core you do not need to specify any connection string into .config file. You just need to specify you DB file into your DB Context. – habib Sep 17 '18 at 06:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180190/discussion-between-habib-and-lenny). – habib Sep 17 '18 at 10:03

file based SQLite with EF6 NOT NULL Constraint failed on Insert

Program.cs

2 Answers2