locked
EF 4.1 with SQL Server CE 3.5 RRS feed

  • Question

  • Does EntityFramework 4.1 support SQL Server CE 3.5 databases?

    I have a simple EntityFramework 4.1 application that is failing to connect with a NotSupportedException (Unable to determine the provider name for connection of type 'System.Data.SqlServerCe.SqlCeConnection'). The app.config file is as follows:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
     <connectionStrings>
      <add name="CustomerContext" connectionString="Data Source=|DataDirectory|\Database.sdf" providerName="System.Data.SqlServerCe.3.5" />
     </connectionStrings>
    </configuration>
    


    Friday, June 17, 2011 3:03 PM

Answers

  • Correct, SQL CE 3.5 does not work with the Code First approach (the provider doesn't support DDL generation); however it should work just fine using the Model First or Database First approaches of EF 4.1.
    Brice Lambson
    Tuesday, June 21, 2011 4:59 PM

All replies

  • Hi,

    yes it supports sql server CE. I use version 4.0 for unit testing. That makes no difference. BTW using CE for unit testing is very nice, because code first recreates a small test database for each test and i can run integration test with full db support.

    Make sure you have installed SQL Server ce correctly. The error message means, that .net is not able to find the provider with that name. The provider is stored in machine.config during setup.. 

    You can add db providers manually (thats needed if you want to deploy you app with sql server ce support or run unit tests on build controllers that havent CE installed) if you references the CE DLL's to your project without installing it on your system.. Thats my app.config for version 4.0...

     

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
     <connectionStrings>
      <add name="TestDb" connectionString="Data Source=TestDatabase.sdf" providerName="System.Data.SqlServerCe.4.0" />
     </connectionStrings>
     <system.data>
      <DbProviderFactories>
       <remove invariant="System.Data.SqlServerCe.4.0"/>
       <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
       </DbProviderFactories>  
     </system.data>
    </configuration>
    
    

     


    hope that helps

    holger

    • Proposed as answer by Joba Diniz Tuesday, November 8, 2011 10:54 AM
    Sunday, June 19, 2011 11:00 AM
  • Thanks for the reply, holger. I have success with SQL Server CE 4.0, but it's version 3.5 I am asking about. Do you have a working app.config for version 3.5 with EF 4.1?
    Monday, June 20, 2011 12:22 PM
  • no i dont, but it should be the same like the posted config (but 3.5 instead 4.0)...

    if it doesnt work, it really looks like you have a corrupt installation. maybe you try to reinstall ce using the webinstaller...

    greets 

    Monday, June 20, 2011 2:10 PM
  • I've tried the following in app.config:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
     <connectionStrings>
      <add name="CustomerContext" connectionString="Data Source=|DataDirectory|\Database.sdf" providerName="System.Data.SqlServerCe.3.5" />
     </connectionStrings>
     <system.data>
      <DbProviderFactories>
       <remove invariant="System.Data.SqlServerCe.3.5" />
       <add
        name="Microsoft SQL Server Compact Data Provider"
        invariant="System.Data.SqlServerCe.3.5"
        description=".NET Framework Data Provider for Microsoft SQL Server Compact"
        type="System.Data.SqlServerCe.SqlCeProviderFactory,
         System.Data.SqlServerCe,
         Version=3.5.0.0,
         Culture=neutral,
         PublicKeyToken=89845dcd8080cc91"
       />
      </DbProviderFactories>
     </system.data>
    </configuration>
    

    I still get the same error. I would like to hear from someone who has EF 4.1 successfully working with SQL Server Compact 3.5 SP 2 to verify whether my app.config is correct or not.

    Monday, June 20, 2011 3:27 PM
  • Hi Stephen,

    I think you've already read this article: http://sondreb.com/blog/post/database-provider-factories-ef-sql-compact.aspx So have you get some packages we need to use in EF4.1+SQL CE through Nuget to the project? If not, please import those packages and have a try. If you have already done this, I think you could take Holger's advice to reinstall ce or you change to use SQL CE 4.0.

    And more, this article will show you the evidence EF4.1 can work fine with SQL CE 3.5:

    http://erikej.blogspot.com/2011/04/saving-images-to-sql-server-compact.html

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 21, 2011 2:46 AM
  • Are you using code First?
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Tuesday, June 21, 2011 8:47 AM
  • Yes, I am using Code First.
    Tuesday, June 21, 2011 12:27 PM
  • OK, I don't think 3.5 works with Code First, only 4.0.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Tuesday, June 21, 2011 12:29 PM
  • Erik, thanks for your reply. Can you point me to some official documentation or confirmation of your statement that 3.5 does not work with code first?
    Tuesday, June 21, 2011 12:30 PM
  • No, not other that I can get the same error in my sample linked above, if use the 3.5 provider, and the information provided here: http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx stating the 4.0 works with Code First (getting the message that 3.5 did not).
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Tuesday, June 21, 2011 12:57 PM
  • Correct, SQL CE 3.5 does not work with the Code First approach (the provider doesn't support DDL generation); however it should work just fine using the Model First or Database First approaches of EF 4.1.
    Brice Lambson
    Tuesday, June 21, 2011 4:59 PM
  • Thank you, Brice, for the confirmation.

     

    Stephen

    Tuesday, June 21, 2011 5:06 PM
  • Brice's answer has to be qualified ... EF can work with SQL CE 3.5 as long as you do not rely on the creation and migration (from EF 5.0) features of EF.

    See http://stackoverflow.com/a/12690003/266919 for a howto.

    Tuesday, October 2, 2012 12:17 PM