Windows Azure Technical Forum Support Team Blog

Blog hosted by Microsoft Windows Azure Tech Forum Support Team, provides tech support of Windows Azure, SQL Azure, Appfabric…

Azure + Bing Maps: Accessing spatial data with Entity Framework

Azure + Bing Maps: Accessing spatial data with Entity Framework

Rate This
  • Comments 1

This is the fifth article in our "Bring the clouds together: Azure + Bing Maps" series. You can find a preview of live demonstration on http://sqlazurebingmap.cloudapp.net/. For a list of articles in the series, please refer to http://blogs.msdn.com/b/windows-azure-support/archive/2010/08/11/bring-the-clouds-together-azure-bing-maps.aspx.

Introduction

In our previous post, we introduced how to work with spatial data in SQL Azure. This post will focus on how to access spatial data in a .NET application, and a few considerations you must take when working with SQL Azure.

Before reading, we assume you have a basic understanding of the following technologies:

  • ADO.NET Entity Framework (EF). If you're new to EF, please refer to the MSDN tutorial to get started. You can also find a bunch of getting started video tutorials on the Silverlight web site, such as this one. This post targets users who know how to work with Visual Studio tools to generate models, but may not understand more advanced topics such as working with stored procedures.
  • Spatial data. If you don't understand spatial data, please read the previous post.
  • A SQL Azure account if you want to use SQL Azure as the data store.

Entity Framework and SQL Azure

Today, the simplest solution to access a database from a .NET application is using ADO.NET Entity Framework (EF). EF is able to generate an object model automatically from the relational database model. Thus it allows you to work with objects in an object oriented programming language (such as C#), and use LINQ to query the objects, rather than dealing with SQL connections and commands manually. EF increases developers productivity tremendously, compared to traditional data accessing frameworks like ADO.NET DataSet. EF is also a built-in provider for WCF Data Services.

Entity Framework is fully supported in SQL Azure. If you're using Visual Studio 2010, you can generate the object model from a SQL Azure database just like generating from a SQL Server database.

Unfortunately, at the moment EF only supports relational data. It doesn't support spatial data, be it in SQL Azure or a local SQL Server.

Make Entity Framework work with spatial data

There're several workarounds to make Entity Framework work with spatial data. A common approach is described in Julie's post. She creates a view which converts the geography type to a varbinary(max) type. Recall SQL Azure stores geography data in its binary format. So this conversion is straightforward. Entity Framework supports binary data type, so now we can create an object model that maps the binary CLR type to the geography SQL type.

We will take the same approach here. Let's create a view:

CREATE VIEW [dbo].[TravelView]

AS

SELECT     PartitionKey, RowKey, Place, CAST(GeoLocation AS varbinary(MAX)) AS GeoLocation, Time

FROM         dbo.Travel

Then we will be able to generate an Entity Framework model. As you can see from the below screenshot, the GeoLocation property has been correctly included in the model. Its type is Binary. Also note with Visual Studio 2010, you can generate EF models from SQL Azure, just like generating models from local SQL Server.

Add custom properties

Keep in mind EF represents the data access layer. Very often data access objects are not exactly the same as business objects. Usually, you need to create a business object class, and then convert the EF object to the business object. However, if your business logic is simple (like the travel application we're building), you can also simply add custom properties to the EF class.

To do so, you need to create a partial class. In this case, let's create a partial class for the Travel class.

    [DataServiceKey(new string[] { "PartitionKey", "RowKey" })]

    [IgnoreProperties(new string[] { "EntityState", "EntityKey", "GeoLocation" })]

    public partial class Travel : EntityObject

The DataServiceKey and IgnoreProperties attributes are used by WCF Data Services, which will be covered in the next post. For now, we add three custom properties to the class: GeoLocationText, Latitude, and Longitude. Even though we store the data as spatial data, our clients may still think latitude and longitude is easier to understand. So we expose those two properties, as well as the WKT representation of the spatial data (GeoLocationText).

Those properties are not automatically populated by EF when querying the database. We need to populate them manually. To do so, implement the partial method OnGeoLocationChanging, which will be invoked whenever the value of GeoLocation property changes. The GeoLocation property is the binary representation of the spatial data, which corresponds to the database view's GeoLocation column.

        partial void OnGeoLocationChanging(global::System.Byte[] value)

        {

            if (value != null)

            {

                using (MemoryStream ms = new MemoryStream(value))

                {

                    using (BinaryReader reader = new BinaryReader(ms))

                    {

                        SqlGeography sqlGeography = new SqlGeography();

                        sqlGeography.Read(reader);

                        this.GeoLocationText = new string(sqlGeography.STAsText().Value);

                        this.Latitude = sqlGeography.Lat.Value;

                        this.Longitude = sqlGeography.Long.Value;

                    }

                }

            }

        }

By the way, if you're not familiar with partial class and partial method, please read http://msdn.microsoft.com/en-us/library/wa80x488.aspx.

Using stored procedures with Entity Framework

As described in the last post, when working with spatial data, it is recommended to handle insert/update/delete operations using stored procedures. To work with stored procedures in EF, first you need to import them to the model. This task is easy. Simply right click "Function Imports" in the Model Browser, and choose "Add Function Import". Then follow the wizard:

Import stored procedures

Entity Framework supports two kinds of stored procedures: CRUD stored procedures and general stored procedures. For CRUD stored procedures, you need to map them to the Insert/Update/Delete Functions in the "Mapping Details" window. Then they will be invoked automatically when the default implementation of SaveChanges is called.

Map Function

For general stored procedures, you don't need to map them. But you need to invoke them manually, just as invoke a normal method on the EF object. Please refer to http://msdn.microsoft.com/en-us/library/bb896279.aspx for more information.

Note while the above screenshot shows how to map a stored procedure to CRUD functions, our sample actually does not use this feature. The CRUD stored procedures have a limitation: It cannot use custom properties like GeoLocationText. All parameters must be properties defined in the model's conceptual models. That also means they must appear in the storage model (that is, must be actual columns in the database), because all properties in the conceptual model must be mapped to properties in the storage model. Well, if you don't know what is conceptual model and storage model, just keep in mind that CRUD stored procedures have to use properties you defined in the EF designer. Custom properties added in the partial class cannot be used. You can read more from http://msdn.microsoft.com/en-us/library/bb399232.aspx.

Override SaveChanges

Since we have to pass our custom property GeoLocationText to the stored procedures, we have to treat them as general stored procedures, and manually invoke the methods. The code should be written in, of course, the ObjectContext's SaveChanges method:

        public override int SaveChanges(SaveOptions options)

        {

            int returnValue = 0;

            var EnsureConnectionMethod = typeof(ObjectContext).GetMethod("EnsureConnection", BindingFlags.Instance | BindingFlags.NonPublic);

            EnsureConnectionMethod.Invoke(this, null);

            foreach (ObjectStateEntry ose in this.ObjectStateManager.GetObjectStateEntries(EntityState.Added))

            {

                Travel travel = ose.Entity as Travel;

                if (travel != null)

                {

                    RetryPolicy retryPolicy = new RetryPolicy();

                    retryPolicy.Task = new Action(() =>

                    {

                        this.InsertIntoTravel(travel.PartitionKey, travel.Place, travel.GeoLocationText, travel.Time);

                    });

                    retryPolicy.Execute();

                    returnValue++;

                }

            }

            foreach (ObjectStateEntry ose in this.ObjectStateManager.GetObjectStateEntries(EntityState.Modified))

            {

                Travel travel = ose.Entity as Travel;

                if (travel != null)

                {

                    RetryPolicy retryPolicy = new RetryPolicy();

                    retryPolicy.Task = new Action(() =>

                    {

                        this.UpdateTravel(travel.PartitionKey, travel.RowKey, travel.Place, travel.GeoLocationText, travel.Time);

                    });

                    retryPolicy.Execute();

                    returnValue++;

                }

            }

            foreach (ObjectStateEntry ose in this.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted))

            {

                Travel travel = ose.Entity as Travel;

                if (travel != null)

                {

                    RetryPolicy retryPolicy = new RetryPolicy();

                    retryPolicy.Task = new Action(() =>

                    {

                        this.DeleteFromTravel(travel.PartitionKey, travel.RowKey);

                    });

                    retryPolicy.Execute();

                    returnValue++;

                }

            }

            var ReleaseConnectionMethod = typeof(ObjectContext).GetMethod("ReleaseConnection", BindingFlags.Instance | BindingFlags.NonPublic);

            ReleaseConnectionMethod.Invoke(this, null);

            return returnValue;

        }

Ignore the connection management and retry policy for now. When writing custom logic for SaveChanges, you can take advantage of the ObjectStateManager.GetObjectStateEntries method. Entity Framework keeps change tracking automatically, and the GetObjectStateEntries method allows you to obtain all added entities, all modified entities, as well as all deleted entities. Thus you can invoke the corresponding CRUD methods.

The return value of the SaveChanges method is defined as how many rows are affected. While many applications (including our sample) do not rely on this feature, it is recommended to implement the return value as it's defined, to avoid confusion.

Finally, note we do not invoke base.SaveChanges, to avoid the pre-defined logic that tries to modify the database using generated SQL rather than stored procedures.

Add a retry logic

So far, everything we described apply to both SQL Azure and local SQL Server. This is good, since the code works fine both locally and in the cloud. However, in SQL Azure, an operation is not guaranteed to always success. It may fail due to unexpected reasons, such as network issues. But our SLA ensures if you try the same operation again after 10 seconds, it should work fine. Actually in most cases, if a connection problem occurs, wait 5 seconds and retry should be more than enough. Note the SLA only covers service issues. If the problem is in your application (such as forgetting to close connections), there's no SLA.

Anyway, it is very important to write a proper retry logic when working with any storage service, especially cloud storage like SQL Azure. Fortunately, this is a simple task. Please refer to the following code:

    public class RetryPolicy

    {

        public int RetryNumber { get; set; }

        public TimeSpan WaitTime { get; set; }

        public Action Task { get; set; }

 

        public RetryPolicy()

        {

            this.RetryNumber = 3;

        }

 

        public RetryPolicy(int retryNumber)

        {

            this.RetryNumber = retryNumber;

            this.WaitTime = TimeSpan.FromSeconds(5d);

        }

 

        public void Execute()

        {

            for (int i = 0; i < this.RetryNumber; i++)

            {

                try

                {

                    this.Task();

                    break;

                }

                catch (SqlException ex)

                {

                    if (i == this.RetryNumber - 1)

                    {

                        throw new SqlExceptionWithRetry(

                            "Maximum retry reached. Still unable to process the request. See inner exception for more details.",

                            ex);

                        throw ex;

                    }

                    Thread.Sleep(this.WaitTime);

                }

            }

        }

    }

 

    public class SqlExceptionWithRetry : Exception

    {

        public SqlExceptionWithRetry(string message, SqlException innerException)

            : base(message, innerException)

        {

        }

    }

Here we have a property whose type is Action. This is actually a delegate which performs the actual logic. For those who's not familiar with the Action delegate, please refer to http://msdn.microsoft.com/en-us/library/018hxwa8.aspx.

We try to execute the action. If it succeeds, then break the loop. If it fails, we wait for a few seconds and try again, until the maximum retry number is reached. Usually the maximum retry number should not be reached. If you do reach it, either your application has a bug which results in an uncover able problem, or the SQL Azure service is down and you can submit a ticket to see if you can get refund.

To use the retry logic, simply write:

RetryPolicy retryPolicy = new RetryPolicy();

retryPolicy.Task = new Action(() =>

{

   this.InsertIntoTravel(travel.PartitionKey, travel.Place, travel.GeoLocationText, travel.Time);

Additional considerations

Before we close this post, we'd like to point out two additional considerations when working with SQL Azure.

First, it is recommended to enable MARS by setting MultipleActiveResultSets to True in the connection string. This enables one connection to work on multiple result sets, which is required by certain features of EF. Please refer to http://blogs.msdn.com/b/adonet/archive/2010/06/09/remember-to-re-enable-mars-in-your-sql-azure-based-ef-apps.aspx for more information.

Second, in the above SaveChanges code, since we're not invoking base.SaveChanges, the connections will not be taken care of automatically. We must make sure we open and close connections at proper time, to avoid service throttling. EF internally uses the EnsureConnection and ReleaseConnection methods, which takes care of open/close connections, as well a few others. So it is a good idea to leverage those built-in methods. Unfortunately they're marked as internal, which cannot be accessed directly in our own application. So we have to use reflection to invoke them.

Conclusion

This post discussed how to access spatial data using Entity Framework, as well as a few considerations you must take when working with SQL Azure. The next post will discuss how to expose the data to the world using WCF Data Services.

 

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • There's an error in the VB distribution of this series for this chapter (5 that is).

    The RetryPolicy-class has an "Exit Try" in the Execute-method, which should be replace by an "Exit For". The "Exit Try" causes an System.Data.EntityCommandException, because the application tries to insert the same data RetryNumber-times into the database.

    The C#-distribution has a "break;" at that part, which is correct. Exit Try is not the correct equivalent for that though.

Page 1 of 1 (1 items)