Linq to 实体:一个选项中的多对多

本文关键字：选项 一个 实体 to Linq | 更新日期: 2023-09-27 18:32:40

我有这个域：

public class User
{
    public long Id { get; set; }
    public string Login { get; set; }
    public string Password { get; set; }
}
public class Application
{
    public long Id { get; set; }
    public string Name { get; set; }
}
public class UserApplications
{
    [ForeignKey("User")]
    public long UserId { get; set; }
    public User User { get; set; }
    [ForeignKey("Application")]
    public long ApplicationId { get; set; }
    public Application Application { get; set; }
    public DateTime LastConnection { get; set; }
}

我想做一个返回类似以下内容的选择：

List of select new
{
    User = user,
    Applications = applications // List of all user's applications
}

我尝试：

from u in Users
join ua in UserApplications on u.Id equals ua.UserId into userApplications
from ua in userApplications.DefaultIfEmpty()
join a in Applications on ua.ApplicationId equals a.Id into applications
select new
{
    User = u,
    Applications = applications
}

但这会使用户重复到每个应用程序。

我知道我可以在两个选择语句中做到这一点，但我不希望那样。

我该怎么做？

Linq to 实体:一个选项中的多对多

我不记得实体框架是否可以基于实体对象本身执行groupby（并将其Id幕后提取并替换适合的东西等）; 但是此代码适用于这种情况：

var q = from uapp in cntxt.UserApplications
        group uapp by uapp.UserId
            into g
            select new { UserId = g.Key, Applications = g.Select(x => x.Application) };

如果您愿意已经提取User：

var q2 = from uapp in cntxt.UserApplications
            group uapp by uapp.UserId
                into g
                let u = Users.First(x => x.Id == g.Key)
                select new { User = u, Applications = g.Select(x => x.Application) };

假设您正在编写针对实体框架上下文的查询 - 而不仅仅是尝试执行 Linq 到对象查询。

实际上，您只需按用户设置UserApplications实体进行分组：

context
    .UserApplications
    .GroupBy(_ => _.User, _ => _.Application)
    .ToList();

因为，事实上，IGrouping<User, Application>是你需要的（Key是用户，组项目是他的应用程序）。

任何其他改进都是品味问题，例如投影到匿名类型：

context
    .UserApplications
    .GroupBy(_ => _.User, _ => _.Application)
    .Select(_ => new
    {
        User = _.Key,
        // since IGrouping<User, Application> is IEnumerable<Application>,
        // we colud return a grouping directly
        Applications = _  
    })
    .ToList();

（另一个投影选项在Applications中丢弃组键）：

context
    .UserApplications
    .GroupBy(_ => _.User, _ => _.Application)
    .Select(_ => new
    {
        User = _.Key,
        Applications = _.Select(app => app)
    })
    .ToList();

试试这个：

var tmp =
    from u in Users
        join ua in UserApplications on u.Id equals ua.UserId
        join a in Applications on ua.ApplicationId equals a.Id
    select new
        {
            User = u,
            App = a
        };
var res = tmp
    .ToArray() // edited
    .GroupBy(_ => _.User)
    .Select(_ => new 
        {
            User = _.Key, 
            Applications = _.Select(_ => _.App).ToArray()
        });
相关文章：