CODEWITHSUNDEEP
linq-to-entities
Brent
Brent

Reputation: 4876

Linq to Entities query - find all records with same field value

I am using the following Linq to Entities Query, querying once on a unique field and then finding all records matching on a seperate (foreign key) property:

public IEnumerable<User> getUsersInSameCentreAs(String userName)
{
    int? studyCentreId = (from u in _db.Users
                         where u.UserName == userName
                         select u.StudyCentreID).FirstOrDefault();
    return (from u in _db.Users
            where u.StudyCentreID == studyCentreId
            select u).ToList();
    }

There must be a clean/single query which will only query the database once to obtain the same result, but my knowledge of linq to entities is letting me down - can anyone see a better solution. Thank you.

Upvotes: 0

Views: 888

Answers (1)

Laszlo Boke
Laszlo Boke

Reputation: 1329

A subquery will solve this:

return (from u in _db.Users
        where u.StudyCentreID == (from u2 in _db.Users
                     where u2.UserName == userName
                     select u2.StudyCentreID).FirstOrDefault();
        select u).ToList();

Upvotes: 1

Related Questions