使用LINQ扩展获取各新闻分类最新条目（C# EF Core问题）

Hey there! Let's break down what's going wrong here and fix it step by step—since you're jumping back into C# after 8 years, let's make sure we cover all the gotchas, especially with EF Core 3.0.

First: Critical Issue with Your Entity Class #

Let's start with the biggest problem in your code: the NewsReleaseDate property in the News class is a computed property that returns DateTime.Now every time it's accessed. That means:

• EF can't store this value in the database (it's not a persisted property)
• You'll never get actual "release dates" for your news—every check will just return the current time, making it impossible to filter for today's news.

Fix this first by changing it to a persisted property:

public class News: IEntity {
    public int NewsId { get; set; }
    public string NewsHeader { get; set; }
    public string NewsContent { get; set; }
    // Replace computed property with a persisted one
    public DateTime NewsReleaseDate { get; set; }
    public string NewsImgUrl { get; set; }
    public int NewsCategoryId { get; set; }
    public NewsCategory NewsCategory { get; set; }
}

Now you can set the actual release date when creating news entries, and EF will store it in the database.

Why Your Original Query Failed #

EF Core 3.0 has strict limitations on how it translates LINQ queries to SQL, especially with GroupBy. Your original code tries to:

1. Group news by category ID in the database
2. Pull all grouped data into memory with ToList()
3. Sort each group in memory and take the first item

This approach is inefficient (loads too much data) and often fails because EF Core 3.0 can't translate the combination of database-side grouping and in-memory sorting correctly. Also, you didn't filter for today's news—your query would return the latest news of all time per category, not just today's.

Solution 1: Subquery + Join (EF Core 3.0 Friendly) #

This approach first finds the latest release date for each category today, then fetches the corresponding news entry:

public class EFNewsDal : EFGenericRepository<News, AdvertContext>, INewsDal {
    public List<News> GetLastEachNewsWithCategories() {
        var today = DateTime.Today;
        var tomorrow = today.AddDays(1); // Use this to avoid time component issues

        using (AdvertContext con = new AdvertContext()) {
            // Step 1: Get the latest release date per category for today
            var latestDatesPerCategory = con.News
                .Where(n => n.NewsReleaseDate >= today && n.NewsReleaseDate < tomorrow)
                .GroupBy(n => n.NewsCategoryId)
                .Select(g => new {
                    CategoryId = g.Key,
                    LatestDate = g.Max(n => n.NewsReleaseDate)
                });

            // Step 2: Join to get the actual news entries, including category data
            var latestNews = from n in con.News.Include(x => x.NewsCategory)
                             join ld in latestDatesPerCategory 
                                 on new { n.NewsCategoryId, n.NewsReleaseDate } equals new { ld.CategoryId, ld.LatestDate }
                             select n;

            return latestNews.ToList();
        }
    }
}

Solution 2: Window Functions (Cleaner, EF Core 3.0 Supported) #

EF Core 3.0 introduced support for window functions like ROW_NUMBER(), which lets us rank news entries per category and pick the top one:

public class EFNewsDal : EFGenericRepository<News, AdvertContext>, INewsDal {
    public List<News> GetLastEachNewsWithCategories() {
        var today = DateTime.Today;
        var tomorrow = today.AddDays(1);

        using (AdvertContext con = new AdvertContext()) {
            // Rank news entries per category, ordered by release date descending
            var rankedNews = con.News
                .Where(n => n.NewsReleaseDate >= today && n.NewsReleaseDate < tomorrow)
                .Select(n => new {
                    News = n,
                    Rank = EF.Functions.RowNumber()
                        .Over(PartitionBy: n.NewsCategoryId, OrderBy: n.NewsReleaseDate descending)
                });

            // Pick only the top-ranked (latest) entry per category, include category data
            return rankedNews
                .Where(r => r.Rank == 1)
                .Select(r => r.News)
                .Include(r => r.News.NewsCategory)
                .ToList();
        }
    }
}

Ensuring You Get 8 Results (Even If a Category Has No Today's News) #

Your requirement says you need exactly 8 results (one per category). The above solutions will only return categories that have today's news. To include all 8 categories (even if they have no news today), use a left join with all categories:

public class EFNewsDal : EFGenericRepository<News, AdvertContext>, INewsDal {
    public List<News> GetLastEachNewsWithCategories() {
        var today = DateTime.Today;
        var tomorrow = today.AddDays(1);

        using (AdvertContext con = new AdvertContext()) {
            // Get latest news per category (if exists)
            var latestNewsPerCategory = con.News
                .Where(n => n.NewsReleaseDate >= today && n.NewsReleaseDate < tomorrow)
                .GroupBy(n => n.NewsCategoryId)
                .Select(g => new {
                    CategoryId = g.Key,
                    LatestNews = g.OrderByDescending(n => n.NewsReleaseDate).FirstOrDefault()
                });

            // Left join with all categories to ensure 8 results
            return con.NewsCategory
                .GroupJoin(latestNewsPerCategory,
                    category => category.NewsCategoryId,
                    newsGroup => newsGroup.CategoryId,
                    (category, newsGroup) => newsGroup.Select(ng => ng.LatestNews).FirstOrDefault() 
                        ?? new News { NewsCategory = category }) // Return empty news with category if no news exists
                .ToList();
        }
    }
}

Quick Note About EF Core 3.0 #

EF Core 3.0 is quite old now (released in 2019). If you can, consider upgrading to a newer version (like EF Core 6 or 7) — they have much better LINQ query translation, more features, and better performance.

内容的提问来源于stack exchange，提问作者Rudvan