CODEWITHSUNDEEP
c#entity-frameworklinq
StepUp
StepUp

Reputation: 38189

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!

Upvotes: 1

Views: 456

Answers (2)

Mohammad Akbari
Mohammad Akbari

Reputation: 4776

Try this:

var result = db.Client.Select(client => new SearchViewModel
{
    Name = client.Name,
    AccountNumber = client.Account.Select(account => account.Name).ToList()
}).ToList();

Upvotes: 1

Gilad Green
Gilad Green

Reputation: 37271

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

Upvotes: 2

Related Questions