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: damienbod.com/2015/08/30/… and connectionstrings.com/sqlite — Sebastian Münster
– Sebastian Münster, Commented Sep 16, 2018 at 18:37
Everything in the config file is geared to SQL Server. Did you install a Sqlite connector through NuGet? — Gert Arnold
– Gert Arnold, Commented Sep 16, 2018 at 19:37
@GertArnold yes, I tried both Microsoft.Data.SQLite and System.Data.SQLite — lenny
– lenny, Commented Sep 17, 2018 at 5:35

habib · Accepted Answer · 2018-09-17 05:39:57Z

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, 2018 at 5:39

habib

2,4665 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

lenny Over a year ago

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 Over a year ago

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´

habib Over a year ago

Just put the directory where you have to store your DB Or let it if you have to store into debug directory.

habib Over a year ago

Why are you using EF 6? You can do the same thing with EF Core very easily.

lenny Over a year ago

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..

|

Tejus · Accepted Answer · 2018-09-17 06:14:55Z

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, 2018 at 6:14

answered Sep 17, 2018 at 4:16

Tejus

7241 gold badge7 silver badges19 bronze badges

5 Comments

lenny Over a year ago

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

Tejus Over a year ago

Change connection string to connectionString="data source=db.sqlite;initial catalog=XXXXXXXXXXXX;integrated security=True;MultipleActiveResultSets=True"

lenny Over a year ago

It seems to think I want to use SQL Server... how exactly does EF figure out the data provider class?

Tejus Over a year ago

Check out this link. I think this may help. stackoverflow.com/questions/38557170/… You may want to concentrate on the connection string used.

lenny Over a year ago

I'm using EF Core now, and it works flawlessly. Thanks for the effort though

Collectives™ on Stack Overflow

file based SQLite with EF6 NOT NULL Constraint failed on Insert

Program.cs

2 Answers 2

12 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Program.cs

2 Answers 2

12 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related