Code first approach for store procedure

See more:

MVC5

entity-framework

I have SP which is returns multiple result set.


CREATE Procedure [dbo].[GetForm2Data]

(@Form2Id bigint)
as

Begin

Declare @IGMRefNo varchar(50)

Select @IGMRefNo = frm2.IGMReferenceNo
from IMP_Form2 frm2
left join M_Courier_MST Courier on frm2.CourierCoId=Courier.CourierCoId
where Form2Id = @Form2Id

Select frm2.Form2ID, frm2.Form2No, frm2.IGMReferenceNo, replace(CONVERT(varchar(11), frm2.IGMReferenceDate,105),' ','-') as IGMReferenceDate,
frm2.ECMReferenceDocNo,replace(CONVERT(varchar(11),frm2.ECMReferenceDocDate,105),' ','-') as ECMReferenceDocDate,
convert(decimal(18,2),frm2.ECMReferenceDocManifestWt) as ECMReferenceDocManifestWt, frm2.ECMReferenceNonDocNo,
replace(CONVERT(varchar(11),frm2.ECMReferenceNonDocDate,105),' ','-') as ECMReferenceNonDocDate,
convert(decimal(18,2),frm2.ECMReferenceNonDocManifestWt) as ECMReferenceNonDocManifestWt, isnull(frm2.ThokaNo,'') as ThokaNo,
isnull(frm2.MAWBNo,'') as MAWBNo, frm2.CourierCoId,Courier.CourierCoName, frm2.AirlineId,
frm2.FlightNumber,frm2.AirportofArrival,frm2.AirportofShipment,frm2.CountryofExportation,
replace(CONVERT(varchar(11),frm2.ActualDateofArrival,105),' ','-') as ActualDateofArrival,
case when len(ActualTimeofArrival) = 4 then convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 3 then '0' + convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 2 then '00' + convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 1 then '000' + convert(varchar(4), frm2.ActualTimeofArrival) End as ActualTimeofArrival,
frm2.TerminalCode, frm2.Status, convert(varchar(23),frm2.LastUpDtOn,121) as LastUpDtOn,isnull(frm2.CreatedBy,'') as CreatedBy,
isnull(replace(CONVERT(varchar(11),frm2.CreatedOn,105),' ','-'),'') as CreatedOn
from IMP_Form2 frm2
left join M_Courier_MST Courier on frm2.CourierCoId=Courier.CourierCoId
where Form2Id = @Form2Id

if @@ROWCOUNT =0 select 'No Record Exists'

Select Form2Type,ThokaNo, NumberofBags, NumberofPkgs, NumberofShps, convert(decimal(18,2),WeightofShps) as WeightofShps,
NumberofShpsTrans, NumberofPkgsTrans, convert(decimal(18,2),WeightofTrans) as WeightofTrans
from IMP_Form2Dtl where Form2Id = @Form2Id

if @@ROWCOUNT =0 select 'No Record Exists'

select a.MAWBNo, ActualNoofPackages, ActualWeight
from imp_form1dtl a
join imp_form1 b on a.Form1Id=b.Form1Id
where b.IgmNo=@IGMRefNo

if @@ROWCOUNT = 0 select 'No Record Exists'

End

and my logic to read this output is

What I have tried:

using (var db = new Models.DAL())
          {
              List<IMP_Form2> Form2hdr = new List<IMP_Form2>();
              List<IMP_Form2Dtl> Form2dtl = new List<IMP_Form2Dtl>();
              List<IMP_Form1Dtl> Form1dtl = new List<IMP_Form1Dtl>();
              // If using Code First we need to make sure the model is built before we open the connection
              // This isn't required for models created with the EF Designer
              db.Database.Initialize(force: false);

              SqlParameter param = new SqlParameter();
              param.ParameterName = "@Form2Id";
              param.SqlDbType = SqlDbType.Int;
              param.Value = 2;
              param.Direction = ParameterDirection.Input;


              // Create a SQL command to execute the sproc
              var cmd = db.Database.Connection.CreateCommand();
              cmd.CommandText = "[dbo].[GetForm2Data]";
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add(param);

              try
              {

                  db.Database.Connection.Open();
                  // Run the sproc
                  var reader = cmd.ExecuteReader();



                  // Read Blogs from the first result set
                  var form2hdr = ((IObjectContextAdapter)db)
                      .ObjectContext
                      .Translate<IMP_Form2>(reader, "IMP_Form2", MergeOption.AppendOnly);

                  foreach (var item in form2hdr)
                  {
                      Form2hdr.Add(item);
                  }

                  VMForm2 result = new VMForm2();
                  result.lstform2 = Form2hdr;

                  // Move to second result set and read Posts
                  reader.NextResult();

                  var form2dtl = ((IObjectContextAdapter)db)
                      .ObjectContext
                      .Translate<IMP_Form2Dtl>(reader);

                  foreach (var item in form2dtl)
                  {
                      Form2dtl.Add(item);
                  }

                  result.lstform2dts = Form2dtl;

                  var form1dtl = ((IObjectContextAdapter)db)
                     .ObjectContext
                     .Translate<IMP_Form1Dtl>(reader);

                  foreach (var item in form1dtl)
                  {
                      Form1dtl.Add(item);
                  }

                  result.lstForm1dtls = Form1dtl;

                  return result;
              }
              finally
              {
                  db.Database.Connection.Close();
              }
          }
      }

and i have 3 different models to mapped this.But problem is some result set have columns which are not mapped in Models.

So i gives me error..So is there anything i can implement other that writing separate model for each SP