As described in my blog post here, Entity Framework Tools support for SQL Server Compact 4.0 in Visual Studio 2013 is broken, because SQL Server Compact is no longer supported by Server Explorer in VS 2013. The latest release of my SQL Server Compact Toolbox, version 3.7.1, currently available in beta release from here, restores the Entity Framework Tools support for SQL Server Compact 4.0 by installing a simple DDEX provider.
Developing DDEX providers is a bit of a dark art, which has put me off even thinking about it, but a recent discussion on CodePlex drew my attention to the DDEX sample provider included with the Entity Framework source code. In particular the fact that the Entity Framework Tools does not get any information from the DDEX provider apart from the connection information, so all Tables, Columns etc. are enumerated via code in the Entity Framework provider, not the DDEX provider. Quote from the included Word document: “Thus, the information shown in the wizard GUI comes directly from the EF provider. The wizard lets users select which tables, views and stored procedures to include in the model”.
So my “simple” provider enables you to use the Entity Framework Tools, for example “Generate Model from Database”, “Generate Database from Model”, “Update Model from Database” and also the EF Power Tools Reverse engineer feature. (Soon to be included directly in the EF Tools).
Once the DDEX Provider has been installed, (which consists of placing the provider DLL on disk, and add a number of registry entries), you can add a connection to any SQL Server Compact 4.0 database from the Toolbox, and it will be added to Server Explorer:
(If you do not see the label at the bottom, but potentially a warning instead, one of the requirements for using the provider have not been fulfilled)
The requirements for the provider are:
- Visual Studio 2013 Pro or higher (it is not possible to add 3rd party DDEX providers to any Express edition, please correct me if I am wrong)
- The Simple DDEX provider must be registered (may require a VS restart after launching with version 3.7.1 the first time), you can verify via About dialog in Toolbox.
- The 4.0 DbProvider must be properly registered, use the About dialog in the Toolbox to confirm:
Finally, you can of course confirm the presence of the provider in the Server Explorer Choose Data Source dialog:
To connect via Server Explorer, select the “SQL Server Compact 4.0 (Simple by ErikEJ)” data source, click continue, and all you normally would need to enter is the path to the database file:
Notice that this UI does not let you create a new database file, but you can do that from the “Add Connection…” dialog in the Toolbox.
With that in place, you have access to the exact same Entity Framework Tools features that you had in Visual Studio 2012. Happy coding (and please let me know if your encounter any issue with this feature)
23 comments:
So, what does it mean for SQL Server Compact Edition that this feature was left out of VS2013, if it was apparently easy for you, external to Microsoft, to implement? (If you've answered this elsewhere please just point me to it, thanks!)
-- David
David: Not exactly sure what you are asking, but I suppose it Means that it was hard to use SqlCe with EF in VS2013, and now it is less hard. (And believe me, it was not "easy")
a) All I meant was ... does leaving out the feature in the newer VS mean SQL Server Compact is being deprecated or neglected, by Microsoft, in a way that bodes ill for depending on it in the future?
b) Ok, it wasn't easy! But it was possible! And if it was possible for you ... it was possible for them ... I didn't mean to disparage your hard work! Not when I'm heavily dependent on SQL Server Compact Toolbox!
-- David
Thanks for the kind words, David. re a: Expect no future versions of SQL Server Compact, still waiting for an official statement from Microsoft. The official product support lifecycle is here: http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=SQL+Server+Compact&Filter=FilterNO
hi ErikEJ, I'm following your blog. I'm new windows phone 8 dev. I want create a application that have many audio files and I need to add my audio files (.mp3) to sqlce table. I'm not sure that sqlce support for this or not ? Could you please give me some information or How can I do this ? Please help
Kaanmookob: I would not recommend storing large mp3 files in your SQL CE db, store just the names and store the files in the file system
Jeff: deleted your comment by mistake. Have you opened the Toolbox after install, then restarted VS?
Hi Erik,
first big 10x for this toolbox.
could you please advice me on how to use the "sync framework tools" seems to be disabled.
all i need is to sync my local sql ce 4.0 db with an sql server instance only upload direction.
any help will do.
Niv: You need the 2,1 Sync framework and SqlCe 3,5 installed to use the sync tools. See junet blog for how to use 4,0 with a binding redirect
Hi Again,
10x 4 the quick resonse.
sync 2.1 sdk is installed and 3.5 is installed still sync menu item is grey
am i missing something?
Niv: The 3.5 DbProvider must Be enabled, IF not, reinstall 3.5. You can check the Toolbox About dialog.
Hi Erik
No, I hadn't restarted VS (RTFM). That fixed it.
The instruction to do so wasn't easy to find, though. Maybe you could show a message after installation, if that's possible?
Keep up the good work.
Thanks,
Jeff Bowman
Fairbanks, Alaska
Jeff: Yes, great suggestion, if the DDEX install can proceed (all requirements are fulfilled) and completes, I will show a message in the Toolbox toolbar to restart VS https://sqlcetoolbox.codeplex.com/workitem/11598
Hi Erik
Nice. Next DDEX request... ;-)
Any chance of drag/drop onto a DBML surface?
Thanks,
Jeff Bowman
Fairbanks, Alaska
Jeff: Are you using 4.0 or 3.5? It was very painful simply to get the basic DDEX provider up and running, in particular for 3.5, as it does not have DbConnectionStringBuilder and GetSchema. So adding column enumeration and drag/drop I could not manage. But as this project is open source, I would be happy to have your contribution, and can alos provide pointers on what to do (I know that and con provide sample code, just could not get it to work)
Hi Erik
Aha. I was hoping to get involved in an open source project one of these days. This might be it [not that I really have time ;-)].
VB, I hope...?
Thanks,
Jeff Bowman
Fairbanks, Alaska
Jeff: C# - sorry - contact me by email should you decide to proceed anyway
Hi Erik,
Great tool!
I have just installed your 3.7.1 toolbox onto my VS 2013 Pro dev machine but when I check the About box all I see is a "No" against "SQL Server Compact 4.0 Simple DDEX Provider" instead of "Yes". As a result, I can't create an EF 6 model against my CE database. Any ideas?
Take care,
Coder Dude
Dude: Make sure you open the Toolbox and then restart VS.. Let me know if that does not work for you. You can also use my Code Generation feature from the Toolbox context menu without the DDEX provider.
Hi Erik,
Thanks for the tip. That worked!
I was able to use your CodeGen feature to create an edmx model, however when I attempt to create one using the VS Wizard I keep getting the error message "...An Entity Framework database provider compatible with this version could not be found for your data connection..."
Oh well.
Coder Dude
Good Day David.
I have been trying to develop a windows form application using EF and SQL Compact. I have tried almost all compact editions but on db.savechages it fails with error :An error occurred while updating the entries. See the inner exception for details.
I have searched and tried different methods stated by many developers but still i cant save data. I need your help please.
sample code below.
using (var context = new AWModel.Model1Container())
{
var peoples = new Person {Name = "james", SurName = "john" };
context.People.AddObject(peoples);
context.SaveChanges();
var item = context.People.ToList();
}
}
}
thanks
Bradley: Foolow the error message and look at the Inner exception...
Post a Comment