Linqtosql - joined rows

Question

In Linqtosql how do I show items from multiple rows in a single field.

eg I have a 3 table setup for tagging(entity, tag, entitytag) all linked via foreign keys.

For each entity I would like to return the name in one field and then all relevant tags in 2nd field.

eg Item1, tag1; tag2; tag3 Item2, tag4, tag5....

VB statements preferred.

Thanks Geoff

Answer 1

Okay, not sure if this is the most efficient way but it works.

Dim dc As New DataContext

Dim query = From i In dc.Items _
            Let tags = (From t In dc.ItemTags _
                        Where t.ItemID = i.ID _
                        Select t.Tag.Name).ToArray _
            Select i.ItemName, Tags = String.Join(" | ", tags)

Answer 2

With this answer I am assuming you have your tables setup similar to the following, names are not great, just for illustration:

AnEntity: Id, Name
ATag: Id, TagName
EntityTag: EntityId (FK to AnEntity.Id), TagId (FK to ATag.Id)

You might try this:

var entityTags = from ent in theEntities
                 from enttags in ent.EntityTags
                 group enttags by enttags.AnEntity into entityGroup
                 select new { TheEntity = entityGroup.Key, TheTags = 
                              from t in entityGroup
                              select t.ATag.TagName };

I have not been able to actually test this, I'll give it a shot this afternoon and edit it if need be. What is happening here is a SelectMany. The 'from ent in dc.AnEntities' gets all of the entity records, then the next 'from enttags in ent.EntityTags' gets all the entity tag records for each entity. The group by does pretty much that, groups the EntityTag records by AnEntity. Put them in an anonymous type and you are good to go.

EDITED:
Okay, changed the code above, it works now. Before you would get a list of the EntityTag objects, now you get the Entiy object and a list of strings (tags) for that entity.