SharePoint 2013 AutoHosted :Using Entity Framework To Connect to Embedded SQL Azure Database

SharePoint 2013 AutoHosted :Using Entity Framework To Connect to Embedded SQL Azure Database

On SQL

To create an Autohosted SharePoint 2013 Application contains SQL azure Database is fairly straight forward:

• Add Database Project to your Solution

• Create your tables and build your database project
• you will notice that a file of type .dacpac is generated 

• To notify the autohosted SharePoint 2013 app you simply set the  path of the generated .dacpac as the value of the SQL Database property of the SharePoint App project

• The trick part is to add the connection string to a specific appSetting named SqlAzureConnectionString  this limitation due to upon application deployment the SharePoint deployed the database and updates the connection string stored at the SqlAzureConnectionString with the correct production value 

All the above was very straight forward , but what to do if i needed to use Entityframework  to communicate with the SQL Azure database

• Add new ADO.NET entity Model

• Notice that the Model Connection string been added to your web.config 

unfortunately if you work locally everything will work perfectly but when you deploy this application to your SharePoint developer site you will face Error 

• Changing the default constructor of the generated DbContext class to take a string parameter 
• Pass the correct connection string after read it from the appSetting SqlAzureConnectionString  and add the appropriate meta data as below :

            
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
entityBuilder.Provider = "System.Data.SqlClient";
entityBuilder.ProviderConnectionString = System.Configuration.ConfigurationManager.AppSettings["SqlAzureConnectionString"];
entityBuilder.Metadata = @"res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl";
return entityBuilder.ToString();

after publishing for the second time now the application will work like a charm !