Partager via

SQL Azure Federations and the Entity Framework

  • Article

 

The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For using SQL Azure with EF see https://msdn.com/data/jj556244

 

The recent SQL Azure Q4 2011 Service Release includes the new feature SQL Azure Federations which enables greater scalability and performance from the database tier of your application through horizontal partitioning. One or more tables within a database are split by row and partitioned across multiple databases (Federation members). This type of horizontal partitioning is often referred to as ‘sharding’. Detailed information on the SQL Azure Federations feature is available here.

The current release of Entity Framework can be used to work with SQL Azure Federations, however a federated database cannot be created by the Entity Framework. Our Customer Advisory Team has started a series of blog posts with the goal of providing guidance around common scenarios and issues that arise when using the Entity Framework with SQL Azure Federations.

The first blog post in this series, SQL Azure Federations with Entity Framework Code-First, is a great getting started guide. It explains the correct procedure to submit the USE FEDERATION statement before sending queries to the database via the Entity Framework (query execution or update operations).

The next post, Understanding SQL Azure Federations No-MARS Support and Entity Framework, explains the impact of the lack of support for MARS on Entity Framework applications.

We are working with the Customer Advisory Team to continue adding posts to this series. While these blog posts provide concrete scenarios detailed walkthroughs and code samples, here are some general guidelines/considerations:

  • The Entity Framework based application needs to be aware and manage the access to the different federation members. What this means is that the application would have to explicitly open the store connection with which the context is associated and issue the “USE FEDERATION” statement to connect to the correct federation member before interacting with the database via the Entity Framework.
  • Any needed database transaction would have to be started after the “USE FEDERATION” statement is issued. This is because federated databases do not support the “USE FEDERATION” statement in a transaction.
  • Any connection retries would also need to be handled by the application.
  • Instances of the context class should not span across federation members. In general, this also means that all changes managed by the context should be associated with a single federation member. This is because at the time SaveChanges is invoked, it would issue the corresponding database data modification operations only to the federation member to which the associated store connection currently points.

In the future, our plan is to provide more integrated support for SQL Azure Federations. We would love to hear your experiences on using the Entity Framework with federated databases as well as your suggestions on how we can improve it.

ADO.NET Entity Framework Team

Comments

  • Anonymous
    January 11, 2012
       Happy New Year To Everyone !    Thank you for information sharing, it useful to me.

  • Anonymous
    March 27, 2012
    I've found that implementing a retry policy for transient database failures using these two technologies to be a challenge, and was simply wondering if there is any news on the horizon about further development being done to bring these two technologies together.  According to a comment in a blog post by "Valery M" from Microsoft in August 2011, I was expecting to hear more about this - but I can't seem to get in touch with her about this.  Do you have any more information about what's on the horizon? Ideally, the Entity Framework would automatically implement a retry policy under the hood when connecting to SQL Azure, and we'd have some way to play with the configuration settings. As I currently understand it, this is an example of the retry policy for SQL Azure / EF, and as you can see, it's quite cumbersome with the delegates and casting: var c1 = policy.ExecuteAction<object>(() =>              (from x in dc.Customers               where x.CustomerID == cId               select new { x.CustomerID, x.FirstName, x.LastName }).First());var anon = CastHelper.Cast(c1, new { CustomerID = -1, FirstName = "", LastName = "" }); Thank you very much, Matt