Is the Entity Framework compatible with SQL Azure?

I cannot get the Entity Framework to work against SQL Azure. Is this just me or is it not intended to be compatible? (I have tried the original release of EF with VS2008 as well as the more recent VS2010 Beta 2 version)

To check this I created the simplest scenario possible. Add a single table to a local SQL Server 2008 instance. The tab开发者_JAVA技巧le has two columns, a primary key of type integer and a string column. I add a single row to the table with values of (1, foobar). I then added exactly the same setup to my SQL Azure database.

Created a console application and generated an EF model from the local database. Run the application and all is good, the single row can be returned from a trivial query. Update the connection string to connect to the SQL Azure and now it fails. It connects to the SQL Azure database without problems but the query fails on processing the result.

I tracked the initial problem down using the exception information. The conceptual model had the attribute Schema="dbo" set for the entity set of my single defined entity. I removed this attribute and now it fails with another error...

"Invalid object name 'testModelStoreContainer.Test'."

Where 'Test' is of course the name of the entity I have defined and so it looks like it's trying to create the entity from the returned result. But for some unknown reason cannot work out this trivial scenario.

So either I am making a really fundamental error or SQL Azure is not compatible with the EF? And that seems just crazy to me. I want to use the EF in my WebRole and then RIA Services to the Silverlight client end.

While I haven't done this myself I'm pretty sure that members on the EF team have, as has Kevin Hoffman.

So it is probably just that you went astray with one step in your porting process.

It sounds like you tried to update the EDMX (XML) by hand, from one that works against a local database.

If you do this most of the changes will be required in the StorageModel element in the EDMX (aka SSDL). But it sounds like you've been making changes in the ConceptualModel (aka CSDL) element.

My guess is you simply need to replace all references to the dbo schema in the SSDL with whatever schema is the SQL Azure schema.

Hope this helps

Alex

To answer the main question - Yes, at least the Entity Framework v4 can be used with SQL Azure - I haven't honestly tried with the initial version (from the .Net Framework 3.5. SP 1).

A little while back I did a complete project and blogged about the experience: http://www.sanderstechnology.com/?p=9961 Hopefully this might help a little bit!

Microsoft's Windows Azure documentation contains How to: Connect to Windows Azure SQL Database Using the ADO.NET Entity Framework.

After creating your model, these instructions describe how to use SQL Azure with the Entity Framework:

1. Migrate the School database to SQL Database by following the instructions in How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database).

2. In the SchoolEFApplication project, open the App.Config file. Change the connection string so that it connects to your SQL Database.

   <connectionStrings>
       <add name="SchoolEntities"
            connectionString="metadata=res://*/SchoolDataModel.csdl|res://*/SchoolDataModel.ssdl|res://*/SchoolDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=<provideServerName>.database.windows.net;Initial Catalog=School;Integrated Security=False;User ID=<provideUserID>;Password=<providePassword>;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False&quot;"
            providerName="System.Data.EntityClient"/>
   </connectionStrings>
   

3. Press F5 to run the application against your SQL Database.