Multi Mapping for one-to-many associations

[GoogleCodeExporter]

Currently I think the only way to map an entity and a one-to-many association 
is by mapping multiple result sets;

It would be nice to be able to do this:

    var data = cn.Query<PostModel, IEnumerable<Tag>, PostModel>(query,
        (p, tags) => {
            p.Tags = tags;
            return p;
        }, new { id = id });

If this "should" work already then please let me know :)

Original issue reported on code.google.com by b...@planetcloud.co.uk on 2 Jun 2011 at 7:02

[GoogleCodeExporter]

hmmm not following at all, can you include the SQL? How are we to populate that 
IEnumerable 

Original comment by sam.saff...@gmail.com on 3 Jun 2011 at 11:40

[GoogleCodeExporter]

It might not be the way to go but I thought we could map:

declare @id nvarchar(20) = 'test-post-1'

SELECT     BlogPosts.PostId AS Id, BlogPosts.Title, BlogPosts.Slug, 
BlogPosts.Summary, BlogPosts.MetaDescription, BlogPosts.[Content], 
BlogPosts.ContentFormat, 
                      BlogPosts.PublishDate, Tags.TagId AS Id, Tags.Title AS Title, Tags.Slug AS Slug
FROM         BlogPosts INNER JOIN
                      BlogPostTags ON BlogPosts.PostId = BlogPostTags.Post_id INNER JOIN
                      Tags ON BlogPostTags.Tag_id = Tags.TagId
WHERE     (BlogPosts.Slug = @id)

It may be that its just better performance to issue separate queries for the 
tags in which case I'll stick to that.

Original comment by b...@planetcloud.co.uk on 3 Jun 2011 at 11:47

[GoogleCodeExporter]

Can I clarify; your aim is to make the following easier (or automatic), yes?

        public void ParentChildIdentityAssociations()
        {
            var lookup = new Dictionary<int, Parent>();
            var parents = connection.Query<Parent, Child, Parent>(@"select 1 as [Id], 1 as [Id] union all select 1,2 union all select 2,3 union all select 1,4 union all select 3,5",
                (parent, child) =>
                {
                    Parent found;
                    if (!lookup.TryGetValue(parent.Id, out found))
                    {
                        lookup.Add(parent.Id, found = parent);
                    }
                    found.Children.Add(child);
                    return found;
                }).Distinct().ToDictionary(p => p.Id);
            parents.Count().IsEqualTo(3);
            parents[1].Children.Select(c => c.Id).SequenceEqual(new[] { 1,2,4}).IsTrue();
            parents[2].Children.Select(c => c.Id).SequenceEqual(new[] { 3 }).IsTrue();
            parents[3].Children.Select(c => c.Id).SequenceEqual(new[] { 5 }).IsTrue();
        }

Original comment by marc.gravell on 3 Jun 2011 at 12:35

[GoogleCodeExporter]

Oooh nice. I hadn't even got that far. In actual fact I'm quite happy with the 
above (now I know how to do it :p).

In your experience does this tend to work faster than two separate queries? In 
my case this would be a simple inner join returning a post record for each tag 
vs a query for the post and a query for the post tags.

Original comment by b...@planetcloud.co.uk on 3 Jun 2011 at 1:39

[GoogleCodeExporter]

What would be nice is if there was an overload of GridReader.Read<T> that 
accepted a multi mapping e.g. multi.Read<Parent, Child, Parent>

The only reason I bring this up is that whilst the above works fine, in some 
cases I need to perform paging and am doing this using multiple results 
(http://stackoverflow.com/questions/6214140/dapper-multi-mapping-with-a-single-r
eturn-value). Currently I'm just handling this by constructing a second In 
query to load the associations, which I'm more than happy to do of course.

Original comment by b...@planetcloud.co.uk on 3 Jun 2011 at 1:45

[GoogleCodeExporter]

It always depends on the volume. For an excessively large query, the amount of 
duplicated data in an artificially rectangular *single* query may cause 
bandwidth issues, but... at that point you are already pulling lots of data. 
There is, however, absolutely no reason that GridReader can't offer 
multi-mapping

Original comment by marc.gravell on 3 Jun 2011 at 3:40

[GoogleCodeExporter]

I totally support the grid reader handling multi mapping, only reason it is not 
there is that I did not get around to it... a patch is more than welcome

Original comment by sam.saff...@gmail.com on 4 Jun 2011 at 11:38

[GoogleCodeExporter]

I'll have a go :)

Original comment by b...@planetcloud.co.uk on 4 Jun 2011 at 1:44

[GoogleCodeExporter]

Just wanted to say +1.  This comes up quite a bit.

Original comment by pe...@hardwareworld.com on 10 Jun 2011 at 11:30

[GoogleCodeExporter]

BTW I have found the code for the "artificially rectangular" approach of Marc's 
June 3 post is simpler if an OrderBy is used to ensure all the parents are in 
order.  This eliminates the need for the lookup object since you deal with one 
parent, then move to the next, etc.

Original comment by pe...@hardwareworld.com on 10 Jun 2011 at 11:50

[GoogleCodeExporter]

Care to post your code? By it's nature the delegate will pass duplicate parents 
(if they have many children) so surely the lookup is necessary?

Original comment by b...@planetcloud.co.uk on 11 Jun 2011 at 9:52

[GoogleCodeExporter]

I tried adding multi mapping support to the GridReader but I think I'm in way 
over my head :(

Original comment by b...@planetcloud.co.uk on 11 Jun 2011 at 10:45

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Sure - my code looks like this.  By ensuring parents are grouped it eliminates 
the need for the Dictionary and for the Distinct().  Apologies I'm using a 
different unit test framework...

[TestClass]
public class UnitTest1
{
    [TestMethod]
    public void ParentChildIdentityAssociations()
    {
        var connection = new SqlConnection("data source=(local);trusted_connection=yes");
        connection.Open();

        var parents = new List<Parent>();
        Parent current = null;

        // SQL command should use order by to ensure all parent records are grouped by the parent id
        const string commandText = "select 1 as [Id], 1 as [Id] union all select 1,2 union all select 1,4 union all select 2,3 union all select 3,5";

        connection.Query<Parent, Child, Parent>(commandText,
            (parent, child) =>
            {
                if (current == null || current.Id != parent.Id)
                {
                    parents.Add(parent);
                    current = parent;
                }
                current.Children.Add(child);
                return current;
            });

        Assert.AreEqual(3, parents.Count);
        CollectionAssert.AreEqual(new[] { 1, 2, 4 }, parents[0].Children.Select(c => c.Id).ToArray());
        CollectionAssert.AreEqual(new[] { 3 }, parents[1].Children.Select(c => c.Id).ToArray());
        CollectionAssert.AreEqual(new[] { 5 }, parents[2].Children.Select(c => c.Id).ToArray());
    }
}

public class Parent
{
    public int Id;
    public List<Child> Children = new List<Child>();
}

public class Child
{
    public int Id;
}

Original comment by pe...@hardwareworld.com on 11 Jun 2011 at 4:33

[GoogleCodeExporter]

complete ... just checked in a fix ... not trivial by any means :) 

Original comment by sam.saff...@gmail.com on 14 Jun 2011 at 1:06

• Changed state: Fixed

[GoogleCodeExporter]

Nice!!

Original comment by pe...@hardwareworld.com on 14 Jun 2011 at 4:05