Geek Answers Handbook

Is Entity Framework compatible with Azure SQL?

I cannot get Entity Framework to work with SQL Azure. Is it just me or not intended for compatibility? (I tried the original version of EF with VS2008, as well as the newer version of VS2010 Beta 2)

To test this, I created the simplest script. Add a separate table to the local instance of SQL Server 2008. The table has two columns, an integer primary key and a row column. I add one row to the table with the values ​​(1, foobar). Then I added the exact same setup to the Azure SQL Database.

Created a console application and generated an EF model from a local database. Launch the application, and all is well, one line can be returned from a trivial request. Update the connection string to connect to SQL Azure, and now it fails. It connects to the Azure SQL database without problems, but the query is not executed when processing the result.

I tracked the original problem using exception information. In the conceptual model, the Schema = "dbo" attribute was set for a set of objects of my only specific object. 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 that I defined, and therefore it looks like it is trying to create an object from the returned result. But for some unknown reason this trivial scenario cannot solve.

So, am I either making a fundamental mistake, or is SQL Azure incompatible with EF? And it seems to me just crazy. I want to use EF in my WebRole and then RIA Services for the Silverlight client.

+4
source share
3 answers

Until I did this, I am sure that the members of the EF team have Kevin Hoffman .

Thus, perhaps you just lost one step in the transfer process.

It looks like you tried to update EDMX (XML) manually, from one that works against the local database.

If you do this, most of the changes will be required in the StorageModel in EDMX (aka SSDL). But it looks like you made changes to the ConceptualModel element (aka CSDL).

I assume that you just need to replace all references to the dbo schema in SSDL with any Azure SQL schema schema.

Hope this helps

Alex

+3
source

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

Some time ago I did a full project and talked about my experience: http://www.sanderstechnology.com/?p=9961 I hope this can help a little!

+2
source

The Microsoft Windows Azure documentation contains How to connect to a Windows Azure SQL database using the ADO.NET Entity Framework.

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

  • Transfer the School database to the SQL database following the instructions in How to migrate the database using the Scripting Wizard (Windows Azure SQL Database) .

  • In the SchoolEFApplication project , open the App.Config file. Modify 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>
  • Press F5 to run the application against your SQL database.
+1
source

More articles:


All Articles