Why won't my DataContext use SQL Server Compact Edition 4, rather than trying to use 3.5?

Question

I'm playing around with SQL Server Compact Edition 4 CTP1 as I'd like to use it as a datastore for a low-traffic web app. When I try to create a DataContext with a connection string specifying System.Data.SqlServerCe.4.0 (in order to use LINQ To SQL), I get the following error message:

Cannot open '|DataDirectory|\data.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.

So why is my code not using version 4 of SQL CE?

Back story: I'm using Visual Web Developer Express 2010 for development, but I downloaded the WebMatrix beta and used its designer to create a SQL CE 4 .sdf file containing some test data.

Using the SqlCeConnection/SqlCeCommand/SqlCeDataReader classes, I've successfully created a basic MVC app which retrieves the test data and displays it. The SQL CE 4 binaries are copied into the app's bin folder. In my controller:

var connectionString = ConfigurationManager.ConnectionStrings["Main"].ConnectionString;
var tmp = new Dictionary<string, string>();

using(var conn = new SqlCeConnection(connectionString))
{
    conn.Open();

    using (SqlCeDataReader r = new SqlCeCommand("select * from ttest", conn).ExecuteReader())
    {
        while (r.Read())
        {
            tmp.Add(r["id"].ToString(), r["name"].ToString());
        }
    }
}

return View(new TestViewModel { 
    Items = tmp
});

The connection string in Web.config is as follows:

<add name="Main" connectionString="Data Source=|DataDirectory|\data.sdf" providerName="System.Data.SqlServerCe.4.0" />

This works perfectly, so I know the connection string is correct and that I've set up the binaries properly etc. So I thought I'd try out a bit of LINQ To SQL stuff, which is how I want to build the real app:

[Table(Name = "tTest")]
public class TestEntity
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID { get; set; }
    [Column]
    public string Name { get; set; }
}

public class Repository
{
    private Table<TestEntity> testTable;

    public Repository()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["Main"].ConnectionString;
        var context = new DataContext(connectionString);
        testTable = context.GetTable<TestEntity>();
    }

    public IQueryable<TestEntity> TestEntities
    {
        get { return testTable;  }
    }
}

And then in the controller (db being a Repository constructed in the controller constructor):

var tmp = db.TestEntities.ToDictionary(x => x.ID.ToString(), x => x.Name);

return View(new TestViewModel { 
    Items = tmp
});

But when I view the page using this code, I get the aforementioned error:

Cannot open '|DataDirectory|\data.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.

How can I force my app to use the correct version? Can anyone help?

Answer 1

LINQ to SQL is not supported by SQL Server Compact 4.0, only Entity Framework / LINQ to Entities. But if you pass a version 4 SqlCeConnection to the DataContext constructor, it will actually work!