dapper sub-select to collections code example

Example: c# query list of nested objects using dapper

public class Shop {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
  public IList<Account> Accounts {get;set;}
}

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get;set;}
}

var lookup = new Dictionary<int, Shop>();
conn.Query<Shop, Account, Shop>(@"
                    SELECT s.*, a.*
                    FROM Shop s
                    INNER JOIN Account a ON s.ShopId = a.ShopId                    
                    ", (s, a) => {
                         Shop shop;
                         if (!lookup.TryGetValue(s.Id, out shop)) {
                             lookup.Add(s.Id, shop = s);
                         }
                         if (shop.Accounts == null) 
                             shop.Accounts = new List<Account>();
                         shop.Accounts.Add(a);
                         return shop;
                     },
                     ).AsQueryable();

var resultList = lookup.Values;