ASP.NET: Linq2SQL: selecting all names matching an id

Got 2 tables: db.Tags (ID, TagName) and db.Names (ID, Name, TagID).

I want to fetch all db.Tags rows, and all the Names matching the TagID.

So it will look like

ID - TagName - Names
1  - tag1    - name1, name2, name3
2  - tag2    - name4, name5, name6

Is this possible in one (long) linq query? or do I have to get all the tags, then d开发者_如何学Co foreach tag, get all the names, then do foreach names to put them in a one long string..

Thanks in advance!

EDIT:

Okay see my comment on the second answer (first one up..), this is what i tried but i get some errors in compiler:

        var tags = 
        from t in db.Tags
        orderby t.Priority ascending
        select new {
            t.ID,
            t.Name,
            t.Priority,
            Places = String.Join(", ", 
                (from p in db.Places
                 join o in db.TagToPlaces on new {
                    p.ID,
                    t.ID
                 }
                 equals new {
                     o.PlaceId,
                     o.TagId
                 }
                 select p.Name
                 ).ToArray()
             ) 
        }
    );

I think this is what you're after:

var query =
    from t in db.Tags
    select new
    {
        t.ID,
        t.TagName,
        Names = String.Join(", ",
            (from n in db.Names
            where n.TagID == t.ID
            select n.Name)
            .ToArray()),
    };

With this I get the same sort of output that you gave in your question. I also understood that you want to output the tag id and name even when there are no associated name records - my query does that.

Now depending on if you're using EF or LINQ-to-SQL or something else you may need to add .ToArray() to the db.Tags & db.Names references to force the database query to occur.

If you have a large number of tag records you'll find you have a large number of queries going to the database. You could make this change to reduce it to only two queries:

var tags = db.Tags.ToArray();
var names = db.Names.ToArray();
var query =
    from t in tags
    select new
    {
        t.ID,
        t.TagName,
        Names = String.Join(", ",
            (from n in names
            where n.TagID == t.ID
            select n.Name)
            .ToArray()),
    };

Now you just need to make sure that your data fits into memory - but it sounds like it should. I hope this helps.

Since the concat is a pain in TSQL, I would query the 3 values "as is", and format from there:

var list = (from tag in db.Tags
           join name in db.Names
             on tag.ID equals name.TagId
           orderby tag.ID
           select new { tag.ID, tag.TagName, name.Name }).ToList();

for example, if I wanted the names by tag-id, I could do:

var namesByTag = list.ToLookup(row => row.ID, row => row.Name);

(or whatever else you choose)