Ramification of using a stored procedure as a return object as opposed to using a foreach to populate a data model?
Here are two example methods, the first uses the foreach statement to populate the data model
public List<TheOrderSummary> GetTheOrderSummaryByID(int id)
{
ExoEntities = new ExoEntities();
List<TheOrderSummary> lst = new List<TheOrderSummary>();
foreach(var a in query)
{
lst.Add(new TheOrderSummary
{
OrderDetailID = a.OrderDetailID,
OrderHeaderID = a.OrderHeaderID,
ItemSeq = a.ItemSeq,
MaterialID = a.MaterialID,
Description = a.Description,
DWidth = a.DWidth,
DLength = a.DLength,
MaterialArea = a.MaterialArea.ToString(),
MaterialDetail = a.MaterialDetail,
DHeight = a.DHeight,
PurchUnitOfMeasure = a.PurchUnitOfMeasure,
SellUnitOfMeasure = a.SellUnitOfMeasure,
MaterialCategory = a.MaterialCategory,
MaterialType = a.MaterialType,
MaterialSubType = a.MaterialSubType,
ColorID = (int)a.ColorID,
Color = a.Color,
MaterialPrice = a.MaterialPrice,
MaterialCost = a.MaterialCost,
MaterialLocationID = (int)a.MaterialLocationID,
MaterialLocation = a.MaterialLocation,
LaborPrice = a.LaborPrice,
LaborCost = a.LaborCost,
VendorID = (int)a.VendorID,
VendorName = a.VendorName,
Size = a.Size,
Height = (decimal)a.Height,
Length = (decimal)a.Length,
Width = (decimal)a.Width,
PurchaseQuantity = (decimal)a.PurchaseQuantity,
SellQuantity = (decimal)a.SellQuantity,
TotalFootage = (decimal)a.TotalFootage,
GeneratedItemInd = (int)a.GeneratedItemInd,
ExtendedMaterialPrice = (decimal)a.ExtendedMaterialPrice,
ExtendedLaborCost = (decimal)a.ExtendedLaborCost,
ExtendedMaterialCost = (decimal)a.ExtendedMaterialCost
});
}
return lst;
}
and this one uses a stored procedure to return an object
public List<usp_GetTheOrderDetails_Result> GetTheOrderSummaryByID(int id)
{
ExoEntities = new ExoEntities();
var query = ExoEntities.usp_GetTheOrderDetails(id);
return query.ToList();
}
Both are in the DAL, and the method that any of them can call is JSONResult, both of them can be used to populate the grid. Which branches would use the second type down the road rather than the first? They both return the same, from performance level views of this, without making a number, the second is faster
source to share
The pain that comes with directly returning a result is that you get a "hard" dependency on the information contract between the consumer and the underlying data provider. If you make changes to the underlying data structure, you must update the client at the same time (which can lead to varying degrees of pain).
If you use your first option instead, you encapsulate knowledge of the underlying information structure at that level and can use that to map the old and new contracts, thereby decoupling the direct dependency between the client and the data provider.
So the second option might speed up a bit (although it really should be marginal), but the first one will probably give you a lot less pain when it comes to maintaining code and longer deployments.
source to share