This should be a really simple problem to solve, but for some reason Entity Framework makes it really hard for me. I just need to do a simple left join...
I have two tables. For example, a user table:
user_id | name | fk_group_id
And a group table
group_id | groupname
In raw SQL:
SELECT * FROM users AS u LEFT JOIN groups AS g ON u.fk_group_id = g.group_id WHERE groupname = 'my group'
Super easy in SQL. But when i search for answers on how to do this is Entity Framework, then there's one thing that immidiately goes through my mind... WTF...!?!? Super large constructed, weird formatted "queries" are performed and i'm really confused what i need and what not...
So i'm hoping someone could help me with my specific problem. How would one rewrite the above SQL query in (LINQ?) Entity Framework.
Currently i have this:
var bla = (from m in myEnt.Users // My join attempt.. join mp in myEnt.Groups on m equals mp.group_id into n where n.group_name == "something" select m);
Even if this was working, i really don't see how this is suppose to make my life easier.. Oo
Anyway, i really hope someone can help me out :-)
Umm, you don't have to explicitly do that with any ORM. That's the catch here, that's what mapping's for.
You can just go:
var blah = myEnt.Users.Where(user => user.Group.Name == "something");
Or in the alternate syntax
from usr in myEnt.Users where usr.Group.Name == "something" select usr`
If it's many-to-one relationship after mapping to objects, then every
User entity will have the navigation property to his
Group, and every
Group will have a
User collection. Then you only have to filter the results appropriately.