CODEWITHSUNDEEP
asp.net-mvclinqasp.net-mvc-4linq-to-sqllambda
Elvin Mammadov
Elvin Mammadov

Reputation: 27387

Select multiple table with Linq to Sql

I have two tables. There is one-to-many relationship between these tables.I want to select Company table and BankAccount List table (for appropriate CompanyID). How can I do it with Linq-to-Sql?

public class Company
{
    // My Fields
    [Key]
    public Guid ID { get; set; }


    public string FullName { get; set; }

    // My virtual properties FOR relationships(one-to-one,one-to-many etc.).


    public virtual List<BankAccount> BankAccounts { get; set; }
}

and

public class BankAccount
{

    // My Fields
    //[ScaffoldColumn(false)]
    [Key]
    public Guid ID { get; set; }

    [ForeignKey("Companies")]
    public Nullable<Guid> CompanyID { get; set; }

    public string BankName { get; set; }




    // My virtual properties FOR relationships(one-to-one,one-to-many etc.).

    public virtual Company Company { get; set; }
}

I write this as follow, but I didn't like it

List<List<BankAccount>> bankaccounts = new List<List<BankAccount>>();
foreach (var comp in companyRepository.Companies)
{
      List<BankAccount> banks = new List<BankAccount>();
      foreach (var bank in bankRepository.BankAccounts)
      {
          if (comp.ID == bank.CompanyID)
          {
              banks.Add(bank);
          }
      }
      bankaccounts.Add(banks);
      banks = new List<BankAccount>();
}

Upvotes: 2

Views: 1154

Answers (1)

shakib
shakib

Reputation: 5469

I think the following will yield the same result.

var bankaccounts = companyRepository.Companies.Select(c => c.BankAccounts)
                       .ToList();

If you are using entity framework, you can eagerload the 'BankAccounts' property to minimize DB calls.

Hope this helps.

Upvotes: 2

Related Questions