Working with Entity Framework v4 and SQL Azure

This post is the first in a series of posts on this topic. 
The following is a summary of the various parts.

Introduction

Well, it’s been “out there” for a while, and now it is time I gave you a taste of how to combine some of the “Next Generation” data access technologies, today.  SQL Azure is Microsoft’s “Database in the Cloud” and represents a complete makeover (replacement) of the first attempt, SQL Data Services (SDS).  SDS is no longer, SQL Azure is the future.

One of the biggest knocks on SDS was the lack of a relational database.  Everything was done according to vertical data “silos” and cross-silo querying was painful (and almost impossible).  Unfortunately, administration was also tricky (well, worse) and it veered quite severely from the established SQL world which we know and love (or submit to).

Now, I’m going to walk you through the process of establishing a working Entity Framework (v4) model which runs against a SQL Azure database – a database in the “Cloud”.  This is not, by any means, a definitive guide.  In fact, I only just got this working myself this morning.  It relies on a few creative “workarounds”, which I will explain in due course.

This example is only intended as a “Proof of Concept” and has not exhaustively been tested. 
Attempt at your own peril (usual conditions and waivers apply)!

Prerequisites

What you will need prior to following along:

• A SQLAzure token or invitation and a valid Live ID (or refer to the article Getting a SQL Azure Database),
• Visual Studio 2010 Beta 2, and,
• SQL Management Studio 2008 R2 Beta (note you only need SSMS to follow along)

All of the above can be obtained for free, from the links above.  You may have to wait for a SQL Azure invitation or token.  My tokens were obtained a little while ago.
I’ll consider writing a separate article on how to set up SQL Azure, so if you are interested send me an email or leave a comment here.

Starting Off

This article assumes you are familiar with Visual Studio basics.

Once all the tools are installed, and you have configured SQL Azure for the first time (this involves setting up a master user account and password) log into the Administration console and create a database – just click “Create Database” and give it a name of “Sample”.

You can now get connection strings by clicking on the “Connection Strings” button.  Your server name is the key thing here.  Make a note of it.

Next, you may need to add your IP address to the Firewall settings.  To do this, click on the “FirewallSettings” tab, and add a new Rule using your IP address (your IP address is listed on the screen).

Now, fire up SQL Management Studio R2 (Beta).  On the initial dialog, enter the full name of your SQL Azure database, e.g. [server].database.windows.net and for the Username, use the one you configured initially (use SQL Authentication, not Windows Authentication), in the form of [user]@[servername] and, of course, your password, which you used to configure SQL Azure initially.

Now, you should be able to successfully connect to SQL Azure from SQL Management Studio.  If you can not connect, recheck your config values, it’s really just servername, username@servername and the correct password.  You may also need to add a firewall rule to allow your IP address to SQL Azure (see above).

Ensure you are using SQL Management Studio 2008 R2 Beta (November CTP) – earlier versions are not supported, so this will not work with the Server 2008 RTM version or prior.

Creating a Test Database

Please note: The following parts in this post introduce you to concepts which will be used in later articles.  You may safely skip the rest of this post if you are only interested in establishing the application and data model for the project.  Otherwise, keep reading and you’ll get a full understanding of how we intend to map a SQL Azure database to a working Entity Framework model.

Everything here is done via T-SQL script (unfortunately) so to create a new table we’ll have to create it.  Here’s a sample T-SQL script you can use.  We’ll create a table called “DailyStats” which has an Identity PK, a text field and a date field.

— =========================================
— Create table template SQL Azure Database
— =========================================

IF OBJECT_ID(‘DailyStats’, ‘U’) IS NOT NULL
  DROP TABLE DailyStats
GO

CREATE TABLE DailyStats
(
    StatId int IDENTITY NOT NULL,
    Data nvarchar(150) NULL,
    Created datetime
NULL
    CONSTRAINT PK_sample_table PRIMARY KEY (StatId)
)
GO

Next, we can populate the table with a row of test data, so we can do some simple selects later.

INSERT INTO [Sample].[dbo].[DailyStats]
           ([Data]
           ,[Created])
     VALUES
           (‘Blah Blah Blah’,
           GETDATE())
GO

Now, we’re pretty much done with SQL Azure for the time being.  Close SQL Management Studio, and fire up Visual Studio 2010 (Beta 2).

Creating the Entity Model

Create a new Console Application.  Once you are ready, in the Solution Explorer, add an “App.Config” (right click under the project in Solution Explorer, “Add” –> “New Item”, “Application Configuration File”) file to your solution.  Once you have done so, open it in the editor and add the following:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="SampleEntities"         connectionString="metadata=res://*/SqlAzureModel.csdl|res://*/SqlAzureModel.ssdl|res://*/SqlAzureModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=[servername].database.windows.net;Database=Sample;Uid=[user]@[servername];Pwd=[password];&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Make sure you change the items in bold to their correct values.  For the .csdl, .ssdl and .msl entries (the first three values in bold, above after the name value), use the name of the Model (file name) you are planning to use, e.g. I am using the name “SqlAzureModel.edmx”.

Now, in Solution Explorer, right click and “Add” –> “Add New Item”, from the Tree, select “Data” and select “ADO.NET Entity Data Model”.  If you can’t find this, make sure your project is a .Net Framework 4.0 Project!  This can be found in the project’s “Application” tab, under “Target framework”.

Now, just create an Empty Data Model.  You can specify your own name for the model, but you will need to edit the app.config.  Once you have added your new Model, expand it in the Solution Explorer and double click the associated “.Designer.” file.

Make note of the value of the Constructor in the Contexts region (mine is as follows):

public SampleEntities() : base("name=SampleEntities", "SampleEntities")
{
    this.ContextOptions.LazyLoadingEnabled = true;
    OnContextCreated();
}

You need to ensure your ConnectionString in the App.Config has the same name as the text in bold (whatever your Context is called).  This is the “hack” workaround.

Now, assuming this is all configured properly, you can double click the .edmx file and you should see an empty model.  Right click on the model and select “Update Model from Database…”.

Assuming you have configured the connection string properly, you should see the following dialog (note we can see the table we created in SSMS, earlier).  If not, just ensure that the key in the App.Config file matches the Connection String expected by the .Designer. file (see above).

Once you see the above dialog, ensure you have selected the table, and we can update the model.

Once created and saved, open the Program.cs (or vb) file and add the following code:

static void Main(string[] args)
{
    using (SampleEntities e = new SampleEntities())
    {
        Console.Write(e.DailyStats.Count());
        foreach (DailyStat stat in e.DailyStats)
        {
            Console.Write(stat.Data);
        }
    }
}

You should be able to execute this code now, and it should print the value (1)  representing the one row in the DailyStats table and the value (blah blah blah) which is the value found in the single row under the “Data” column.

I do realise this is a very basic example, but it does introduce you to a number of fundamental ways to interact with your SQL Azure database.  You have no successfully used SQL Management Studio 2008 R2 and Visual Studio 2010 (Beta 2) to contact SQL Azure.  To top it all off, we have (hopefully) managed to use the Entity Framework to query real dat
a in the database.

This is an important first step.  In the next article we will delve deeper into the supported functionality.  For now, it may help to read ahead on the limitations of SQL Azure:

MSDN: Guidelines and Limitations (SQL Azure)

That’s it for now – you have now successfully (hopefully) connected to your SQL Azure database using the Entity Framework v4.  Check back for my next entry on this topic soon.