Linq Query - how can you improve the following query?

Question

Hi guys,

I have a Linq To Sql query in my asp.net website.

This query works too Slow and im sure that it can be improved.

Can anyone help me ReWrite this query?

I assume using "join" would make it quicker but couldn't get it right. :(

---

Here is the query: (input params : INT_GENDER )

var users = from u in db.Users
            where (u.gender == INT_GENDER) && (u.age > 25)
            let fileId = (from f in db.Files 
                         where f.username == u.username && f.approved 
                         orderby f.primary 
                         select f.id).FirstOrDefault()
            let answer = (from a in db.Answers
                           where (a.username == u.username) && 
                                 (a.q_id == (from q in db.Questions where q.type == 1
                                                  select q.id).FirstOrDefault()) &&
                                  a.approved

                          select a).FirstOrDefault()

select new { 

    Username = u.u_username,
    FileId = fileId !=null ? fileId : GetEmptyFileId(),
    Answer = (answer == null ? "" : (answer.approved ? answer.value: "Empty"))
};

Query is based on 3 tables. Tables : 1. Users 2. Files 3. Answers

• Username column in the users table is identity.
• Each user can have many or none Files
• Each user can have many or none Answers.

Thank you! Dan

Answer 1

Here is my modest attempt to decipher this in plain T-SQL. Hope this helps.

SELECT u.username, f.id, a.value, a.approved
FROM users AS u
     JOIN files AS f ON u.username = f.username
     JOIN answers AS a ON a.username = u.username
     JOIN questions AS q ON a.q_id = q.id      
WHERE u.gender = @INT_GENDER
      AND u.age > 25
      AND f.approved = 1
      AND a.approved = 1
      AND q.type = 1