Linq,EF Core-对一个字段进行分组，并使用其他字段从其他表中获取数据列表

Question

    Id T_Id
    4   2 
    6   5
    6   7

    T_Id  fname lname
    2     mary   smith
    5     john    pope
    7     steve   blair

    public class SomeView
    {
       public int Id {get; private set;}
       public int T_Id { get; private set; }

       public User User { get; set; } //can't navigate here
    }

    public class User
    {
       public int T_Id { get; set; }
       public string fname {get; set;}
       public string lname{get; set;}

       public SomeView SomeView{ get; set; } //can't navigate here
    }

由于SomeView是SQL视图，所以数据库中没有定义外键约束。不能将导航与QueryType一起使用。所以用户和SomeView之间的映射是不可能的，或者我不知道该怎么做。

     public class SomeViewModel
     {
        public int Id { get;  set; }
        public List Users{get; set;}
     }

finally my linq in progress - 

    from t in SomeView
    group new {t} by t.Id into grp
    select new SomeViewModel{
       Id = grp.Key,
       Users = grp.Select(x => x.t.User).ToList()
       //need help here to get Users based on T_Id
      }

最终的API数据输出应该是以下格式。

[{
  "Id" : "4",
  "users":[{
         "T_Id": 2,
         "fname": "mary",
         "lname": "smith"
      }]
},
{
  "Id" : "6",
  "users":[{
         "T_Id": 5,
         "fname": "john",
         "lname": "pope"
      },
      {
         "T_Id": 7,
         "fname": "steve",
         "lname": "blair"
      }
      ]
}]

Answer 1

<罢工> 首先，我认为您可以使用任何序列化程序将对象转换为您想要的格式。

var serialized = JsonConvert.SerializeObject(data)

第二，回到你的问题，这里是代码。但是，您需要在变量周围添加“为了可读性，去掉我添加的字符串连接”。此外，这段代码是针对您的问题的，为了更通用的解决方案，请使用第一种方法。

var mainData = string.Join(',', data.Select(x => $" {{ {nameof(x.Id)} :  {x.Id}, " +
                                                           $"{nameof(User)}s: " +
                                                           $"[ {string.Join(',', x.Users.Select(y => $"{{ {nameof(User.T_Id)} : {y.T_Id} }}"))}]" +
                                                           $"}}"));
var result = $"[{mainData}]" ;

<罢工>

        var someViewsUsersJoin = someViews.Join(users, l => l.t_id, r => r.t_id, (someView, user) => new {someView, user});

        var result = someViewsUsersJoin.GroupBy(x => x.someView.id).Select(x => new SomeViewModel()
        {
            Id = x.Key,
            Users = x.Select(y => y.user).ToList()
        });