C# EF Code first - create database and add data

Question

I want to create database and add some data. I added EF and I want to use code first. I have these classes:

public class Question
{
    public bool Sort { get; set; }
    public int QuestionID { get; set; }
    public int Level { get; set; }
    public string Description { get; set; }
    public string Answer1 { get; set; }
    public string Answer2 { get; set; }
    public string Answer3 { get; set; }
    public string Answer4 { get; set; }
    public string RightAnswer { get; set; }
    public bool Show { get; set; }
}

public class QuestionDb : DbContext
{
    public DbSet<Question> Questions { get; set; }
}

This is my connection string:

<add name="ConvertCSVtoSQL.QuestionDb" connectionString="Data Source=|DataDirectory|ConvertCSVtoSQL.QuestionDb.sdf"
      providerName="System.Data.SqlServerCe.4.0" />

Now I am creating database like this:

using (var db = new QuestionDb())
        {

            foreach (var question in questions)
            {
                db.Questions.Add(question);
            }
            db.SaveChanges();
        }

It creates database but if I have some data in questions which I want to add I get error:

The Entity Framework provider type 'System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' for the 'System.Data.SqlServerCe.4.0' ADO.NET provider could not be loaded. Make sure the provider assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

I tried to add some initializers but it didn't help:

Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0",@"C:\Path\To\",@"Data Source=C:\Path\To\DbFile.sdf");
Database.SetInitializer(new CreateDatabaseIfNotExists<QuestionDb>());

So where is problem?

Proberly your connectionstring that makes the error. Check "mvc music store" and see how he did. — thatsIT
So did you visit the link in the exception message? What you tried does not seem to be inspired by that. — Gert Arnold
Thanks, I thought I don't find any useful information but I looked there and I added EntityFramework.SqlServerCompact package by Nuget and It helps. — Libor Zapletal

Libor Zapletal Libor Zapletal · Accepted Answer · 2012-12-22T17:39:44

I looked at link in exception, add nuget package EntityFramework.SqlServerCompact to my solution and it helps.

C# EF Code first - create database and add data

3 Answers