How to map a stored procedure result to a custom c

问题:

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.

回答1:

• 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.

回答2:

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;
}

回答3:

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