StepUp StepUp - 3 months ago 13
C# Question

Join two tables to populate a viewModel with a nested collection

I have two tables

Client
and
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; }
}


and

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; }
}


Data looks like this:

Client:

ClientID Name
1 Ben
2 Joseph


Account:

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


I've written the following query:

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
};


The result is should be look like this:
enter image description here

My viewModel is
SearchViewModel
:

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


I can imitate result, if I create instance of
SearchViewModel
manually:

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" } },
};


How to make a such result using
linq
?

Any help would be greatly appreciated!

Answer

what you are looking for is the Groupjoin method. In query syntax it looks like this:

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)
            };