How can I Include child data using SqlQuery in EF 6?

Question

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();

lloydphillips

View more on Stack Overflow

tschmit007 · Accepted Answer · 2/24/2015 11:40:38 AM

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.

How can I Include child data using SqlQuery in EF 6?

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related