How do I write a LINQ query for a list of users with their first article?

Question

How do I write a LINQ query that would translate to the following SQL?

SELECT u.id, u.username, a.id, MIN(a.created_at) AS firstArticle
FROM users u
INNER JOIN editorial_articles a
ON a.user_id = u.id
GROUP BY u.id
ORDER BY u.id, a.created_at, a.id

Basically, a list of users with their first article.

Everything that I try results in an incorrect group-by clause (too many columns), not enough columns selected, or some other issue.

I've tried a thousand different combinations – why is this so difficult?

Classes:

[Table("users")]
public class User
{
    [Column("id", IsPrimaryKey = true, IsIdentity = true, SkipOnInsert = true, SkipOnUpdate = true)]
    public int Id { get; set; } // int

    [Column("username", CanBeNull = false)]
    public string Username { get; set; } = null!; // varchar(20)

    [Association(ThisKey = nameof(Id), OtherKey = nameof(Article.UserId))]
    public IEnumerable<Article> Articles { get; set; } = null!;
}

[Table("articles")]
public class Article
{
    [Column("id", IsPrimaryKey = true, IsIdentity = true, SkipOnInsert = true, SkipOnUpdate = true)]
    public int Id { get; set; } // int
    
    [Column("user_id")]
    public int UserId { get; set; } // int
    
    [Column("created_at")]
    public DateTime CreatedAt { get; set; } // datetime

    [Association(CanBeNull = false, ThisKey = nameof(UserId), OtherKey = nameof(User.Id))]
    public User User { get; set; } = null!;
}

Update:

The closest that I get with a GROUP BY is:

var result =
    from user in db.Users
    join article in db.Articles
    on user.Id equals article.UserId into articles
    from article in articles
    group article by new { user } into g
    orderby g.Key.user.Id, g.Min(a => a.CreatedAt), g.Min(a => a.Id)
    select new
    {
        User = g.Key,
        FirstArticle = g.Min(a => a.CreatedAt)
    };

I don't like that it puts all of the user fields into the group-by. If you just group by Id, then it's not possible to reference the initial user in the select.

Is there no way to group by ID, but select additional fields?

Answer 1

Use Window Function ROW_NUMBER for such task:

var query = 
    from u in db.Users
    from a in u.Articles
    select new
    {
        User = u,
        FirstArticle = a,
        RN = Sql.Ext.RowNumber().Over()
            .PartitionBy(u.Id)
            .OrderBy(a.CreatedAt)
            .ToValue()
    } into s
    where s.RN == 1
    select new 
    {
        s.User,
        s.FirstArticle
    };