CODEWITHSUNDEEP
c#sqldapper
user2827377
user2827377

Reputation: 1381

Multiple results with many-to-many relationship dapper

I have Items that i want to associate with 1 - many Tags

Entities: Items Tags ItemTag

I want to return all the items and the associated tags for displaying in a grid. So a collection of:

{
ItemTitle: ''
ItemTags: [ '', '', '']
}

I know dapper can do multiple result sets, but not sure how i'd use that functionality here.

SELECT * FROM Items
SELECT * FROM Tags
SELECT * FROM ItemTag

Then i would need to something to bring them all together so i have a collection:

Items: [
{
ItemTitle: ''
ItemTags: [ '', '', '']
},
{
ItemTitle: ''
ItemTags: [ '', '', '']
}
]

UPDATE: I have a solution, but would like input on optimizing

    public List<Item> GetAll()
    {
        var sql =
            "SELECT * FROM Items;" +
            "SELECT ItemId, Tags.Title FROM ItemTag left join Tags on ItemTag.TagId = Tags.Id;";

        using (var multipleResults = this.db.QueryMultiple(sql))
        {
            var Items = multipleResults.Read<Item>().ToList();
            var tags = multipleResults.Read<Tag>().ToList();

            foreach (var Item in Items)
            {
                var ItemTags = new List<Tag>();
                foreach (var tag in tags)
                {
                    if (tag.ItemId == Item.Id)
                    {
                        ItemTags.Add(tag);
                    }
                }
                Item.Tags = ItemTags;
            }

            return Items;
        }
    }

Upvotes: 2

Views: 2580

Answers (1)

user2827377
user2827377

Reputation: 1381

Here's what i ended up with. 

  public List<Item> GetAll()
    {
        var sql =
            "SELECT * FROM Items;" +
            "SELECT ItemId, Tags.Title FROM ItemTag left join Tags on ItemTag.TagId = Tags.Id;";

        using (var multipleResults = this.db.QueryMultiple(sql))
        {
            var items = multipleResults.Read<Item>().ToList();
            var tags = multipleResults.Read<Tag>().ToList();

            var tagsByItemId = tags.ToLookup(t => t.ItemId);

            foreach (var item in items)
            {
                item.Tags = tagsByItemId[item.Id].ToList();
            }

            return items;
        }
    }

Ref: Filling List property of objects in a foreach-loop

Upvotes: 2

Related Questions