Clever Geek Handbook

Linqtosql - joined rows

In Linqtosql, how can I show items from multiple lines in one field.

For example, I have 3 tables for tags (entity, tag, entitytag), all related with foreign keys.

For each object, I would like to return the name in one field, and then all the corresponding tags in the second field.

for example Item1, tag1; tag2; tag3 Item2, tag4, tag5 ....

Alleged statements VB.

Thanks Geoff

+1


source to share


2 answers


Ok, 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)
+1


source


With this answer, I am assuming your table settings are like the following, the names are not great, just for illustration:

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

You can 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 haven't been able to actually test this, I'll give him a snapshot today and edit it if necessary. What's going on here is SelectMany. "From ent in dc.AnEntities" gets all the entity entries and then "from enttags in ent.EntityTags" gets all the entity tag entries for each entity. The group does quite a bit of this, groups EntityTag entries using AnEntity. Put them in anonymous type and you're good to go.

Revision:
Ok, changed the code above, it works now. Before you get a list of EntityTag objects, you now get an Entiy object and a list of strings (tags) for that object.

0


source






More articles:


All Articles