Return data from 2 tables using Entity Framework
I am working with MVC3 and Entity Framework, but I have come to a point where I need more data from different tables. I used to 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 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 will give me all my users.
But a user can join a group and I need to get all usernames from a specific group.
Table: userGroups
id
fk_user_id
fk_group_id
Now if I were to 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 for some reason I also need the username from the Users table. How can I get this data and still return my "custom groups" as IEnumerable?
In SQL, I was just doing a LEFT JOIN, but I can't figure out how it works here.
source to share
Something like this could be:
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 using 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, });
source to share
The above requests will require you to change your method signature, which can be very hard work depending on where you configured this setting. Arion, in particular, pretty much completely mimics the left join behavior you're talking about (which is great because you know what the Entity does), but you'll need to change your return type to Tuple<userGroups, Users>
or something like that.
Instead, you can update the userGroups poco to include the nav property in the table Users
. If I understand your posted question correctly, you have a one-to-many relationship that exists here. In this case, you change poco like this:
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 posted in the original question are not what Entity considers to be a normalized naming, so you might need to use data annotations as described. Ctrl-F "ForeignKey", if you are having trouble finding it, this is kind of a big flow of information for data annotations in general.
The advantage is that if you link to this, you don't have to worry about joining again. You can simply access the collection of users in custom groups, and everything will be available, combined and worked out for you.
source to share