英文:
How do I write a LINQ query for a list of users with their first article?
问题
Here's the LINQ query that corresponds to the provided SQL query:
var query =
from u in db.Users
join a in db.Articles on u.Id equals a.UserId
group new { u.Id, u.Username, a.Id, a.CreatedAt } by u.Id into g
orderby g.Key
select new
{
UserId = g.Key,
Username = g.First().Username,
FirstArticleId = g.OrderBy(x => x.CreatedAt).ThenBy(x => x.Id).First().Id,
FirstArticleCreatedAt = g.OrderBy(x => x.CreatedAt).ThenBy(x => x.Id).First().CreatedAt
};
This query should provide the desired result, grouping by the UserId and selecting the additional fields Username, FirstArticleId, and FirstArticleCreatedAt for each user's 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?
答案1
得分: 2
使用窗口函数 ROW_NUMBER 来执行这个任务：
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
};
英文:
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
};
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论