How to map a stored procedure result to a custom class?

Question

I am using entity framework 5 and I've added two stored procedures to my .edmx model.

The first stored procedure returns a string and if I open the model browser in Visual Studio, I can change the Returns a Collection Of section and set the scalar type to String, as follows:

Then in the code, I can call the SP and get the result like follows:

using (var context = new MyEntities())
{
    var spResult = context.mySPNameFromTheModel(exampleParameter1, exampleParameter2); // This will get the output as string
}

The above example works fine, but how can I do the same with a Complex type?

My second stored procedure returns just one row, with two columns, both strings. So I created a class with two string properties, but I cannot see it when I change to Complex, I cannot select anything from the drop down because it is empty.

When I click on Get Column Information, it just says that the SP doesn't return any columns, which isn't true.

score 3 · Accepted Answer · answered Nov 19 '15 at 08:44

3

Check whether the edmx is updated with corresponding stored procedure
Try creating a Complex Type, in model browser manually.
Save & Rebuild Solution
Then try Add Function Import and give function importname, sp & in Returns Collection select the manually created Complex Type.
Now click get column information.
Then click ok.

answered Nov 19 '15 at 08:44

Vishnu S

817
14
20

You are right, I have to add the `Complex Type` in the model browser manually. I didn't know that this is the way to do it. I thought that when I create a class in the project, I will be able to directly select it. – Apostrofix Nov 19 '15 at 08:59
After following the steps, I was getting compilation error saying the complex type class is not found ..I had to click on "Run Custom Tool" on .tt file, which generated the complex type class automatically, and compilation errors were gone. – Dipendu Paul Oct 23 '17 at 08:46

score 2 · Answer 2 · answered Dec 27 '17 at 21:52

I found this Mapping Stored Procedure Output to POCO Class article to be very helpful in answering the question specified in the title since I was looking for a way to do it without the hassle of writing strings while using ADO.NET.

public IList<Order> GetOrders(string CustomerID)
{
    var orders = new List<Order>();
    using (var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        // Use count to get all available items before the connection closes
        using (SqlCommand cmd = new SqlCommand("PagingProcTest", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@CustomerID", SqlDbType.NChar).Value = CustomerID;

            cmd.Connection.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                // Map data to Order class using this way
                orders = DataReaderMapToList<Order>(reader).ToList();

                // instead of this traditional way
                // while (reader.Read())
                // {
                    // var o = new Order();
                    // o.OrderID = Convert.ToInt32(reader["OrderID"]);
                    // o.CustomerID = reader["CustomerID"].ToString();
                    // orders.Add(o);
                // }
            }
            cmd.Connection.Close();
        }
    }
    return orders;
}

private static List<T> DataReaderMapToList<T>(DbDataReader dr)
{
    List<T> list = new List<T>();
    while (dr.Read())
    {
        var obj = Activator.CreateInstance<T>();
        foreach (PropertyInfo prop in obj.GetType().GetProperties())
        {
            if (!Equals(dr[prop.Name], DBNull.Value))
            {
                prop.SetValue(obj, dr[prop.Name], null);
            }
        }
        list.Add(obj);
    }
    return list;
}

score 0 · Answer 3 · answered Nov 19 '15 at 08:27

0

Try adding aliases to columns which are returned from SP. For instance: SELECT table1.id ID

answered Nov 19 '15 at 08:27

Aleksa

2,976
4
30
49

I have done that in the stored procedure. The output of the SP looks like that: `SELECT @fResult as finalResult, @pResult as preliminaryResult`. And they both are `nvarchar`s – Apostrofix Nov 19 '15 at 08:31
And also in the model class in c# I have named the properties the same way. – Apostrofix Nov 19 '15 at 08:32

How to map a stored procedure result to a custom class?

3 Answers3

Linked