Migrating EF model-first to Azure

Question

I have an ASP.NET MVC app that I want to put on Azure.  I've been developing it with EF Model First.  As such, the magic providerName and connectionString attributes created by the EF Designer look like this:

providerName="System.Data.EntityClient"
connectionString="
 metadata=res://*/Models.MyApp.csdl|res://*/Models.MyApp.ssdl|res://*/Models.MyApp.msl;
 provider=System.Data.SqlServerCe.4.0;
 provider connection string="Data Source=|DataDirectory|\MyApp.sdf""

The tutorials that I've found don't deal with the EntityClient provider.  How do I go about changing this to work with SQLAzure?

Bob

Answer 1

Hi Bob,

The connection string to your SQL Azure Database, for Entity Framework should look like this: 

<add name="MyDatabaseModelEntities" connectionString="metadata=res://*/MyDBModel.csdl|res://*/MyDatabaseModel.ssdl|res://*/MyDatabaseModel.msl;provider=System.Data.SqlClient;provider connection string="data source=abcdefg123.database.windows.net;initial catalog=MyDatabase;persist security info=True;user id=MyDatabaseUser@abcdefg123;password=p@$$w0rd;multipleactiveresultsets=True;Trusted_Connection=False;Encrypt=True;App=EntityFramework"" providerName="System.Data.EntityClient" /> 

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 2

Ok, that confirms my suspicion: the standard connection string claptrap is inserted into the "provider connection string" portion of the connection string.

I can run my app locally under the emulator, both in Debug and Release configurations, but when I try to run it on the Azure website, the login/logout stuff works but at the point where my code would access my model data it just displays a beautiful, red message that says "Error. An error occurred while processing your request."

This leads to two obvious questions:

1. What's wrong with my EntityClient provider string, and, more important,

2. How do I get back enough information from the deployed app to figure out where it was, what it was trying to do, and why it failed?

FYI, my connection strings, in Web.Release.Config, look like this:

<add name="IndigoContainer"
     providerName="System.Data.EntityClient" 
     connectionString="metadata=res://*/Models.Indigo.csdl|res://*/Models.Indigo.ssdl|res://*/Models.Indigo.msl;
                       provider=System.Data.SqlClient;
                       provider connection string=&quot;
                          data source=xxx.database.windows.net;Initial Catalog=Indigo;
                          User ID=bob@xxx;Password=xxx;Encrypt=true;
                          Trusted_Connection=false;MultipleActiveResultSets=True;App=EntityFramework
                          &quot;"
     xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />
<add name="DefaultConnection"            
     providerName="System.Data.SqlClient" 
     connectionString="data source=up0ile7glq.database.windows.net;Initial Catalog=Indigo;
                       User ID=bob@xxx;Password=xxx;Encrypt=true;
                       Trusted_Connection=false;MultipleActiveResultSets=True" 
     xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />

Answer 3

2. How do I get back enough information from the deployed app to figure out where it was, what it was trying to do, and why it failed?

This article (http://msdn.microsoft.com/en-us/library/ff966484.aspx) has good info about debugging an app in the cloud.

Answer 4

Ok, so I figured out how to enable IntelliTrace, and I got this:

So, I assume that my problem is that the deployed Azure app can't find the System.Data.EntityClient magic.  Help!

Answer 5

Hi,

Could it be that it is the Entity Framework DLL that is missing instead?

Try the following, please: Right-click your EntityFramework DLL (in your Project) and then make sure that the Copy Local is set to True. Redeploy your app to Azure and retry.

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 6

Hi Carlos,

> Right-click your EntityFramework DLL

I can't find anything to "right-click" that gives that information.  If I select (left-click) EntityFramework in my project References, the Properties window shows that Copy Local is True:

I do notice that several referenced assemblies have CopyLocal set to false:  Microsoft.CSharp, Microsoft.WindowsAzure.ServiceRuntime, and a bunch of System.* assemblies.

Answer 7

Hi Carlos,

Thanks for you help, but I'm still good and stuck.  I'm going to restart this thread in the SQL Azure forum where, in theory, MS Tech Support will take me under their wing if I don't get a resolution in a couple of days.

I went back to square one and created a new MVC 4 app, added a Person entity using the designer, added the EF 5.x DbContext code generation item, and used the scaffolding magic to create a functional People controller.  For the "provider connection string" magic, I copied the ADO.NET connection string that the management portal gives me, without the "tcp:" and port number, and with the additional "MultipleActiveResultSets".  It works under the Azure emulator, but it fails when I run it online.  I did notice that System.Data.Entity has Copy Local set to False.  I changed it to True, but that didn't help.

Bob

Answer 8

Hi Bob.at.SBS,

Please also take a look at this article about, MVC + Code-First + Azure, and scroll down a bit ,to the Azure ConnectionString part. It goes into some level of detail regarding the Azure connection string.

There is one thing in that article that raises my attention and that could be related with the issues you are having (EF code first on SQL Azure):

Entity Framework and Azure

Entity Framework 4.1 and Azure work quite nicely together, as long as the configuration settings are correct. Here are some of the key things to have in mind while deploying a CF app to Azure. If any of this is not clear to you, please continue reading, as each bullet is explained in the appropriate context below:

• Add PersitSecurityInfo=true to the connection string to allow Code First to create the database in SQL Azure. Make sure to remove PersistSecurityInfo from the connection string after the database is created.
• Ensure that any assembly referenced in your project that is newer than .NET 4.0 has Copy Local = true
• Make sure all third-party assemblies are signed
• Uploading the security certificate is key for Visual Studio to communicate with Windows Azure
• Make sure to set CustomErrors on Web.Config to the RemoteOnly so that errors are only shown on remove clients.
• Use the ‘Upgrade’ option on the Windows Azure Management portal whenever you make changes to the application. It is faster than re-deploying the app from visual studio
• System.Transactions transactions cannot be used with SQL Azure. Please see this article for General SQL Azure Guidelines and Limitations
• You may run into connection retry issues. Check out this blog post for troubleshooting options.

---

Best Regards,
Carlos Sardo

Answer 9

Just wanted to let you know that I'm still working on this... I seem to have gone from bad to worse.  I decided to create a super-simple code-first app to see if I could get that to work.  But I didn't even get that far.  Now, when I just create a new MVC 4 app and try to run it under the local Azure emulator, I get an InvalidOperationException that tells me 'To call this method, the "Membership.Provider" property must be an instance of "ExtendedMembershipProvider"'.  I've posted a question in the Windows Azure forum to try to get past this latest stumbling block.

I've gotta say, all of the magic templates, Web Installers, and NuGet installers are great when things work, and it gets me a lot of functionality quickly without me having to learn a zillion different, new technologies.  But all of this magic introduces a whole lot of moving parts, not all of which are thoroughly happy with each other all the time...

Answer 10

The Grateful Dead album cover says it all: What a Long Strange Trip It's Been.

It turned out that my project had numerous problems that caused it fail.  Since I believe that my experience is probably fairly typical of newbies trying to wrap my brain around all of these pieces, I present, forthwith, these Things You Need To Know (that took me several days to dig out of the random 3rd-party blogs, forums, tutorials, and articles that seem to have replaced any sort of coherent documentation provided by Microsoft):

1. Each Entity Framework (EF) context must reside in a different database.  This gem came to me from a comment in another forum.  I have yet to find an article, tutorial, or (gasp!) MS documentation that tells me this.  If you look at the connection strings that I posted earlier in this thread, you'll see that I was trying to use the same Azure database for both the DefaultConnection magic and my own EF data.  This was the actual problem that caused my problem.
2. The "tcp:" nomenclature in the connection string is, in fact, optional.  According to the gentleman who answered this forum posting, if you omit the "tcp:" nomenclature then you incur some runtime overhead while the server (unsuccesfully) tries other connection options, such as named pipes, before deciding to use the default TCP connection.  Just FYI, the easiest way to get your Azure connection string is to right-click your Azure Deployment project in the VS Solution Explorer; Select Browse to Management Portal; Click on your database to view its dashboard, and click on Show connection strings.
3. Don't forget to add MultipleActiveResultSets=True to your connection string.
4. If you are using MVC 4 then do not install the ASP.NET Universal Providers. The MVC 3 tutorials all tell you -- with no explanation why -- to install the ASP.NET Universal Providers in order to get the magic "membership" claptrap (registration, login, and logout) to work in Azure.  If you use this same cookbook for an MVC 4 app you get a runtime error that says: To call this method, the "Membership.Provider" property must be an instance of "ExtendedMembershipProvider".  It turns out that MVC 3 uses the ASP.NET SQL membership providers, as described here: http://msdn.microsoft.com/en-us/library/aa478948.aspx.  The MVC 4 project template uses the new and improved SimpleMembership extension to the ASP.NET membership framework.  Here are a couple of good links for this subject:  http://blog.osbornm.com/archive/2010/07/21/using-simplemembership-with-asp.net-webpages.aspx and http://www.asp.net/web-pages/tutorials/security/16-adding-security-and-membership.
5. Your Azure project must target .Net Framework 4.0 or lower.  You will run into this gotcha if you are a VS 2012 early adopter.  Azure doesn't tolerate .Net Framework 4.5 (yet), which is the default framework target in VS 2012.  In Visual Studio, you must select the .Net Framework version on the first page of the New Project dialog.  Converting an existing 4.5 project to 4.0 is a major pain.

And now, for your reading pleasure, I climb upon my soapbox to decry the dismal state of Microsoft documentation.  Back in the day (ca. VS 2005) you could select a class or a method in your code, or a property in the Property window, press F1, and be magically transported to reasonably complete documentation on the subject.  Nowadays, it seems that F1 help is worse than useless because it forces me to stare at either a meaningless stub of documentation (if I'm that fortunate) or, worse, some totally irrelevant high-level blurb whose relationship to the thing I've clicked on in my code is totally opaque.

So, what are my other options?  The tutorials provided by the Azure and ASP.NET websites are ok as far as they go, but they provide FEW OR NO EXPLANATIONS for why they do what they do, leaving the student to "read between the lines" to figure out what's going on in the tutorial.  I wasted the better part of a week because the second tutorial on the Azure website tells me to create an MVC 3 project and then to install the ASP.NET Universal Providers.  No explanation why.  No warning that, hey, MVC 4 has been out for a while now, but if you decide to use it, you should not install the Universal Providers.

Forums like this are invaluable, but gleaning scraps of knowledge one question at a time is a really tough way to learn a subject.  And you need to know enough to ask the right questions...

There are a lot of good books on the fundamentals of the technology.  I invested in a Safari Books Online subscription, and I use it daily.  But with the pace of product changes, the books are obsolete before they are published.

It seems that the best, most up-to-date information I've stumbled across has been in the form of blogs published by Microsoft insiders.  But I still don't know how to find them short of random Google searches or answers posted in forums like this.  And who has time to actually follow some MS Guru's blog in the hopes that it might contain a posting germain to a topic I'm interested in?

Sigh...