Return data from 2 tables with Entity Framework

.net c# 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.

1
6
4/2/2012 2:15:46 PM

Accepted Answer

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, });
7
4/2/2012 2:16:49 PM

Popular Answer

var query = from ug in ent.userGroups
            join u in ent.Users on ug.OwnerUserId = ug.userID
            select new
            {
                Name = u.UserName,
                Id = u.userID
                Group = ug.GroupName
            };

If you need left join then you would require DefaultIfEmpty.

Please check the following articles:



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow