EF & Web API 2 Multiple Round trips to the database to populate object model

Question

It seems this problem would have been encountered before me but I'm not finding much help online probably because I don't really know what to search for.

My problem in short is that I have a db table. That table has 5 keys to other tables.

I then have a model that represents this table in EF. Of course this object that represents the db table has List<T> properties that are representations of foreign keys in the db. That doesn't seem to be the problem as much as the EF model that has this table representation but also List<T> properties to other models.

The problem I am experiencing is that a call to a stored procedure to populate the main modelforces additional calls to the db to populate the related List<T> models.

I am looking to improve performance namely by eliminating the multiple calls.

My only thought to this point is to modify the stored procedure to return multiple recordsets and match each List<T> property to its corresponding recordset.

My sterilized structure is something like this.

DB:

sql_Id           Int            PK
sql_Status       Int            FK
sql_Reason       Int            FK
sql_GuestId      Int
sql_Name         varchar
sql_Created      DateTime
sql_Original     Int            FK

EF:

public class OrderHeader : ClassBase
{
    public OrderHeader()
    {
        TaskCodeAssignments = new List<OrderHeaderTaskCodeAssignment>();
        StatusReasonCode = new OrderHeaderStatusReasonCode();
        StatusCode = new OrderHeaderStatusCode();
        Links = new OrderHeaderLinks();
    }

    public int OrderHeaderID { get; set; }
    public short OrderHeaderStatusCodeID { get; set; }
    public short? OrderHeaderStatusReasonCodeID { get; set; }
    public short? OriginatingApplicationId { get; set; }
    public string CustomerFirstName { get; set; }
    public string CustomerLastName { get; set; }
    public OrderHeaderStatusCode StatusCode { get; set; }
    public OrderHeaderStatusReasonCode StatusReasonCode { get; set; }
    public CustomerStatusCode CustomerStatusCode { get; set; }
    public ICollection<OrderHeaderTaskCodeAssignment> TaskCodeAssignments { get; set; }
}

public class OrderHeaderStatusCode
{
    public OrderHeaderStatusCode()
    {
        OrderHeaderStatusReasonCodes = new List<OrderHeaderStatusReasonCode>();
    }

    public ICollection<OrderHeaderStatusReasonCode> OrderHeaderStatusReasonCodes { get; set; }
    public virtual ICollection<OrderHeader> OrderHeader { get; set; }
}

The other custom types like OrderHeaderStatusReasonCode are pretty similar in design so I'm leaving out for brevity.

C# Web API

public async Task<IHttpActionResult>GetOrdersHistory([FromUri]GetOrderRequestParameters orderParams)
{
    ....removed for brevity....

    var query = await TheOrderRepository.GetOrderHistory(getOrder);

}

Order Repository:

public async Task<IQueryable<OrderHeader>> GetOrderHistory(GetOrderParameters orderParams)
{
   // this is the call to stored procedure that I would modify to return multiple recordsets
   var storedProcedure = StoredProcedure.Name.MyStoredProc.ToString();

   var ordersHistory = await dbctx.Database.SqlQuery<OrderHeader>(...), storedProcParam).ToListAsync();

   // now I jump off to fill in the other properties and their data has to come from the db
   await GetOrdersData(ordersHistory, orderParams.Include);
}

private async Task GetOrdersData(List<OrderHeader> ordersHistory)
{
   if (ordersHistory != null)
   {
     await LoadOrderStatusCodeForList(ordersHistory);
     await LoadOrderStatusReasonCodeForList(ordersHistory);
     await LoadCustomerStatusCodeForList(ordersHistory);
     await LoadOrderHeaderTaskCodeAssignmentsForList(ordersHistory);
     await LoadOrderHeaderTaskCodeForList(ordersHistory);
   }
}

Again most of these awaits are similar so I'm just going to give an example of one...

private async Task LoadOrderStatusCodeForList()
{
   ....snipped for brevity...
   await LoadOrderStatusCode(order.OrderHeaderStatusCodeID));
}

private async Task<OrderHeaderStatusCode> LoadOrderStatusCode(short orderHeaderStatusCodeId)
{
  ....snipped brevity....
  var storedProcedure = StoredProcedure.Name.MySprocStatusCode.ToString();

  return await _dbctx.Database.SqlQuery<OrderHeaderStatusCode>(...), ...).FirstOrDefaultAsync();
}

EDIT:

The crux is this. OrderHeader has properties with a custom type and basically those custom types have a List<T> that has to be populated. My current design is such that I repeatedly hit the db to populate those custom types List properties.

Is there a way to make one trip to the db to get all my information. As mentioned earlier the only way I can think of is to modify the stored procedure to return multiple record sets and then match them up.

BTW the architecture may be the flaw...in which case educate me on how to properly populate a complex object like this.

TIA

Answer 1

The root problem is that stored procedures aren't composable. In SQL you can't join a stored procedure call with anything (a database table or another stored procedure). So EF can't do that either.

If you want to get data with loaded collections from the database, normally you'd have to use Includes. EF will translate that into the appropriate joins and figure out how to load the entities and their collections from one big result set. But, as said, joins are no option here.

There is a way to load multiple result sets from one stored procedure. IMO it's pretty messy and very procedural. I would keep loading the data separately as you do now, if you want to keep using stored procedures. Others may suggest that you could load the additional data by lazy loading. Unfortunately that's not as straightforward as it should be with SqlQuery.

Another option of course is to start using regular DbSets (with Includes), but I can't judge if that's possible for you.