Return data from 2 tables with Entity Framework

Question

I'm working with MVC3 and Entity Framework but i came to a point where i need more data from different tables. Usually i'd do something like this to get data from a table:

Table: Users
id
username

In code i would do something like this to get all the users:

public static IEnumerable<Users> GetUsers( int userId )
{
    MyEntities ent = new MyEntities();

    return from g in ent.Users
           where g.OwnerUserId == userId
           select g;
}

So this would give me all my users back.

---

But a user can join a group, and i have to get all the usernames from a specific group.

Table: userGroups
id
fk_user_id
fk_group_id

Now if i'd use this code:

public static IEnumerable<userGroups> GetUsersFromGroup( int groupId )
{
    MyEntities ent = new MyEntities();

    return from g in ent.userGroups
           where g.OwnerUserId == userId
           select g;
}

Now obviously this only returns me the data from the "userGroups" table. But somehow i also need the username from the Users table. How can i get that data too and still return my "userGroups" as an IEnumerable?

In SQL i'd simply do a LEFT JOIN, but i can't really figure out how that works here.

Answer 1

Something like this maybe:

var query = from g in ent.userGroups
            join u in ent.Users on g.fk_user_id equals u.userID
            select new { g, u, });

Or with a LEFT JOIN

var query = (from g in ent.userGroups
             from u in ent.Users.Where(a => a.fk_user_id == u.userID).DefaultIfEmpty()
             select new { g, u, });