Return Custom Object <List T> from Entity framework and assign to Object Data Source

Question

I need some guidance with an issue, I am using Entity Framework 4.0, I have a DAL and BLL and am binding to ObjectDataSource on the page.

I had to write a stored procedure using PIVOT and dynamic SQL to return the data from multiple entities the way I want. Now I am trying to figure out how can I get Entity Framework to return a custom object that I can bind to my ObjectDataSource on the page, I NEED to use a custom object or a dynamic object since the stored procedure can return any number of columns so I can't use a strongly typed class or entity and I need to be also able to bind this with an ObjectDataSource.

Can someone point out a good way to do this and how to define my function? With some code examples please.

I read that I should try to use List<T> for returning an object since EF does not support returning datatables/datasets, I have the following so far but I know this isn't correct.

I have not worked with generics much, if you could point out how to do this I'm sure this would be helpful for a lot of people. Please provide code examples for the function and how to bind ObjectDataSource to the return object?

Your help is greatly appreciated!!

Thanks for your help Richard this is what my function looks like right now based on your suggestion for using DbDataRecord

C# function for ObjectDataSource in DAL

public List<DbDataRecord> GetData(int product_id)
{
    List<DbDataRecord> availableProducts = new List<DbDataRecord>();

    var groupData = context.ExecuteStoreQuery<DbDataRecord>("exec 
  spGetProducts @ProductID={0}", product_id);

    availableProducts = groupData.ToList<DbDataRecord>();

    return availableProducts;
}

ObjectDataSource in ASPX page

<asp:ObjectDataSource ID="ODSProductAvailability" runat="server"
        TypeName="Project.BLL.ProductBL" 
        SelectMethod="GetData"  >
     <SelectParameters>
        <asp:SessionParameter Name="product_id" SessionField="ProductID" />
     </SelectParameters>
</asp:ObjectDataSource>

Right now I'm getting this error when I access the page:

The result type 'System.Data.Common.DbDataRecord' may not be abstract and must include a default constructor

Is this because the ExecuteStoreQuery expects to be defined class or entity? How can I just create an object based on the stored procedure results and assign it that?

Forgave

Hi, how did you solve your problem? The result type 'System.Data.Common.DbDataRecord' may not be abstract and must include a default constructor – Conciseness

You can't use DbDataRecord with ExecuteStoreQuery. If you are not able to define the columns returned by the query, you should use SqlDataReader. If you are able to define the columns returned, create a class and use it to materialize the query. – Kilogram

Answer 1

If you already have an Entity type that matches your proc return type, use it as the type parameter.

public List<MyEntity> GetData<MyEntity>(int product_id) where T : class 
{

    List<MyEntity> myList = new List<MyEntity>(); 

    var groupData = context.ExecuteStoreQuery<MyEntity>("exec 
    spGetProductsByGroup @ProductID={0}", product_id);

    return myList;
}

Otherwise you could use an ADO.NET DataReader to build the list manually.

using (SqlConnection connection = new SqlConnection("your connection string"))
{
    SqlCommand command = new SqlCommand(
      "exec spGetProductsByGroup @ProductID",
      connection);
    command.Parameters.Add(product_id);

    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    List<ProcType> list = new List<ProcType>();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            list.Add(new ProcType(){Property1 = reader.GetInt32(0), Property1 = reader.GetString(1));
        }
    }
    reader.Close();

    return list;
}

Algology

No thats the problem the stored proc gets data from multiple entities and displays it in a different format, I cant return it with an Entity Type, do you have any examples of using Datareader like you mentioned? or is there a better way to generate customObject from the proc return results? – Forgave

I'm not sure about using a non Entity type, but I added a DataReader example. See the link above for more general examples. – Algology

Answer 2

what about something like this:

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    string myQuery = @"SELECT p.ProductID, p.Name FROM 
        AdventureWorksEntities.Products as p";

    foreach (DbDataRecord rec in new ObjectQuery<DbDataRecord>(myQuery, context))
    {
        Console.WriteLine("ID {0}; Name {1}", rec[0], rec[1]);
    }
}

Rune

Answer 3

If you want to present the results in say a GridView then actually you're nearly there - with the binding and AutoGenerate columns, because ObjectQuery is an IEnumerable. I'm using ObjectQuery but you can interchange this with ExecuteStoreQuery - as they both return an IEnumerable

string myQuery = @"SELECT p.Name,p.VatNumber FROM MyEntities.Users as p";

ProductList.ItemsSource = new ObjectQuery<DbDataRecord>(myQuery, context);

in the XAML

<DataGrid AutoGenerateColumns="True" x:Name="ProductList"/>

and you'll see the columns that are returned directly in the UI.

If you're not using WPF and you want a List of your elements then all you need to do is:

var myList =  new ObjectQuery<DbDataRecord>(myQuery, context).ToList();

or to take your original method it would be:

var myList = context.ExecuteStoreQuery<DbDataRecord>("exec spGetProductsByGroup @ProductID={0}", product_id);

If you do need to iterate over the fields; then the following will do the trick:

foreach (var rec in context.ExecuteStoreQuery<DbDataRecord>("exec spGetProductsByGroup @ProductID={0}", product_id))
    {
    for (var ri = 0; ri < rec.FieldCount;ri++)
    {
        Console.WriteLine(rec.GetDataTypeName(ri)
                          +"   " + rec.GetName(ri)
                          +" = " + rec.GetValue(ri));
        }
    }

One of the many beauties of LINQ and Generics is that most of the time you can simply not worry too much about the actual datatypes until you eventually get to where you need to process them.

Eviaevict

Thanks for your reply Richard I tried to do your suggestion for var myList = context.ExecuteStoreQuery<DbDataRecord>("exec spGetProductsByGroup @ProductID={0}", product_id); but it gives me an error saying The result type 'System.Data.Common.DbDataRecord' may not be abstract and must include a default constructor. I am not using WPF btw, this is an ASP.net page, please see my post above for an update for the function and Object Data source so you know what I have, thanks again for your help – Forgave

@Forgave to create the list do availableProducts = groupData.ToList(); - you don't need to tell it the type it will figure it out. – Eviaevict

Ok thats good but what does the function's signature look like in the DAL class and Business layer class? so far I have tried List<T> GetProductsByGroup<T>(int client_id) and List<T> GetAvailabilityByCarGroups<T>(int client_id) where T : DbDataRecord but none of these work – Forgave

List<DbDataRecord> GetSomeData() – Eviaevict

I tried the function signature like you mentioned and it still gives me the following error var myList = context.ExecuteStoreQuery<DbDataRecord>("exec spGetProductsByGroup @ProductID={0}", product_id); gives me an error saying The result type 'System.Data.Common.DbDataRecord' may not be abstract and must include a default constructor, I even tried using context.ExecuteStoreQuery<dynamic>>("exec spGetProductsByGroup @ProductID={0}", product_id); as well and it builds fine but the page throws an error object refernce could not be found – Forgave