c#之连接两个表以使用嵌套集合填充 viewModel

我有两个表 Client 和 Account:

public partial class Client 
{ 
    public Client() 
    { 
        this.Account = new HashSet<Account>(); 
        this.Doc = new HashSet<Doc>(); 
    } 
 
    public int ClientId { get; set; } 
    public string Name { get; set; } 
 
    public virtual ICollection<Account> Account { get; set; } 
    public virtual ICollection<Doc> Doc { get; set; } 
} 

和

public partial class Account 
{ 
    public Account() 
    { 
        this.Doc = new HashSet<Doc>(); 
    } 
 
    public int AccountId { get; set; } 
    public string Name { get; set; } 
    public int Fk_ClientId { get; set; } 
 
    public virtual Client Client { get; set; } 
    public virtual ICollection<Doc> Doc { get; set; } 
} 

数据看起来像这样:

客户:

ClientID     Name 
   1         Ben 
   2         Joseph 

账号:

AccountID     Name             Fk_ClientId 
   1         BenAccount1           1 
   2         BenAccount2           1 
   3         JosephAccount1        2 
   4         JosephAccount2        2 
   5         JosephAccount3        2 

我编写了以下查询:

var query = from clnt in db.Client 
                        join acnt in db.Account  
                             on clnt.ClientId equals acnt.Fk_ClientId 
                        select new SearchViewModel 
                        { 
                            Name = clnt.Name, 
                            AccountNumber = //don't know what I should write here 
                        }; 

结果应该是这样的:

我的 viewModel 是 SearchViewModel:

public class SearchViewModel 
{ 
    public string Name { get; set; } 
    public IList<string> AccountNumber { get; set; }         
} 

如果我手动创建 SearchViewModel 的实例，我可以模仿结果:

List<SearchViewModel> searchVM = new List<SearchViewModel>() 
{  
   new SearchViewModel(){Name="Ben", AccountNumber = new List<string>() { "BenAccount1",  
           "BenAccount2" } },  
   new SearchViewModel(){Name="Joseph", AccountNumber = new List<string>() {   
           "JosephAccount1", "JosephAccount2", "JosephAccount3" } },                     
}; 

如何使用linq得到这样的结果？
任何帮助将不胜感激!

请您参考如下方法:

您正在寻找的是 Groupjoin 方法。 在查询语法中它看起来像这样:

var query = from clnt in db.Client 
            join acnt in db.Account on clnt.ClientId equals acnt.Fk_ClientId into accounts 
            select new SearchViewModel 
            { 
                Name = clnt.Name, 
                AccountNumber = accounts.Select(account => account.Name) 
            };