Dapper的多表查询实现

打开链接

using (var conn = new SqlConnection(myConnectionString)) {
    conn.Open();
    ....
}

单表查询

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;}
}

返回列表

IEnumerable<Account> resultList = conn.Query<Account>(@"

                    SELECT * 
                    FROM Account
                    WHERE shopId = @ShopId", 
new {  ShopId = shopId });

返回单个对象

Account result = conn.Query<Account>(@"
                    SELECT * 
                    FROM Account
                    WHERE Id = @Id", 
   new {  Id = Id }).FirstOrDefault();

　返回dynamic

dynamic account = conn.Query<dynamic>(@"
                    SELECT Name, Address, Country
                    FROM Account
		    WHERE Id = @Id", new { Id = Id }).FirstOrDefault();

　

嵌有单个对象

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;}
  public Shop Shop {get;set;}
}
public class Shop {
  public int? ShopId {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
}

　查询

var resultList = conn.Query<Account, Shop, Account>(@"
                    SELECT a.Name, a.Address, a.Country, a.ShopId
                            s.ShopId, s.Name, s.Url
                    FROM Account a
                    INNER JOIN Shop s ON s.ShopId = a.ShopId                    
                    ", (a, s) => {
                         a.Shop = s;
                         return a;
                     },
                     splitOn: "ShopId"
                     ).AsQueryable();

 

嵌有列表对象

public class Shop {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
  public int ShopId {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;