How can I Include child data using SqlQuery in EF 6?
asp.net asp.net-mvc-5 entity-framework entity-framework-6
Question
I am using EF 6 in MVC and have a query like this to get all my articles and then the category that the article is listed under. This returns results:
list = db.Articles.Where(a => a.PublishDate <= DateTime.Now)
.OrderByDescending(a => a.PublishDate)
.Skip(page * ArticleCount)
.Take(ArticleCount)
.Include(a => a.Category).ToList();
I also have a filtered query by category or subcategory which I have as a stored procedure. I'm using SqlQuery. The Category model isn't loaded in and Include doesn't work. I can't seem to find any examples online that relate to EF 6 using code first. How can I pull in the category data?
list = db.Database.SqlQuery<Article>(
"sp_GetArticlesByCategory @category, @subcategory",
new SqlParameter("@category", category),
new SqlParameter("@subcategory", subcategory))
.Skip(page * ArticleCount)
.Take(ArticleCount)
.ToList();
Popular Answer
The following is not preciselly an answer to the question: How can I Include child data using SqlQuery in EF 6?
But based on the explanation and the question: why do you need a stored procedure ?, I show the following:
if you need a tracked entity this can be done by using eager loading multiple levels.
from a in db.Articles.Include("Category.SubCategory")
where a.Category.SomeProperty == someValue
select a
But if not, you can use a DAO object and use a linq query
from a in db.Articles
where a.Category.SomeProperty == someValue
select new DaoArtType {
ArticleId = a.Id,
//...
Cat = new DaoCatType {
CatId = a.Category.Id
//...
}
}
From here we can imagine another DAO object
public class DAOArticleWithCat {
public int ArticleId {get; set;}
//...
public int ArticleCatId {get; set;}
//...
public int ArticleSubCatId {get; set;}
//...
}
such an DAO object can be populated by the result of a stored procedure. But you won't get tracked entities.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow