如何将含LEFT JOIN与DISTINCT的SQL查询改写为Linq Lambda语句？

First, let's break down what your original SQL is doing: it gets distinct User records (with Id, FirstName, LastName, Email) where the user has at least one Booklet released in the specified year, ordered by LastName ascending. The LEFT JOIN in your SQL effectively acts like an INNER JOIN here because your WHERE clause filters on a Booklet field—users without matching booklets will be excluded.

Issues with Your Current Code #

• Incorrect GroupBy: Your GroupBy(x => x.UserId && x.YearReleased) uses a boolean expression as the grouping key, which doesn't make sense for this use case. You don't need grouping to get distinct users here.
• N+1 Database Queries: Fetching a list of UserIds first, then looping to fetch each user individually creates unnecessary database calls that hurt performance.
• Missing Join Logic: You aren't properly linking the User and Booklet tables to apply the year filter correctly.

Clean Single-Statement LINQ Solutions #

Method Syntax (Fluent Style)

This matches your original SQL's logic closely and runs as a single database call:

var yearOfRelease = 18;
List<User> users = db.User
    // Join User and Booklet on matching Id/UserId
    .Join(db.Booklet,
          user => user.Id,
          booklet => booklet.UserId,
          (user, booklet) => new { User = user, Booklet = booklet })
    // Filter booklets by the target year
    .Where(joinResult => joinResult.Booklet.YearReleased == yearOfRelease)
    // Select only the User object
    .Select(joinResult => joinResult.User)
    // Remove duplicate users (in case a user has multiple booklets in the year)
    .Distinct()
    // Order by last name ascending
    .OrderBy(user => user.LastName)
    // Convert to a List<User>
    .ToList();

Query Syntax (SQL-like Style)

If you prefer syntax that mirrors your original SQL structure:

var yearOfRelease = 18;
List<User> users = (from user in db.User
                    join booklet in db.Booklet on user.Id equals booklet.UserId
                    where booklet.YearReleased == yearOfRelease
                    select user)
                   .Distinct()
                   .OrderBy(user => user.LastName)
                   .ToList();

If You Need a True LEFT JOIN (Include Users Without Matching Booklets) #

If you actually want to retain users who don't have any booklets in the target year (your original SQL doesn't do this, but just in case), move the year filter to the join condition:

var yearOfRelease = 18;
List<User> users = (from user in db.User
                    join booklet in db.Booklet
                        on new { UserId = user.Id, Year = yearOfRelease }
                        equals new { UserId = booklet.UserId, Year = booklet.YearReleased } into bookletGroup
                    from booklet in bookletGroup.DefaultIfEmpty()
                    select user)
                   .Distinct()
                   .OrderBy(user => user.LastName)
                   .ToList();

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