How to use entity framework (code first) with a dynamic created connection string and a single-file database?

Question

I'm using SqlServerCompact, Entity framework with code-first and I'm generating the connection string dinamically because the database it's generated when the user requests it.

I followed suggestions from this question: How do I programmatically set the connection string for Entity-Framework Code-First? however, I still have problems, infact I get an exception complaining that the schema in the model is not the same as used in code.

That's obvius however, I want the schema to be generated from code, so I really can't understand why this happen

Can someone help me?Here is the code used:

NerdDinners.cs

class NerdDinners : DbContext
{
    public static string CreateConnectionString(string dbPath = @"|DataDirectory|\NerdDinners.sdf")
    {
        SqlCeConnectionStringBuilder sqlConnection = new SqlCeConnectionStringBuilder();
        sqlConnection.Password = "9023fase93";
        sqlConnection.DataSource = dbPath;

        EntityConnectionStringBuilder connection = new EntityConnectionStringBuilder();
        connection.Metadata = @"res://*/NerdDinnersModel.csdl|res://*/NerdDinnersModel.ssdl|res://*/NerdDinnersModel.msl";
        connection.Provider = "System.Data.SqlServerCe.3.5";
        connection.ProviderConnectionString = sqlConnection.ToString();

        return connection.ToString();
    }

    public NerdDinners() : base() { }
    public NerdDinners(string nameOrConnectionString) : base(nameOrConnectionString) { }

    public DbSet<Dinner> Dinners { get; set; }
    public DbSet<RSVP> RSVPs { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer<NerdDinners>(new NerdDinnersInitializer());
        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe");

        base.OnModelCreating(modelBuilder);
    }
}

Program.cs

class Program
{
    static void Main(string[] args)
    {
        NerdDinners nerdDinner = new NerdDinners(NerdDinners.CreateConnectionString());
        Console.WriteLine("Inizio test");

        var res = from d in nerdDinner.Dinners
                  select d;

        Console.WriteLine("Dinners:");
        foreach (var item in res)
            Console.WriteLine(item.ToString());
        Console.WriteLine("End");

        // Usato per terminazione
        Console.ReadKey();
    }
}

An this is my solution situation (an image):

Solution rapresentation

EDIT 1:

The error is a MetadataException, it complains about invalid schema

EDIT 2:

NerdDinnersInitializer.cs

class NerdDinnersInitializer : DropCreateDatabaseAlways<NerdDinners>
{
    public NerdDinnersInitializer() : base() { }

    protected override void Seed(NerdDinners context)
    {
        var toadd = new List<Dinner>
        {
            new Dinner
            {
                Address = "bla",
                DinnerID = 0,
                EventDate = DateTime.Parse("2010-12-03"),
                HostedBy = "mclink",
                Title = "Notitle",
            },
            new Dinner
            {
                Address = "Fiwe",
                DinnerID = 0,
                EventDate = DateTime.Parse("2011-12-03"),
                HostedBy = "ngi",
                Title = "Bah",
            },
        };

        toadd.ForEach(item => context.Dinners.Add(item));

        context.SaveChanges();

        base.Seed(context);
    }
}

score 2 · Answer 1 · answered Sep 17 '11 at 19:34

I'm not sure if this applies to your situation, but you can get an error if the model does not match the currently existing DB schema. You need to do something similar to this -

Database.SetInitializer<NerdDinners>(
    new DropCreateDatabaseIfModelChanges<NerdDinners>());

which will drop the existing database and create a new one based on your model.

You do have some code in your sample code that sets up an initializer -

Database.SetInitializer<NerdDinners>(new NerdDinnersInitializer());

but I'm not sure what your NerdDinnersInitializer class is doing.

You are right sorry, however it NerdDinnersInitializer inherits directly from DropCreateDatabaseIfModelChanges — Francesco Belladonna, Sep 17 '11 at 20:02

score 0 · Accepted Answer · edited May 23 '17 at 12:30

I found the answer with deep google searching. I understood that I misunderstood how code first works, infact you can't create a database you want to connect to if you are using code-first.

I still don't understand if you can save in a chosen place the database generated with code-first (I think you can't).

However, if you want to connect to an existing database file you have to use model-first or database-first approach you can't use code first

This is what I understood, hopefully I'm not wrong. This question explains it better: Why does Entity Framework Code-First (with an existing DB) keep trying get data from an EdmMetadata table?

EDIT 1: It looks like I was wrong, is possible, but you need SQL Server compact 4.0 and not 3.5 (I was having problems with it), also you need vs 2010 and entity framework 4.1. Then, just follow the answer to this question to create dinamically the string: Is possible to create a database (Sql Server compact) on a given path if we use Entity framework with code-first approach?

You can create a database with code first, just follow the conentions: http://erikej.blogspot.com/2011/04/saving-images-to-sql-server-compact.html — ErikEJ, Sep 18 '11 at 11:13
With your tutorial, I'm **almost** there, now I manage to connect to a database without Model.edmx (it was asking for it before), what the link doesn't explain, is how to use a dinamically generated connection string. I need this because I must specify the data source dinamically (can change). Any suggestion? I Solved the problem of dynamic connection; http://stackoverflow.com/questions/7458513/is-possible-to-create-a-database-sql-server-compact-on-a-given-path-if-we-use-e/7458778#7458778 — Francesco Belladonna, Sep 18 '11 at 12:57

How to use entity framework (code first) with a dynamic created connection string and a single-file database?

2 Answers2