Making a LINQ query better

Question

I'm having a hard time getting the LINQ-syntax.. How can I do this command in a better way?

var user = (from u in context.users
            where u.email.Equals(email)
            select u).Single();
var pinToUser = (from ptu in context.pintousers
                 where ptu.user_id.Equals(user.id)
                 select ptu).Single();
var pin = (from p in context.pins
           where p.idpin.Equals(pinToUser.pin_idpin)
           select p).Single();

return pin;

As you can see, there's a table user, a table pintouser and a table pin. Pintouser references user and pin. Is it possible to write something short like "user.pintouser.pin"? I think I have the navigation properties all set up but I'm not sure how to use them properly or if I could make them better by modifying them.

Thanks for reading

Answer 1

Use joins to rewrite everything as a single clean query. If I read your queries properly, this should give you the correct result:

var pin = (from u in context.users
          join ptu in context.pintousers on u.id equals ptu.user_id
          join p in context.pins on ptu.pin_idpin equals p.idpin
          where u.email == email
          select p).Single();

Keep in mind, though, that if this query returns anything other than a single result your code will throw an Exception.

If you want to handle the possibility of getting one or no rows then you should use SingleOrDefault().

If you want to handle the possiblity of getting any number of rows then you should really use FirstOrDefault().

Answer 2

You should be using join, as @JustinNiessner points out, but this is another way to write your query.

var user = context.users.Single(u => u.email == email);
var pinToUser = context.pintousers.Single(ptu => ptu.user_id == user.id);
var pin = context.pins.Single(p => p.idpin == pinToUser.pin_idpid);

Answer 3

Since you have navigation properties, might as well use them:

Pin pin =
(
  from u in context.Users
  where u.email == email
  from ptu in u.pintousers
  let p = ptu.pin
  select p
).Single();

Answer 4

Note that if you have your foreign-key relationship set righ in your database, Linq-to-Sql should have the joins for you automatically:

var pin = (from u in context.users 
      where u.email == email 
      select u.pintouser.pin).Single(); 

which means you can reduce this to:

var pin = context.users.Where(u=>u.email == email)
                       .Select(u=>u.pintouser.pin)
                       .Single();

(UPDATE Note: I had originally suggested the following, which is much shorter, but I believe it will cause two round-trips to the database)

var pin = context.users.Single(u=>u.email == email).Single().pintouser.pin;

Now, the .pintouser.pin is safe, because the Single() will always return a user object (or throw an exception).