Folks, FYI - I'm posting this as a reminder to myself to try and paste what I've done into my long-term memory, and hopefully help someone else in the future. I want a "data layer service" using EF 4.4 that talked to SQLite, SQL Server and SQL Azure databases with identical schemas. The database should be swappable via config with minimal changes.
- The scripts that generate the SQL and Lite databases need care with the types. Lite INTEGER AUTOINCREMENT columns are Int64 so you must use BIGINT IDENTITY in SQL. Lite REAL are 8-byte so use FLOAT in SQL. Don't confuse INT, INTEGER and BIGINT in Lite. - Create 3 projects with EDMX files of different names pointing to the 3 databases and set meta data to copy to output directory. All you want from these projects are the ssdl, msl and csdl files, so I post-build copy them to a work folder. - Steal one of the DbContext generated classes into a different project, trim it down and use it as if you wrote it manually (which you can do). The 3 originals are nearly identical and you don't need them. - Steal one of the POCO generating tt templates and put it in it's own project so the classes have no model dependencies. Delete the 3 POCO tt files from the model projects. The output from the 3 tt templates must be identical except for the namespace, so you can steal any one of them. - The consuming app must have the 3 sets of csdl/ssl/msl files available, so I bring them over with a pre-build xcopy. At runtime, use the EntityConnectionStringBuilder to make a full EF connect string with the appropriate ADO connect string and 3 meta files. Then you can make DB calls through the context and get POCOS back without knowing which database is being used. So it's a bit fiddly, but once you "steal" and separate the POCO generated classes, one of the DbContext classes and the sets of meta files then it's all database neutral. Only the ADO connection strings, the provider names and the 3 meta files differ at runtime and you can easily configure which ones are needed. My sanity check of all scenarios is working nicely so far, but lord knows what other gotchas I might find when the methods get more complicated. Greg (K)
