Convert Linq To Sql with JOIN to IQueryable select.

Question

I have a function called GetUserByOpenId I don't want to be using this function at all

    Public Function GetUserByOpenID(ByVal claimedidentifier As String) As User Implements IUserRepository.GetUserByOpenID
        Dim user = (From u In dc.Users
                    Join o In dc.OpenIDs On u.ID Equals o.UserID
                    Where o.ClaimedIdentifier = claimedidentifier
                    Select u).FirstOrDefault
        Return user
    End Function

What I really want to be able to do is use my "GetUsers" function (IQueryable) and do the JOIN in my service layer.

    Public Function GetUsers() As IQueryable(Of User) Implements IUserRepository.GetUsers
        Dim users = (From u In dc.Users
                    Select u)
        Return users.AsQueryable
    End Function

Can anybody tell me what the method would look like to return the appropraite data using a similar function to this

    Public Function GetAllUsers() As System.Collections.Generic.IList(Of User) Implements IUserService.GetAllUsers
        Return _UserRepository.GetUsers().Where(Function(u) (Not u.isClosed)).ToList
    End Function

Answer 1

Hmm it seems like you should have a FK relationship between OpenIDs and Users but you don't. If you create this relationship you will be able to easily traverse it via property and not have to do querying at all (besides a simple Where).