If you have used the Entity Framework that shipped with Visual Studio 2008 SP1, you would really start appreciating the flexibility it offers for building schema driven data access layer and get it to the UI Layer either directly or using a middle tier such as WCF RIA Service. Check my earlier post on this, if you are interested further :)
Meanwhile, the other exciting stuff that has been around is the SQL Azure which is part of the Windows Azure platform. SQL Azure provides relational data over the web which means, the Database is hosted, maintained and all is done by us and you get to store your database and query the same as if you were running it in your local Data Center or server. Of course, SQL Azure is currently CTP and you can get free access to it if you have the Azure Tokens.
While I had earlier written about Migrating your database to SQL Azure that example used an ASP.NET front end which had a GridView doing direct data binding with SQL DataSource. Obviously, one would want to use some of the more abstract controls such as LINQ DataSource / Entity DataSource.
The trick however, here is that, when you create an Entity DataSource from your local database, the Entity Modelling Designer gets access to pull all the required information and build a nice schema with meta data. However, if you try to bind your Entity Model to a SQL Azure tool, then you wouldn’t get this flexibility since SQL Azure doesn’t provide support for the same currently.
Kevin Hoffman provides a nice work around in his post which I want to repeat here for the sake of continuity in the post. If you would like to generate an Entity Model out of your SQL Azure Database, have a local copy of the database running with the same schema. The trick is to first point your Entity Designer tool to your local database and allow it to pull all the required information from here and build the entity model. Thereafter, you can just visit the SQL Azure Portal at https://sql.azure.com and pick up the identical database’ connection string to be copied to your web.config file to replace the local connection string (Read my earlier post for a steps).
Now, the Entity Framework connection string is a little complicated with a lot of settings. The connection string path is provided as a property within the main connection string. For example a typical Entity Framework connection string in the web.config file looks as below:-
connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="CONNECTION STRING;Encrypt=True;;MultipleActiveResultSets=False"" providerName="System.Data.EntityClient" /></connectionStrings>
In the above, as you can see, the actual connection string starts from provider connection string setting.
You need to replace that portion with the Connection string copied from the SQL Azure Portal for your Database. Make sure you don’t mess up with the $quot and other settings by mistake.
Secondly, if you are running Visual Studio 2010 Beta 2 and trying to accomplish this, in the copied connection string, you need to change the User Id part to USERNAME@SERVERNAME (read my previous post on this for more information) . Also, the default password that is copied from the SQL Azure portal is “mypassword” which you have to change to your actual password.
Finally you also need to set the MultipleActiveResultSets to False since SQL Azure doesn’t support the same currently. Otherwise, you will get an error.
Once you have taken care of all the steps above, your page works seamlessly as it did while binding the entity framework to your local database (provided you have an identical database to one that is running on SQL Azure)
I have highlighted the important portions in the connection string so that you can take care of those when binding.