When are SQL views appropriate in ASP.net MVC?
- by sslepian
I've got a table called Protocol, a table called Eligibility, and a Protocol_Eligibilty table that maps the two together (a many to many relationship). If I wanted to make a perfect copy of an entry in the Protocol table, and create all the needed mappings in the Protocol_Eligibility table, would using an SQL view be helpful, from a performance standpoint? Protocol will have around 1000 rows, Eligibility will have about 200, and I expect each Protocol to map to about 10 Eligibility rows and each Eligibility to map to over 100 rows in Protocol.
Here's how I'm doing this with the view:
var pel_original = (from pel in _documentDataModel.Protocol_Eligibility_View
where pel.pid == id
select pel);
Protocol_Eligibility newEligibility;
foreach (var pel_item in pel_original)
{
newEligibility = new Protocol_Eligibility();
newEligibility.Eligibility = (from pel in _documentDataModel.Eligibility
where pel.ID == pel_item.eid
select pel).First();
newEligibility.Protocol = newProtocol;
newEligibility.ordering = pel_item.ordering;
_documentDataModel.AddToProtocol_Eligibility(newEligibility);
}
And this is without the view:
var pel_original = (from pel in _documentDataModel.Protocol_Eligibility
where pel.Protocol.ID == id
select pel);
Protocol_Eligibility newEligibility;
foreach (var pel_item in pel_original)
{
pel_item.EligibilityReference.Load();
newEligibility = new Protocol_Eligibility();
newEligibility.Eligibility = pel_item.Eligibility;
newEligibility.Protocol = newProtocol;
newEligibility.ordering = pel_item.ordering;
_documentDataModel.AddToProtocol_Eligibility(newEligibility);
}
