LINQ query enhancement

Question

I find it difficult to get LINQ syntax. How can I make this team better?

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 is a table user, a table column and a column. Pintouser refers to the user and pin. Is it possible to write something short like "user.pintouser.pin"? I think I have all the navigation features, but I'm not sure how to use them correctly, or if I can improve them by changing them.

Thanks for reading

Answer 1

Use joins to rewrite everything as one clean request. If I read your queries correctly, this should give 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 that if this query returns anything other than one result, your code will throw an exception.

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

If you want to handle the ability to get any number of rows, you should really use FirstOrDefault() .

Answer 2

Note that if you have a foreign key relationship established in your database, Linq-to-Sql should automatically join you:

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

which means you can reduce this:

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

(UPDATE Note: I initially suggested the following, which is much shorter, but I believe that this will lead to two back trips to the database)

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

Now .pintouser.pin is safe, because Single() always returns a user object (or throws an exception).

Answer 3

You should use join as @JustinNiessner points out, but this is another way to write your request.

 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 4

Since you have navigation properties, they can also 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();