LINQ and subqueries

How would I do the following in LINQ?

select fkUniqueID
from tblUserRights
开发者_如何学编程where fkUniqueID =
(select PkUserID
from Users
where UserID = 'mike')

I assume you're using LINQ to SQL or something similar.

Ideally, with a join:

var query = from user in db.Users
            where user.UserID == "mike"
            join userRight in db.UserRights
              on user.PkUiserID equals userRight.FkUniqueID
            select userRight;

Alternatively, if you really want to use a subquery:

var mikeIDs = from user in db.Users
              where user.UserID == "mike"
              select user.PkUserID;

var query = from userRight in db.UserRights
            where mikeIDs.Contains(userRight.fkUniqueID)
            select userRight;

(Note that LINQ is lazy, so this won't actually execute a SQL query for the first part.)

Of course, if you've got your relationships set up, you can just use:

var rights = db.Users.Single(u => u.UserID == "mike").UserRights;

... that will go bang if there's no such user though, and it'll probably make two DB queries.

Something along the lines of this pseudo-LINQ should work (it's practically a translation of the problem)

var uniqueIDs = from userRight in tblUserRights
                where fkUniqueID = (from user in Users 
                                    where user.UserID = 'mike' 
                                    select user.pkUserID).First()
                select userRight.fkUniqueID 

In SQL we do not have to use anything like the First() construct (a Top 1 would be a SQL equivalent) because SQL will try to work with us, working correctly if there is only one record (or no records at all), and throwing a descriptive error if there are two or more records.

In C#, that would be translated to a compile-time error, since we are comparing with a T, and the query returns a IEnumerable<T>.
First() explicitly returns the first T in the collection, or throws an error if there are no elements in the result.