Why won't my DataContext use SQL Server Compact Edition 4, rather than trying to use 3.5?
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"].Conne开发者_高级运维ctionString;
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?
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!
精彩评论