CODEWITHSUNDEEP
c#.netentity-framework
w00
w00

Reputation: 26772

Return data from 2 tables with Entity Framework

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.

Upvotes: 6

Views: 22479

Answers (3)

tmesser
tmesser

Reputation: 7656

The above queries are going to require you to change your method signature, which could be a lot of really painful work depending on where you have this set up. Arion's in particular pretty much totally mimics the left join behavior you're talking about (which is great, because you know what Entity is doing), but you will need to change your return type to a Tuple<userGroups, Users> or something of that nature.

What you may try instead is to update the userGroups poco to include a nav property to the Users table. If I'm understanding your posted question properly, you have a one-to-many relationship that exists here. In this case, you would change the poco as follows:

public class userGroups
{
    public int ID { get; set; }
    public string GroupName { get; set; }
    public virtual ICollection<Users> Users { get; set; }
}

public class Users
{   
    public int ID { get; set; }        
    public string Name { get; set; }
    public virtual userGroups UserGroup { get; set; }
}

However, the names you've posted in your original question are not what Entity considers normalized naming, so you may need to use data annotations as described here. Ctrl-F "ForeignKey" if you're having some trouble finding it, it's kind of a big infodump on data annotations as a whole.

The benefit is, if you link in like this you will never have to worry about joining again. You can simply access the Users collection on userGroups and it will be accessed, joined, and worked out all for you.

Upvotes: 2

Habib
Habib

Reputation: 223267

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:

Upvotes: 3

Arion
Arion

Reputation: 31239

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, });

Upvotes: 7

Related Questions