Monojit Sarkar Monojit Sarkar - 2 months ago 13
C# Question

EF Code First & Stored Procedures return Multiple Results

i read this article https://romiller.com/2012/08/15/code-first-stored-procedures-with-multiple-results/

and try to implement the below code to handle store proc which return multiple result set.

these are namespaces i included



using System.Data;
using System.Data.SqlClient;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;


see my full code



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using EFTest.demo1;

namespace EFTest
{
public partial class CallSP : Form
{
public CallSP()
{
InitializeComponent();
}

private void button4_Click(object sender, EventArgs e)
{
using (var db = new TestDBContext1())
{
db.Database.Initialize(force: false);
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "[dbo].[MultiResultSet]";

try
{

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

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


foreach (var item in blogs)
{
Console.WriteLine(item.Name);
}

// Move to second result set and read Posts
reader.NextResult();
var posts = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Addresses>(reader, "Addresses", MergeOption.AppendOnly);


foreach (var item in posts)
{
Console.WriteLine(item.Title);
}
}
finally
{
db.Database.Connection.Close();
}
}

}
}
}


my sample store proce which return multiple result set



CREATE PROCEDURE [dbo].[MultiResultSet]

AS
SELECT * FROM Customers

SELECT * FROM Addresses


i am getting compilation error.


The best overloaded method match for
'System.Data.Entity.Core.Objects.ObjectContext.Translate(System.Data.Common.DbDataReader,
string, System.Data.Entity.Core.Objects.MergeOption)' has some invalid
arguments


please help me what to fix as a result there should be no compilation error. thanks

Answer

Use MergeOption from namespace System.Data.Entity.Core.Objects instead of using MergeOption from System.Data.Objects

var blogs = ((IObjectContextAdapter)db)
                        .ObjectContext
                        .Translate<Customer>(reader, "Customers", System.Data.Entity.Core.Objects.MergeOption.AppendOnly);

and

var posts = ((IObjectContextAdapter)db)
                        .ObjectContext
                        .Translate<Addresses>(reader, "Addresses", System.Data.Entity.Core.Objects.MergeOption.AppendOnly);
Comments