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

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?

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
    };