oData and Stored Procedures aka Service Operations

If you work with WCF Data Services, at some point, you will probably need to call a stored procedure to return result of a complex query. If you wonder how to wrap a call to stored procedure into a WCF Data Service call read on.

The quick pointer is here and below there is a summary of the required steps:

1. You need to create a service method decorated with “WebGet” attribute. This method needs to return IQueryable<yourEntity>.
2. Inside this method you will call a stored procedure, usually wrapped in Entity Framework Function Import Call, returning <yourEntity>.
3. If you use a stored procedure to return entities then, very likely, you are optimizing for performance. If returned entities have related child entities that you also want to return, you have two options:
   1. Do nothing and let WCF Data Services handle that. You will be surprised how smart the oData is .
   2. You are returning a big result set and your goal is to optimize the performance (you really do not want to lazy load 30k child entities). In such a case, execute another Function Import call and load your child entities. Once again, you will be surprised how smart the oData is . Just make sure that you disable Lazy Loading on the Context object.
4. Now you can go back to your browser or to your .NET client and execute something like this:  http://localhost/my.svc/CallMySp?searchTerm=’my term’&$expand=MyChildEntities. As I mentioned before .. you will be surprised how nicely it works.

Example service method returning entities from stored procedure:

[WebGet]
       public IQueryable<CalendarEvent> FindEventsForSearchCriteria(
           string searchTerm, int maxToReturn, string customerIds)
       {
           CurrentDataSource.ContextOptions.LazyLoadingEnabled = false;
           if (String.IsNullOrEmpty(searchTerm) || maxToReturn <= 0)
               return new List<CalendarEvent>().AsQueryable();
           if (searchTerm.Length > 98 ) searchTerm = searchTerm.Substring(0, 98);
           searchTerm = "%" + searchTerm + "%";

           var result = CurrentDataSource
               .FindEventBySearchTerm(searchTerm, customerIds, ListSeparator)
               .OrderByDescending(ce => ce.StartUtc)
               .Take(maxToReturn).ToList();

           //get child itineraries
           if (result.Count > 0)
           {
               var itineraries =
                   CurrentDataSource.FindItineraryByEventList(
                       result.Select(ev => ev.CalendarEventId.ToString()).ToList()
                           .Aggregate((accumulator, b)
                               => accumulator + ListSeparator + b), ListSeparator)
                               .ToList();
               if (itineraries.Count() > 0)
               {
                   foreach (var eventEntity in result)
                   {
                       var allChildren = itineraries
                           .Where(it => it.CalendarEventId == eventEntity.CalendarEventId)
                           .ToList();
                       allChildren.ForEach(
                           it=> eventEntity.Itineraries.Add(it));
                   }
               }
           }
           return result.AsQueryable();
       } 

Example client code:

var customersGateway = new EntitiesServiceGateway();
           var customers = customersGateway.GetCustomers();
           var matchingCustomers = customers
               .Where(c => c.Name.IndexOf(searchTerm) > 0)
               .Take(100).Select(c=> c.Id.ToString()).ToList();

           var customerIds = matchingCustomers.Count > 0 ?
               matchingCustomers.Aggregate((accumulator, b) => accumulator + ListSeparator + b)
               : string.Empty;

           var requestUri = new Uri(
               string.Format(
               "{0}/FindEventsForSearchCriteria?searchTerm='{1}'&maxToReturn={2}&customerIds='{3}'&$expand=EventItineraries",
                   _context.BaseUri, HttpUtility.UrlEncode(searchTerm)
                   , Constants.EventsSearchResultLimit, customerIds)
                   , UriKind.RelativeOrAbsolute);

           return _context.Execute<CalendarEvent>(requestUri).ToList();