Return multiple result set using DataReader and Entity Framework

We know th NextResult() function of Reader Object which is part of ADO.NET library. Using this mechanism we can read more than one entity in a single database class. Is there any way to read more than one entity on top of Entity Framework? Yes, we can implement same mechanism in EF too. 

DbContxt API does not support this operation directly so, we have to cast database object to ObjectContext to enjoy this facility.

Here is sample code implementation for same.

   

            var db = new ApplicationDbContext();

            var cmd = db.Database.Connection.CreateCommand();

            cmd.CommandText = "YourStoredProcedure";

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@param", Id));

           

            try

            {

                db.Database.Connection.Open();

                var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                var objectContext = ((IObjectContextAdapter)db).ObjectContext;

var Modl1 = objectContext.Translate<Model1>(reader, "Model1",         MergeOption.AppendOnly).FirstOrDefault();

                reader.NextResult(); 

              var Model2 = objectContext.Translate<Model2>(reader, "Model2",                   MergeOption.AppendOnly).FirstOrDefault();

            }

            finally

            {

                db.Database.Connection.Close();

            }

Translate<T>  function will transferred return Entity to T model. Now, the question is if the result type id single valued?

Say, for example, the query is something like this.

Select count(*) from Table.

It will return some integer value in result. In this case the T value should be integet

               var Model2 = objectContext.Translate<int>(reader).FirstOrDefault();

and no need to supply model name more.

Please make sure Model1 and Model2 are property of Context class which is inherited from DbContext.