CODEWITHSUNDEEP
c#databaseormdapper
blfuentes
blfuentes

Reputation: 2827

Adding multimap children into parent with Dapper

I have a very simple test project where I am trying to figure out how to add children into the parent's collection.

The datamodel is quite basic:

enter image description here

Current result returns a duplicated entry. enter image description here

Expected/desired

I expected the result to be just one entry with two children

GROUP1 -> { USER_1, USER_2 }

GROUP class

public class GROUP
{
    public GROUP()
    {
        this.USERs = new HashSet<USER>();
    }

    public int Group_ID { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
    public ICollection<USER> USERs { get; set; }
}

USER class

public class USER
{
    public int User_ID { get; set; }
    public int Group_ID { get; set; }
    public string Name { get; set; }
    public Nullable<int> Age { get; set; }

    public GROUP GROUP { get; set; }
}

Dapper method

public GROUP Get(int id)
{
    string sqlGetGroupExtended = $"SELECT _group.Group_ID, _group.Name, _group.Location, _user.User_ID, _user.Name, _user.GROUP_ID, _user.Age FROM dbo.[GROUP] _group " +
                                        "LEFT JOIN dbo.[USER] _user ON _group.Group_ID = _user.Group_ID " +
                                        "WHERE _group.Group_ID = @groupid;";

    GROUP result = null;
    var lookup = new Dictionary<int, GROUP>();

    using (var connection = new SqlConnection(Properties.Settings.Default.CodeTest_DB))
    {
        var extendedGroup = connection.Query<GROUP, USER, GROUP>(sqlGetGroupExtended, (parent, child) =>
        {
            if (!lookup.TryGetValue(parent.Group_ID, out GROUP found))
            {
                lookup.Add(parent.Group_ID, found = parent);
            }
            found.USERs.Add(child);
            return found;
        }, param: new { groupid = id }, splitOn: "Location");

        // result = extendedGroup  <--- WHAT TO DO HERE?
    }

    return result;
}

How can I achieve this?

References:

http://dapper-tutorial.net/dapper https://github.com/StackExchange/Dapper/blob/master/Dapper.Tests/MultiMapTests.cs#L12

Upvotes: 0

Views: 2473

Answers (2)

mauridb
mauridb

Reputation: 1569

If you're using SQL Server 2016 or Azure SQL you can take advance of JSON to return a hierarchical object:

https://medium.com/dapper-net/one-to-many-mapping-with-dapper-55ae6a65cfd4

It's an article I wrote on the subject, along with source code, that shows how to elegantly solve the problem.

Upvotes: 2

blfuentes
blfuentes

Reputation: 2827

My bad, as the code shows here https://github.com/StackExchange/Dapper/blob/master/Dapper.Tests/MultiMapTests.cs#L12

I was missing the .Distinct()

var extendedGroup = connection.Query<GROUP, USER, GROUP>(sqlGetGroupExtended, (parent, child) =>
{
    if (!lookup.TryGetValue(parent.Group_ID, out GROUP found))
    {
        lookup.Add(parent.Group_ID, found = parent);
    }
    found.USERs.Add(child);
    return found;
}, param: new { groupid = id }, splitOn: "Location,User_ID").Distinct();

Upvotes: 1

Related Questions