How do I connect to a .sdf local database file in C#

Question

I am using local SQL Server CE database file (.sdf) and Entity Framework for my task.

I have created my models and now entity framework should map them to local database.

I have this code in my Main method which inserts some data to database(to check):

var db = new Context()
var blog = new Blog { Name = 'FirstBlog'};
            db.Blogs.Add(blog);
            db.SaveChanges(); 

and this is my connection string:

<connectionStrings>
   <add name="Connection" 
        connectionString="Data source=|DataDirectory|\LocalDB.sdf&quot;" 
        providerName="System.Data.EntityClient" />
</connectionStrings>

when I run this, I get this error:

Format of the initialization string does not conform to specification starting at index 0.

I have tried several connection string but none worked.

How can I fix this?

UPDATE:

As suggested I changed my connection string to be:

<connectionStrings>
   <add name="Connection" 
        connectionString="provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data source=|DataDirectory|\LocalDB.sdf&quot;" 
        providerName="System.Data.EntityClient" />
</connectionStrings>

but this gives me the following error:

Some required information is missing from the connection string. The 'metadata' keyword is always required.

Answer 1

First Install this EntityFramework.SqlServerCompact nuget package and then set your connection string to somting like this:

<connectionStrings>
    <add name="connection" connectionString="Data Source=|DataDirectory|\TestDb.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>

**Note:**By default entity framework will create TestDb.sdf if it doesn't exsist in you solutions \bin\Debug directory and after deployment it will be in C:\Users\YourUserName\AppData\Roaming this directory