AT.Data.DbContextExtensions

Entity Framework Multiple Result Sets Using AT.Data.DbContextExtensions

Using the Entity Framework is a great way to retrieve data. However, not all complex operations are built into the framework. Not being able to return multiple result sets in a single database trip is one that I hear about the most. The extension method MultipleResultSet in AT.Data.DbContextExtensions extend the framework to allow you to do just that. For instance, currently if I want to retrieve two result sets from the Entity Framework, I need to execute two IQueryable statements as such:

 using (TestModelEntities context = new TestModelEntities())
 {
    int age = 17;
    int horsePower = 140;
    
    IQueryable<Person> peopleQuery = from p in context.People
                                     where p.Age > age
                                     select p;
    
    IQueryable<Car> carQuery = from c in context.Cars
                               where c.HorsePower > horsePower
                               select c;

    ...
 }

As we all know, as soon as you try to use a value inside of either of the above IQueryable entities (or call something like .ToList on it) the queries are each immediately evaluated, resulting in two round trips to the database.

Enter the MultipleResultSet extension method. This method extends the DbContext class and allows you to pass in multiple IQueryable statements that have not yet been evaluated, traverses there Expression trees, build a multiple result set SQL statement (that is still parameterized) and return all of your results. One way to convert the above example into a multiple result set query is as follows:

 using (TestModelEntities context = new TestModelEntities())
 {
    int age = 17;
    int horsePower = 140;
    
    MultipleResultQuery<Person> peopleQuery = (from p in context.People
                                               where p.Age > age
                                               select p).AsMultipleResultQuery();
    
    MultipleResultQuery<Car> carQuery = (from c in context.Cars
                                         where c.HorsePower > horsePower
                                         select c).AsMultipleResultQuery();

    context.MultipleResultSet(peopleQuery, carQuery);

    ...
 }

MultipleResultSet executes the database call immediately, and it maps each query's results as full fledged entities. You can then retrieve the cached results from a parameter on the original query:

 IEnumerable<Person> people = peopleQuery.Results;
 IEnumerable<Car> cars = carQuery.Results;

The MultipleResultSet method works for N number of multiple result queries, and has an intuitive way of knowing what results are related to which query.