How to join multiple tables?

code-first entity-framework lambda linq-to-sql

Question

I have the following classes. I have a object var of Description class. I want to select Balance related to the Client provided in the var object using Linq to Sql or Lambda expression. How to join these tables to get the Balance from Account table?

public class Description
    {
        public int DescriptionID { get; set; }

       // Attributes

        public int ClientID { get; set; }

        [ForeignKey("ClientID")]
        public virtual Client Client { get; set; }


    }

public class Client
    {
        public int ClientID { get; set; }

       // Attributes

        public int UserID { get; set; }

        [ForeignKey("UserID")]
        public virtual User User { get; set; }

    }

 public class User
    {
        public int UserID { get; set; }

       // Attributes

     }

 public class Account
    {

        public int AccountID { get; set; }

        [Required, Column("Balance"), Display(Name = "Account Balance")]
        public double Balance { get; set; }


        public int UserID { get; set; }

        [ForeignKey("UserID")]
        public virtual User User { get; set; }

    }
1
7
6/26/2012 9:28:21 AM

Accepted Answer

You could try this:

var balance = (from a in context.Accounts
               join c in context.Clients on a.UserID equals c.UserID
               where c.ClientID == yourDescriptionObject.ClientID
               select a.Balance)
              .SingleOrDefault();

Or - if you only have the DescriptionID:

var balance = (from a in context.Accounts
               join c in context.Clients on a.UserID equals c.UserID
               join d in context.Descriptions on c.ClientID equals d.ClientID
               where d.DescriptionID == yourDescriptionID
               select a.Balance)
              .SingleOrDefault();

(Or FirstOrDefault() or ToList() or Sum()? Because your model would allow that clients/descriptions are related to multiple accounts ...)

13
6/26/2012 11:54:35 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow