Consider the following:

Using Linq I want to select all the roles for a particular user. I could create a query like this:

 

public List<Role> GetByUserID(int userID)
{
    List<Role> roles = (from r in Context.Roles
                       join ur in Context.UserRoles on r.ID equals ur.RoleID
                       where ur.UserID.Equals(userID)
                       select r).ToList();

    return roles;

}

This simply creates the following SQL:

SELECT [t0].[RoleID] AS [ID], [t0].[Name]
FROM [dbo].[Role] AS [t0]
INNER JOIN [dbo].[UserRole] AS [t1] ON [t0].[RoleID] = [t1].[RoleID]
WHERE [t1].[UserID] = @p0

Alternatively I could use the Any extension method.

public List<Role> GetByUserID(int userID)
{
    return Context.Roles.Where(r => r.UserRoles.Any<UserRole>(ur => ur.UserID.Equals(userID))).ToList();
}

The Any method returns a bool which indicates if any of the UserRoles meet the lambda expression; in this case checking the UserID property. The Where method evaluates the Roles against the Roles the User belongs to so we only get those Roles.

The resulting SQL looks like this:

SELECT [t0].[RoleID] AS [ID], [t0].[Name]
FROM [dbo].[Role] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[UserRole] AS [t1]
    WHERE ([t1].[UserID] = @p0) AND ([t1].[RoleID] = [t0].[RoleID])
    )