linq2sql left join with "multiselect"

I'm trying to achieve following by linq2sql, but not successful.

I've Member and Reference tables. DB is design in such a manner that Member can have multiple (>=0) References. What I want as a result of query is, list (rows) of members, where all references of the member are "collected" in one column.

What I had achieved is following query, but for this one there exist a row for each Reference.

 var refs = (from m in db.Members
                    join
                        r in db.References on m.PID equals r.PID into g
                    from o in g.DefaultIfEmpty()
                    select new
                    {
                        member = m,
                        name = (o == null ? "" : o.NameSurname)
                    });

I feel I need to开发者_如何转开发 insert SelectMany somewher :)

Could you please give hints on achieving the goal?

---

var refs = (from m in db.Members
                        select new
                        {
                            member = m,
                            name = String.Join(",",(from  r in db.References on m.PID equals r.PID into g
                        from o in g.DefaultIfEmpty() select o.NameSurname).toArray())
                        }).Distinct();

This is untested but I think this is what your looking for. It should grab your member and have all the references joined into name. If it doesn't work let me know and I will look into it furthur.

---

Do you mean like this:

var refs = from m in db.Members
           join r in db.References on m.PID equals r.PID into j
           from o in j.DefaultIfEmpty()
           select new { Member = m, Reference = o } into results
           group result by result.Member into g
           select g;

That would group all your results by member (the key of the group) and all the references associated with them (results matching that key).

Although you'd probably be better just setting the relationships up in the ORM then you can just reference the (presumably named) member.References property like so:

foreach( var member in db.Members )
{
    var allReferences = member.References;
}