Working with Entity Framework 1.0 and Stored Procedures – how to deal with complex data types
Last months I worked on a ASP.NET project with Entity Framework.
The key requirement for a project was to use .NET Framework 3.5 not 4.0, so I used older version of EF that was included in .NET Framework 3.5 Service Pack 1.
I also must use SQL Server with Stored Procedures, instead of great features from the EF - LINQ.
Entity Framework is not a nice tool. But the problem is when I started to use Stored Procedures instead of generated SQL queries from LINQ.
To get started I recommend following articles:
Microsoft documentation - Quickstart (Entity Framework)
ADO.NET Entity Framework Tutorial and Basics:
ADO.NET team blog: Stored Procedure Mapping
Many examples in Microsoft documentation and blog posts does not show, how to deal with complex types by using Entity Framework and Stored Procedures?
The biggest drawback is, that the references are not automatically loaded by creating instances of objects. This means that child collection and child objects references are equal to null.
(This is not a problem when you are using LINQ. You write a LINQ query, SQL query will be generated and objects hierarchy is populated as you need: See example here.)
When working with EF 1.0 with Stored Procedure, you must deal with a problem manually.
1) Write “Select” Stored Procedures for object type you need and for all object types you need to reference.
- Select SP for root object - that return one data row by ID
- Select SP for single child objects - that return one data row by a parent ID
- Select SP for child collections - that returns a list of data rows by a parent ID
Because references are empty, you need to realize it with SQL Inner Joins, and query objects by a parent ID.
Example:
When we have such object hierarchy:
Select SP for root Account type:
Create Procedure [dbo].[usp_Account_SelectRow]
@ID bigint
As
Begin
Select
[ID],
[Name],
[AccountTypeId],
[Supervisor],
[ManagerName],
[Created],
[Modified]
From Account
Where
[ID] = @ID
End
GO
Select SP for child type Account Type. Here important is that you load AccountType data by Account.ID by using inner join ( 1 to N relation)
Create Procedure [dbo].[usp_AccountType_SelectRowsByAccountId]
@AccountID bigint
As
Begin
Select
AccountType.ID,
[Code],
AccountType.Name as Name
From AccountType inner join Account on AccountType.ID = Account.AccountTypeId
Where
Account.ID = @AccountID
End
GO
Select SP for child collection accountObjectives is similar, the difference is, that it return whole list of data rows (N to N relation)
CREATE Procedure [dbo].[usp_AccountObjective_SelectRowsByAccountId]
@AccountId bigint
As
Begin
Select
[ID],
[AccountId],
[ProductId],
[Titel],
[Ranking],
[Created],
[Modified]
From AccountObjective
Where
[AccountId] = @AccountId
Order by [Ranking]
End
SET ANSI_NULLS ON
GO
2) Map all “Select”Stored Procedures to the Methods using “Function Import” in Visual Studio EF Model browser.
3) Loading root object by using Imported select function. Attach needed child objects and child collections
In this example I load Account object and its referenced AccountType child object.
public Account FindById(long id)
{
SelectedItem = _dataContext.FindAccountById(id).First();
var accountType = _dataContext.GetAccountTypeForAccount(id).ToList();
if(accountType.Any())
{
SelectedItem.AccountTypeReference.Attach(accountType.First());
}
if(IsAuthorizedForSelectedAccount)
{
return SelectedItem;
}
else
{
throw new ApplicationException("The user does not have permission for selected Account.");
}
}
