Multiple join query in eSql

This is my first question in stackoverflow but really not the first time to get solution here. I am struggling with multiple join in entity framework 4. I have three tables (Accounts, Users and AccountUsers) that am finding difficult to query.

What I want is to get all the users for the provided accountId including the account creator. I can get all the account users with no problem but the hard side for me is getting the account creator since it's not added to AccountUsers table. below is a quick preview how the tables are related.

Accounts

• AccountId
• UserId (account creator)
• ...other columns

Users

• UserId
• ...other columns

AccountUsers

• AccountId
• UserId

I would prefer the query to be esql, but Linq to Entities will do.

I trust you guys on stackoverflow, so I know this won't take long to 开发者_如何学Cget the 'Answer' mark.

Thanks for even reading.

If I'm reading your question right, you're not looking for a join so much as a union? I'm not sure about esql, but I'd think the following Linq query should work. Maybe it will get you going in the right direction (or I could be completely off-base or missing something):

var users = (from accountUser in db.AccountUsers
             where accountUser.AccountId == myAccountId
             select accountUser.UserId)
            .ToList()
            .Add((from account in db.Accounts
                  where account.AccountId == myAccountId
                  select account.UserId)
                 .Single());

To make it lazy loading, you could always make a method that makes use of foreach and yield return:

IEnumerable<int> GetAccountUsers(int AccountId)
{
    //return the users
    foreach(var userId in (from accountUser in db.AccountUsers
                           where accountUser.AccountId == myAccountId
                           select accountUser.UserId))
        yield return userId;

    //return the owner too
    yield return (from account in db.Accounts
                  where account.AccountId == myAccountId
                  select account.UserId)
                 .Single();
}

Out of curiosity, though, why is the owner not also added to the AccountUsers table?