StoredProcedures with multiple result sets and RIA Services

Here is one way we could work with StoredProcedures that return multiple results when using WCF RIA Services. I grabbed the code for EFExtensions and  was working with a model from MusicStoreSample

I created a simple procedure, which returns the artist details for the given Id and all the Albums for the Artist

create procedure [dbo].[GetArtistDetails](@artistId int) as

select * from Artist where ArtistId=@artistId                    
select * from album where ArtistId=@artistId                    
I extended the generated Entities class in the web project to add a method (followed the Sample in the  EFExtensions sample project).

public partial class MusicStoreEntities : ObjectContext
    {
        private static readonly Materializer<Artist> s_artistMaterializer = new Materializer<Artist>();
        private static readonly Materializer<Album> s_albumMaterializer = new Materializer<Album>();
        public Artist GetArtistAndRelatedAlbums(int artistId)
        {
            DbCommand command = this.CreateStoreCommand(“GetArtistDetails”, CommandType.StoredProcedure, new SqlParameter(“artistId”, artistId));
            Artist artist;

            using (command.Connection.CreateConnectionScope())
            using (DbDataReader reader = command.ExecuteReader())
            {
                // first result set includes the category
                artist = s_artistMaterializer
                    .Materialize(reader)
                    .Bind(this.Artists)
                    .SingleOrDefault();

                // second result set includes the related products
                if (null != artist && reader.NextResult())
                {
                    artist.Albums.Attach(s_albumMaterializer
                        .Materialize(reader)
                        .Bind(this.Albums));
                }
            }

            return artist;
        }
    }

In the DomainService class I created based on the MusicStoreEntities, I added the following method which calls the method we created above

  public Artist GetArtistAndRelatedAlbums(int artistId)
        {
            return this.ObjectContext.GetArtistAndRelatedAlbums(artistId);
        }

In the Generated MetaData class, Added [Include] attribute on Albums property

 public partial class Artist
    {
      
        internal sealed class ArtistMetadata
        {

            // Metadata classes are not meant to be instantiated.
            private ArtistMetadata()
            {
            }

            [Include]
            public EntityCollection<Album> Albums { get; set; }

            public int ArtistId { get; set; }

            public string Name { get; set; }
        }
    }

In the client project, we could write small bit of code to test and see, we get Artist Details along with all the Albums for the artist

Artist artist = null;
MusicStoreDomainContext _ctx = new MusicStoreDomainContext();           
            _ctx.Load<Artist>(_ctx.GetArtistAndRelatedAlbumsQuery(22), (lo) => {
                artist = lo.Entities.FirstOrDefault();
                }, null);                                    
            }, false);