How can I Include child data using SqlQuery in EF 6?
asp.net asp.net-mvc-5 entity-framework entity-framework-6
Question
I have a query like this to obtain all of my articles and then the category that each article is classified under while using EF 6 in MVC. This yields these outcomes:
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 stored process that filters a query based on category or subcategory. I utilizeSqlQuery
The Category model is not inserted, andInclude
fails to work. Online, I can't seem to find any instances of EF 6 using code first. How do I get the category data to pull in?
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 does not quite address the query: How can I use SQL Query to Include child data in EF 6?
However, based on the justification and the number Why is a stored procedure necessary?, I demonstrate the following:
If you require a tracked entity, use the command hurriedly loading several stages.
from a in db.Articles.Include("Category.SubCategory")
where a.Category.SomeProperty == someValue
select a
If not, though, you can utilize both a DAO object and 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
//...
}
}
We can envision another DAO object from here.
public class DAOArticleWithCat {
public int ArticleId {get; set;}
//...
public int ArticleCatId {get; set;}
//...
public int ArticleSubCatId {get; set;}
//...
}
The output of a stored procedure can be used to populate a DAO object of this type. You won't receive tracked entities though.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow