Question 177573 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I write a LINQ query for a list of users and their first article in MySQL?

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!;
}
Tags:

2

Answers


  1. 0

    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
    };
    Login or Signup to reply.
  2. 0

    Try Efcore query:

    _context.Users
     .Include(u => u.Articles)
     .GroupBy(u => u.Id, (user, article) => new
     {
        User = user,
        FirstArticle = article.Min(a => a.CreatedAt)
     })
     .OrderBy(res => res.User)
     .ThenBy(res => res.FirstArticle.CreatedAt)
     .ThenBy(res => res.FirstArticle.Id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search