1

I have a project to create a program that can operate in two modes:

  1. internal users access a centralized database (SQL Server) and can view/edit each others items, or

  2. external customers create all their own data locally (SQL Server Compact) and package it up in XML over e-mail to request a quote.

The question is, what's the best way to do this to minimize maintenance and maximize EF functionality? I'd also like to use stored procedures in SQL Server for write operations, but this isn't a top priority if too much trouble.

I could hand-create a separate SSDL before deployment, but this is extra work and error-prone. I could go Model First, but I think it would complicate database updates for both providers. I could go the Code First direction using the DbContext Generator T4 templates, but then I lose a lot of EF benefits like change tracking and stored procedure mapping. And with CF, I'd have to greatly enhance the T4 templates or I still have to create a separate SSDL.

Is there an article or any tools to make this easier?

Edit: I decided the best way to accomplish this was to use Code First to create my model and use the new code first migrations. With migrations I can generate a change script for the full server instance and I can just apply the full changes on the local CE database. The other advantage is that I have full control over my connection string and can really point it at any provider.

It's a little extra work to create the POCO classes by hand, to create the configuration classes (I prefer defining by Fluent API), and to add the extras (like unique indexes) to the first migration class, but in the end its the least work overall.

I'll have to figure out how to shunt in the stored procedures usage at a later date, but EF 5 might be available by then and have solved my problem.

N Jones
  • 1,004
  • 11
  • 18

1 Answers1

1

I'd also like to use stored procedures in SQL Server for write operations, but this isn't a top priority if too much trouble.

SQL Compact doesn't support stored procedures so if you mean this seriously you will not be able to reuse your mapping any way.

I could go the Code First direction using the DbContext Generator T4 templates, but then I lose a lot of EF benefits like change tracking and stored procedure mapping.

You will lose just stored procedure mapping . Change tracking will work in the same way. You will also be able to use same mapping code for both database server but you will have to figh with some minor differences between SQL server and SQL Server compact.

I could hand-create a separate SSDL before deployment, but this is extra work and error-prone.

You will have to do that if you want to use EDMX and both big SQL Server and SQL Server Compact with the same code base. Moreover you will have to limit features of your big SQL Server implementation to only features supported by SQL Server Compact.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thanks for the reply. Since a most of the database access will be read operations, I still envision a high degree of reuse. But for security, I don't want to give everyone access for CUD operations. Other than that, I'm fine with all the other restricting factors. I'm just looking for the most convenient way to do it. – N Jones Mar 19 '12 at 21:34