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
