EF, SQL Federations and returning IQueryable<T> and IDisposable

Category: azure sqlazure


benaw on Fri, 24 Feb 2012 14:08:24

public IQueryable<Product> GetProducts(Guid tenantId)
      var useFederationCommand = this.GetUseFederationStatement(TenantFederation.FederationName, TenantFederation.DistributionName, tenantId, true);            
	      var context = new Repository(this.connectionString);
      context.Configuration.ProxyCreationEnabled = false;

      return context.Products;

The new ASP.NET Web API allows you to expose over HTTP IQueryable<T> to enable query parameter or odata syntax to be used to map to a subset small set of expressions, top, skip orderby for example.  In all the examples for sql federations the context is wrapped in a using statement to dispose immediately, 1,2 above. If one wants to keep it live for a little longer, to enable an iquerable to be returned and let the runtime do the disposal would there be any issue or is there another place it could be disposed of explicitly? (btw this functionality is what I want and working, could it be better to effect a disposal in code after the exucution has occured?)



Sandrino Di Mattia on Fri, 24 Feb 2012 14:44:53

Hi benaw,

This question has actually nothing to do with SQL Azure, but is more related to Entity Framework and/or the ASP.NET Web API. Seen the ASP.NET Web API works with controllers (and thus supports ActionFilters) you could evaluate the following solution:


First you can create an ActionFilter that creates the context before the action is executed and stores it in the current HttpContext. Once the action and result have executed, you could clean up your context.

public class ManageDbContextAttribute : ActionFilterAttribute
        public override void OnActionExecuting(ActionExecutingContext filterContext)
            var useFederationCommand = this.GetUseFederationStatement(TenantFederation.FederationName, TenantFederation.DistributionName, new Guid(filterContext.ActionParameters["tenantId"]), true);

            var context = new MyEntities(this.connectionString);
            context.Configuration.ProxyCreationEnabled = false;

            ControllerContext.RequestContext.HttpContext.Items.Add("MyContext", context);


        public override void OnResultExecuted(ResultExecutedContext filterContext)
            if (filterContext.HttpContext.Items.Contains("MyContext"))
                var context = filterContext.HttpContext.Items["MyContext"] as IDisposable;
context.Dispose(); } base.OnResultExecuted(filterContext); } }

Your action:

Then on your action you simply add the ManageDbContext attribute:

        public IQueryable<City> GetCities(Guid tenantId)
            MyEntities context = ControllerContext.RequestContext.HttpContext.Items["MyContext"] as MyEntities;
            return context.Cities;

Note that I didn't test this code and it's not production ready. But it might get you on your way.


benaw on Fri, 24 Feb 2012 15:05:18

Sandrino, I appreciate your time to think about an idea for interfacing to SQL Azure federations from ASP.NET Web API.

Cheers ben.