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();
1
2
2/24/2015 9:54:01 AM

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.

1
2/24/2015 11:40:38 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow