Database Reference
In-Depth Information
To add the stored procedure to our model, do the following:
5.
Create a stored procedure called GetSubCategories that makes use of a Common Table
Expression to return all of the subcategories for a CategoryId recursively. The stored
procedure is shown in Listing 6-14.
Listing 6-14. The GetSubCategories() Stored Procedure That Returns Subcategories for a Given
CategoryId
create proc chapter6.GetSubCategories
(@categoryid int)
as
begin
with cats as
(
select c1.*
from chapter6.Category c1
where CategoryId = @categoryid
union all
select c2.*
from cats join chapter6.Category c2 on cats.CategoryId =
c2.ParentCategoryId
)
select * from cats where CategoryId != @categoryid
end
Add a method that takes an integer parameter and returns an ICollection<Category> to
your DbContext subclass, as shown in Listing 6-15. Entity Framework 6 Code First does not
yet support function imports in the way that the EF designer does, so in the method body
we'll call our stored procedure with the SqlQuery method that's defined in the Database
property of DbContext.
6.
Listing 6-15. Implementing the GetSubCategories Method in Our DbContext Subclass
public ICollection<Category> GetSubCategories(int categoryId)
{
return this.Database.SqlQuery<Category>("exec Chapter6.GetSubCategories @catId",
new SqlParameter("@catId", categoryId)).ToList();
}
We can use the GetSubCategories method that we've defined in our DbContext subclass to materialize our entire
graph of categories and subcategories. The code in Listing 6-16 demonstrates the use of the GetSubCategories()
method.
Listing 6-16. Retrieving the Entire Hierarchy Using the GetSubCategories() Method
using (var context = new EF6RecipesContext())
{
var book = new Category { Name = "Books" };
var fiction = new Category { Name = "Fiction", ParentCategory = book };
var nonfiction = new Category { Name = "Non-Fiction", ParentCategory = book };
var novel = new Category { Name = "Novel", ParentCategory = fiction };
var history = new Category { Name = "History", ParentCategory = nonfiction };
 
Search WWH ::




Custom Search