Dapper.net - how to map many to many relationship in single command

Question

I have a Place object.

It can have many Tags associated with it.

Tags can be associated with many Places.

(Although this is a many to many relationship - I guess conceptually it may as well be a one to many - a collection of objects - with each object containing a collection of objects).

Each Place object has a List collection associated with it:

public class Place
{
    public Guid Id { get; set; }
    public string PlaceName { get; set; }
    ...
    public List<Tag> Tags { get; set; }
}

public class Tag
{
    public Guid Id { get; set; }
    public string TagName { get; set; }
}

SQL that selects required data from many to many relationship:

select * from place p
inner join tagplace tp on tp.placeid = p.id 
inner join tag t on t.id = tp.tagid
order by placename

Can I map all of this in a single Dapper command (or at least not n+1 commands)? The Github documentation covers one to many relationships - but not many to many.

(The reason I need to do this - is because I want to output a grid of all places, and I want one column to be a comma separated list of tags (I will create a Helper that takes a collection of tags and outputs a comma separated string).

I have seen a similar question where Slapper.AutoMapper is recommended:

How do I write one to many query in Dapper.Net?

I would rather avoid using additional libraries if possible - but I guess it may be required here?

Whats the best way to approach this?

score 3 · Accepted Answer · answered Jul 04 '16 at 17:33

I want to output a grid of all places, and I want one column to be a comma separated list of tags

Since you want all the places, you need to use a LEFT JOIN instead of inner join to get all the Places and it's associated tag(s).

var query = @"select p.Id,p.Name,t.TagName from place p
              leftjoin tagplace tp on tp.placeid = p.id 
              left join tag t on t.id = tp.tagid
              order by placename";

This query is going to give a result set with 3 columns : Id, Name and TagName . I would create a simple DTO for the result set record.

internal class PlaceTagDto
{
   public Guid Id { internal set;get;}
   public string Name { internal set;get;}
   public string TagName { internal set; get }
}

Now use Dapper to execute the query and map the result to the PostTagDto collection. Then group the result by TagName.

using(var c=new SqlConnecction("YourConnectionString"))
{
  var r=cn.Query<PlaceTagDto>(query);
  var grouped=r.GroupBy(s => s.Id, i => i,
                    (k, groupdPlaces) => new Place {
                               Id = k, 
                               PlaceName = groupdPlaces.First().Name,
                               Tags = groupdPlaces.Where(y=>y.TagID!=null)
                                                  .Select(t=>new Tag
                                                                 { 
                                                                   TagName = t.TagName
                                                                 }).ToList() 
                                                   });
}

Also, If you change the Tags property type from List<Tag> to IEnumerable<Tag>, you can avoid the ToList() call in the LINQ expression.

Now in your UI you may use String.Join method to show the comma separated list of Tags,

@model IEnumerable<Place>
<table>
@foreach(var p in Model)
{
  <tr>
      <td>@p.Name</td>
      <td>
          @String.Join(",",p.Tags.Select(s=>s.TagName))
      </td>
   </tr>
}
</table>

Dapper.net - how to map many to many relationship in single command

1 Answers1