title: Linq Query - how can you improve the following query?
body: <p>I have a Linq To Sql query in my asp.net website.</p>
  
  <p>This query works too <strong>Slow</strong> and im sure that it can be improved.</p>
  
  <p><strong>Can anyone help me ReWrite this query?</strong> </p>
  
  <p>I assume using "join" would make it quicker but couldn't get it right. :(</p>
  
  <hr>
  
  <p>Here is the query: (input params : INT_GENDER )</p>
  
  <pre><code>var users = from u in db.Users
              where (u.gender == INT_GENDER) &amp;&amp; (u.age &gt; 25)
              let fileId = (from f in db.Files 
                           where f.username == u.username &amp;&amp; f.approved 
                           orderby f.primary 
                           select f.id).FirstOrDefault()
              let answer = (from a in db.Answers
                             where (a.username == u.username) &amp;&amp; 
                                   (a.q_id == (from q in db.Questions where q.type == 1
                                                    select q.id).FirstOrDefault()) &amp;&amp;
                                    a.approved
  
                            select a).FirstOrDefault()
  
  select new { 
  
      Username = u.u_username,
      FileId = fileId !=null ? fileId : GetEmptyFileId(),
      Answer = (answer == null ? "" : (answer.approved ? answer.value: "Empty"))
  };
  </code></pre>
  
  <p>Query is based on 3 tables. 
  Tables : 
  1. Users 
  2. Files 
  3. Answers</p>
  
  <ul>
  <li>Username column in the users table is identity.</li>
  <li>Each user can have many or none Files</li>
  <li>Each user can have many or none Answers.</li>
  </ul>
  
  <p><strong>Thank you!
  Dan</strong></p>