How to Map a Stored Procedure with Multiple Result Sets to a Function Import in Entity Framework

Function imports are a pretty cool way of implementing stored procedures in Entity Framework. Most of the time it is a very straight forward process of adding the stored procedure to your model, creating the function import, getting column information, and building a new complex type. However, sometimes you may not be in control of the stored procedures you are mapping and your Database Administrator (DBA) might decide to do something crazy, like returning multiple result sets.

Entity Framework does not handle multiple result sets natively. The easiest way to get around this is to get your DBA to write two stored procedures, one for each result set. This would make it simple to map a couple complex types to your function imports. However it's not always possible to convince your DBA to modify a perfectly good and working stored procedure. Another option is to have a look at Entity Framework Extensions. I have not tested this out but it would seem that this extension library adds support for multiple result sets. It might be worth a look. It also seems to contain a lot of other goodies one could take advantage of.

This post is mostly for my own reference, but after I give this library a try I will definitely come back here and update with my experience.