Mapping to Child Collections / Objects

Dec 1, 2014 at 11:35 PM
I have a result set I am trying to generate that maps to 12+ relational tables with several of those needing to be returned to a collection.

Using traditional Code First with custom mapping I am very easily able to join the data together, however the performance after that many different tables are joined together is just awful. I'd like to solve the problem with a stored procedure but I cannot seem to find any examples online that can support complex child objects. This package seems to be the closest for support that I've come across which Julie Lerman mentions on her new PluralSight video.

Let's say for example we have:
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public string Description { get; set; }
    public virtual Customer Customer { get; set; }
}
and the stored procedure needs to return a collection of customers and their orders
CREATE PROCEDURE [dbo].[CustomersOrdersAndAnswer] AS
SELECT 
   c.[Id],
   c.[Name],
   o.[Id],
   o.[Customer_Id],
   o.[Description]
FROM [dbo].[Customers] AS c
    INNER JOIN [dbo].[Orders] AS o ON c.[Id] = o.[customer_Id]
Is there a way that this can be mapped perhaps via convention such that:
CREATE PROCEDURE [dbo].[CustomersOrdersAndAnswer] AS
SELECT
   c.[Id],
   c.[Name], 
   o.[Id] AS [order.Id], 
   o.[Customer_Id] AS [order.Customer_Id], 
   o.[Description] AS [order.Description]
FROM [dbo].[Customers] AS c 
   LEFT OUTER JOIN [dbo].[Orders] AS o ON c.[Id] = o.[customer_Id]
and allow the mapping that is controlled in a mapping class be utilized:
HasOptional(a => a.Customer).WithMany(b => b.Orders).HasForeignKey(c => c.CustomerId);


The assumption is that since traditional mapping with Code First is able to automatically create the joins even with it's crazy [EXTENT1] [EXTENT2] [LIMIT1] [PROJECT6] that a way can be handled here as well.

We used to do something with ADO & DataTables/DataRows to find Mapping via reflection similar to:
public virtual void LoadFromDataRow(DataRow dr, string tableName)
{
    if (tableName.Length > 0 && !tableName.EndsWith("."))
        tableName += ".";

    // foreach protected/private instance field, check attempt to assign a value
    foreach (FieldInfo fi in GetType().GetFields(BindingFlags.Instance | BindingFlags.NonPublic))
    {
        string propertyName = fi.Name;
        if (tableName.Length > 0)
            propertyName = String.Format("{0}{1}", tableName, propertyName);

        // check if a corresponding column exists in the datatable
        if (!dr.Table.Columns.Contains(propertyName))
            continue;

        // only set the value if it is not null
        if (dr[propertyName] != null && !dr.IsNull(propertyName))
        {
            fi.SetValue(this, dr[propertyName]);
        }
    }
}

protected void PopulateFromDataTable<TDerived>(DataTable dt, string columnPrefix, bool fromRow) where TDerived : TObject, new()
{
    if (columnPrefix.Length > 0 && !columnPrefix.EndsWith("."))
        columnPrefix += ".";

    string objectIDColumn = string.Format("{0}ID", columnPrefix);
    if (dt.Columns.Contains(objectIDColumn))
    {
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i][objectIDColumn] is DBNull)
                continue;

            TKey id = (TKey)dt.Rows[i][objectIDColumn];
            if (!Contains(id))
            {
                TObject item = new TDerived();

                if (fromRow)
                {
                    item.LoadFromDataRow(dt.Rows[i], columnPrefix);
                }
                else
                {
                    DataTable subSet = new DataTable();
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        subSet.Columns.Add(dt.Columns[j].ColumnName, dt.Columns[j].DataType);
                    }

                    DataRow[] rows = dt.Select(string.Format("{0} = '{1}'", objectIDColumn, id));
                    for (int j = 0; j < rows.Length; j++)
                    {
                        subSet.Rows.Add(rows[j].ItemArray);
                    }

                    item.LoadFromDataTable(subSet, columnPrefix);
                }

                Add(item);
            }
        }
    }
}
I am not that familiar with what is exposed in ObjectContext or DBContext but imagine that doing something similar should be possible. Any suggestions on where to get started?
Dec 3, 2014 at 6:55 AM
Unfortunately EF does not support materializing entity graphs from a single resultset containing combined data for the graph returned from a stored procedure. However what you can do instead is to create a stored procedure that returns multiple resultsets. In your case the first resultset would contain data for Customers while the second resultset would contain data for Orders. When materializing EF should fix up relations and you should end up with the graph you were looking for (without having to create crazy joins).

Hope this helps,
Pawel
Dec 3, 2014 at 1:36 PM
Thanks Pawel. I ended up going a similar route with EntityFramework.Utilities. This utility calls the DB in parallel for the multiple recordsets and then automatically combines the child collections to the parent. It reduced my load time by 80%.
Dec 3, 2014 at 7:13 PM
Yes, if the logic to get entities is straightforward using multiple Linq queries is a good way to avoid overly complicated queries EF sometimes creates for graphs. Using a stored procedure returning multiple result sets only makes sense if you have some custom logic that is hard to express in a Linq query.

Thanks,
Pawel