Taking last items from each category with Linq extension

Question

I need some help (Because I try to learn C# after 8 years old break)

I have two tables which has 1 to many relation.

    public class NewsCategory: IEntity
{
    public int NewsCategoryId { get; set; }
    public string NewsCategoryName { get; set; }
    public News News { get; set; }
}

And News table

public class News: IEntity
    {
        public int NewsId { get; set; }
        public string NewsHeader { get; set; }
        public string NewsContent { get; set; }
        public DateTime NewsReleaseDate => DateTime.Now;
        public string NewsImgUrl { get; set; }
        public int NewsCategoryId { get; set; }
        public NewsCategory NewsCategory { get; set; }
    }

NewsCategory.CategoryId are split into 8 selection. For example: Health, Sport, Travel, Fashion... And Table.News must have only 1 categoryid from Category table.

The question is how can I get the last Entries from each NewsCategories?

Forexample : Healt category had 10 entries, Sport category had 20 entries... I would like to take the last entries are written today. So as I have 8 selection for categories, then I must bring 8 news back.

I tried some codes but I could not reach at any point. My last chance is to ask help here from you guys.

I tried some codes shared on the internet but none of them worked for me.

public class EFNewsDal : EFGenericRepository<News, AdvertContext>, INewsDal
    {
        public List<News> GetLastEachNewsWithCategories()
        {
            using (AdvertContext con = new AdvertContext())
            {
                // con.News.Include(x => x.NewsCategory).Where(x => x.NewsCategoryId == x.NewsCategory.NewsCategoryId).Reverse().Select(x => x.NewsCategoryId).Distinct().ToList();
                //con.News.Include(x => x.NewsCategory).GroupBy(x => x.NewsCategoryId).Select(g => g.Last()).ToList();
              //var query1= con.Set<News>().Include(x => x.NewsCategory).Where(x => x.NewsCategoryId == x.NewsCategory.NewsCategoryId).GroupBy(x=>x.NewsCategoryId);
                //var query2 = query1.Select(g => g.Last());
            //    var query = from g in con.News
            //                where g.NewsCategoryId != 0
            //                group g by g.NewsCategoryId into grp
            //                select grp.OrderByDescending(g => g.NewsId).First();

            //    return query.ToList();
            //}                // con.News.Include(x => x.NewsCategory).Where(x => x.NewsCategoryId == x.NewsCategory.NewsCategoryId).GroupBy(x => x.NewsId).Select(grp => grp.LastOrDefault()).ToList();

        }
    }

update :

I tried the code written down.

return con.News.Include(x => x.NewsCategory)
                   .GroupBy(x => x.NewsCategoryId)
                    .Select(x => x.ToList().OrderByDescending(c => c.NewsReleaseDate).First()).ToList();

But I getting this error now.

this could be caused by Core 3.0 ?

Answer 1

You could need to Group by NewsCategoryId and select the latest in each group. For example,

con.News.Include(x => x.NewsCategory)
        .GroupBy(x=>x.NewsCategoryId)
        .Select(x=>x.ToList().OrderByDescending(c=>c.NewsReleaseDate).First());

Sample Input

Sample Output