Dapper implements one-to-Multiple object link aggregation navigation attributes and dapper Aggregation

Dapper implements one-to-Multiple object link aggregation navigation attributes and dapper Aggregation

Domain objects: Game and Room. one-to-many relationships between the two. JOIN is used in SQL statements.

public class Game : AggregateRoot{    public string Tag { get; set; }    public string Title { get; set; }    public string Description { get; set; }    public IEnumerable<Room> Rooms { get; set; }    public Game()    {        this.Rooms = new HashSet<Room>();    }    public void SetRooms(IEnumerable<Room> rooms)    {        this.Rooms = this.Rooms.Concat(rooms);    }}public class Room : Entity{    public int GameId{ get; set; }    public intstring Name { get; set; }    public int Limit { get; set; }    public string Owner { get; set; }}

Generally, Dapper Query <TFirst, TSecond, TReturn> () or QueryAsync <TFirst, TSecond, TReturn> () can be used, but it is troublesome to remove duplicate records.

Therefore, we can extend two Query/QueryAsync methods:

/// <Summary> /// query the object set with aggregation navigation attributes /// </summary> /// <typeparam name = "TFirst"> subject object type </ typeparam> // <typeparam name = "TSecond"> aggregation navigation object type </typeparam> /// <param name = "setting"> how to set aggregation navigation attributes </ param> public static IEnumerable <TFirst> Query <TFirst, TSecond> (this IDbConnection cnn, string SQL, Action <TFirst, TSecond> setting, object param = null, string splitOn = "Id") where TFirst: class, IEntity <int> where TSe Cond: class, IEntity <int> {TFirst lookup = null; var hashes = new HashSet <TFirst> (); cnn. query <TFirst, TSecond, TFirst> (SQL, (first, second) =>{// the first record, or a new subject record, otherwise, lookup is still the previous record if (lookup = null | lookup. id! = First. Id) lookup = first; if (second! = Null & second. Id> 0 & setting! = Null) setting (lookup, second); if (! Hashes. any (m => m. id = lookup. id) hashes. add (lookup); return null;}, param: param, splitOn: splitOn); return hashes ;} /// <summary> /// asynchronously query the object set with aggregation navigation attributes /// </summary> /// <typeparam name = "TFirst"> subject object type </typeparam> /// <typeparam name = "TSecond"> aggregation navigation object type </typeparam> /// <param name = "setting"> how to set aggregation navigation attributes </param> public static async Task <IEnumerable <TFirst> QueryAsync <TFirst, TSecond> (this IDbConnection Cnn, string SQL, Action <TFirst, TSecond> setting, object param = null, string splitOn = "Id") where TFirst: class, IEntity <int> where TSecond: class, IEntity <int> {TFirst lookup = null; var hashes = new HashSet <TFirst> (); await cnn. queryAsync <TFirst, TSecond, TFirst> (SQL, (first, second) =>{// the first record, or a new subject record, otherwise, lookup is still the previous record if (lookup = null | lookup. id! = First. Id) lookup = first; if (second! = Null & second. Id> 0 & setting! = Null) setting (lookup, second); if (! Hashes. any (m => m. id = lookup. id) hashes. add (lookup); return null;}, param: param, splitOn: splitOn); return hashes ;}

 

Call example:

return await _db.QueryAsync<Game, Room>("SELECT * FROM game g LEFT JOIN room r ON r.gameid = g.id", (game, room) =>{    game.SetRooms(new HashSet<Room> { room });}, splitOn: "Id");