Create ComplexType If Stored procedure returns multiple Select statements in Entity Framework

Guys, Today during our daily development we faced another big problem.

We are using Ef4(Entity Framework) in our project using EDM(Entity Data Model) approach .

The problem is that we have one stored procedure which returns two select statement in the output

For ex – Select * from emp;

                Select * from library;

1.In function import the complex type will only hold one type of object like either emp or library , so we can’t able to show result of the stored procedure .

2.If we manually execute Stored procedure query  then also it will return the result of first select.

Like this ->

var query = _cxt.ExecuteStoreQuery<TestModel>("exec testselect");

And the drawback is that EF4 is not supporting multiple select in edm.

It is available in EF5 by nugget.

Now we have come up with the result.

Here we go.

1.First we have downloaded extension for Entity Framework

Download From here.

2. Include that project in your project.

3.Create new class file and Create a partial class in the same name of your Entities Class

In my case

       public partial class TestDatabaseEntities : ObjectContext

Now suppose my stored procedure return as an output like this.

       Select * from tblState;

       Select * from tblCountry;

Include that extension in that file.

4.As there is two type of object returning from SP tblState and tblCountry.

So create two private readonly variable of same type.

Like this

       private static readonly Materializer<tblState> s_StateMaterializer = new Materializer<tblState>(r =>

            new tblState

            {

                id = r.Field<int>("id"),

                StateName = r.Field<string>("StateName"),

            });

        private static readonly Materializer<tblCountry> s_CountryMaterializer = new Materializer<tblCountry>(r =>

            new tblCountry

            {

                countryId = r.Field<int>("countryId"),

                countryName = r.Field<string>("countryName"),

                isActive = r.Field<bool>("isActive")

            });

5.Now Comes the Method which will return multiple record Set.

public void GetMultiple()

        {

            DbCommand command = this.CreateStoreCommand("TestSelect", CommandType.StoredProcedure); //Create the command which will call the sp.

            List<tblCountry> country;

            List<tblState> state;

            using (command.Connection.CreateConnectionScope()) //Opening Connetion

            using (DbDataReader reader = command.ExecuteReader()) // Getting Data on Data reader

            {

                state = s_StateMaterializer

                        .Materialize(reader)

                        .Bind(this.tblStates)

                        .ToList<tblState>();  //Extracting the State from the output

                if (reader.NextResult())

                {

                    country = s_CountryMaterializer

                        .Materialize(reader)

                        .Bind(this.tblCountries)

                        .ToList<tblCountry>();  //Extracting the Country from the output

                }

               

            }

        }

In my case it is void type you can change acc to your requirement.

That’s all now call this method and see the output.

Thanks

Morpheus

Fokat RND Team Member