c# - Adding joins and selects to a linq-to-sql lambda expression -

i have following 3 generated classes trying write linq-to-sql query against dynamically add joins based on settings:

public class user {     public int userid { get; set; }     public string username { get; set; }     // ... }  public class useremail {     public int userid { get; set; }     public string address { get; set; }     // ... }  public class userphone {     public int userid { get; set; }     public string phonenumber { get; set; }     // ... } 

this ideal execute against database, fine retrieving columns joined tables:

// if includeemail , includephone both true. select  u.*, ue.address, up.phonenumber    users u join    user_email ue on u.user_id = ue.user_id join    user_phone on u.user_id = up.user_id  // if includeemail , includephone both false. select  u.*    users u 

and here code working with:

// base query var queryableusers = m_context.users.where(u => u.userid == 1).select(u => u);  // join other tables based on settings if (includeemail) {     expression<func<useremails, useremails>> emailselector = (ue => ue.address);     queryableusers = queryableusers.join(m_context.useremails, u => u.userid, ue => ue.userid).select(emailselector);  } if (includephone) {     expression<func<userphones, userphones>> phoneselector = (up => up.phonenumber);     queryableusers = queryableusers.join(m_context.userphones, u => u.userid, => up.userid).select(phoneselector);  }  // execute query var results = queryableusers.tolist(); 

the problem you'll face you're trying change type of queryableusers. you're attempting:

iqueryable<user> queryableusers = originalquery;  if(includeemail)     queryableusers = iqueryable<useremails>; if(includephone)     queryableusers = iqueryable<userphones>; 

i don't know exact situation, don't need decide when include 1 or (this fast query, in case clarity , ease of use preferred) , query so:

var userdata = (from u in m_context.users                 u.userid == 1                 select new                 {                     user = u,                     emailaddress = u.useremail.address,                     phonenumber = u.userphone.phonenumber                 }).tolist(); 

or, if want use entities directly, can use dataloadoptions eagerly load related entities when necessary:

dataloadoptions options = new dataloadoptions();  if(includeemail)     options.loadwith<user>(u => u.useremail);  m_context.loadoptions = options;  var userdata = m_context.users.where(u => u.id == 1)                               .tolist();  foreach(var user in userdata) {     // if includeemail == true, not trigger db call     // if includeemail == false, useremail property lazily     // loaded , db call made every iteration of     // loop (very bad).     console.writeline(user.useremail.address); }