linq select m:n user:groups

Question

Hi guys,

I've got three tables:

cp_user (id, name)
cp_group (id, name)
cp_usergroup (user_id, group_id)

• the classical m:n stuff.

Assume the following Data:

cp_user
1, Paul
2, Steven

cp_group
1, Admin
2, Editor

cp_usergroup
1, 1
1, 2
2, 2

So Paul is in the Admin AND Editor group, while Steven is just in the Editor group. I want to generate a list like that from the database:

Paul Admin
Paul Editor
Steven Editor

Any suggestions?

Thanks! Clemens

Answer 1

A friend of mine just helped me out on this:

var q = db2.cp_users.SelectMany(u => u.cp_groups.Select(g => new { Username = u.name, Groupname = g.name }));

Works fine for me. Is there anyway to do this in query syntax?

Answer 2

In query syntax:

from u in db2.cp_users
from g in u.cp_groups
select new { Username = u.name, GroupName = g.name }

Answer 3

Wah! Two froms! Thank you :)