linq select m:n user:groups

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 sugges开发者_开发问答tions?

Thanks! Clemens

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 any way to do this in query syntax?

In query syntax:

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