Entity Framework Core filter related entities and get top 2 for each group

c# entity-framework entity-framework-core

Question

I am using Entity Framework Core 2.0.1 and I have the following models

public class Article
{

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
    public string Slug { get; set; }
    public int Approved { get; set; }
    public DateTime ArticleDate { get; set; }

    // ... some other fields

    public virtual ICollection<ArticleCategoryRelation> ArticleCategoryRelations { get; set; }
}

public class ArticleCategory
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    //... soem other fields
    [ForeignKey("ArticleCategoryParent")]
    public int? ArticleCategoryParentID { get; set; }

    public virtual ArticleCategory ArticleCategoryParent { get; set; }
    public virtual ICollection<ArticleCategory> SubCategories { get; set; }

    public virtual ICollection<ArticleCategoryRelation> ArticleCategoryRelations { get; set; }
}

public class ArticleCategoryRelation
{
    [Column(Order = 0)]
    public int ArticleId { get; set; }
    public Article Article { get; set; }
    [Column(Order = 1)]
    public int ArticleCategoryId { get; set; }
    public ArticleCategory ArticleCategory {get; set;}
}

Every article belongs to one or more categories. Categories might have parent category.

I want to get from database last two articles (where Approved = 1) with related category details, for each category that belongs to a parent category which id is given as input.

I have tried but with no success. I can't filter results of an .Include() entity. Is it possible... or I don't know how to do it?

All my data are accessed through entity framework with appContext (the context used to get entities from database). Can I achieve what I want through entity framework core (lambda expression is preferred over Linq if possible), or should I use ADO.NET library (which I know how to execute custom queries).

P.S. I want to get data only to show in the view... no edit is needed.

1
-1
5/17/2018 3:21:45 PM

Accepted Answer

You don't actually need to include here at all, as far as I can tell. Whenever you use data from a nav property, EF will go get the data from that table, as best it can filter it.

var CategoriesUnderParent = AppContext.ArticleCategories
.Where(c => c.ArticleCategoryParent == {parent});

foreach(var category in CategoriesUnderParent)
{
    var ArticlesAllowed = category.ArticleCategoryRelations
         .Where(acr => acr.Article.Approved == 1).Select(a => a.Article);

    var ArticlesPicked = ArticlesAllowed
         .OrderByDescending(ar => ar.ArticleDate)
         .Take(2);
   // Do something with your data
}
0
5/18/2018 2:57:02 PM


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