sql = @"select * from users where City = @City and IsActive = @IsActive"; con.Query<User>(sql, new User() { IsActive = true, City = "Beijing" }).ToList().Dump();
也可以使用匿名对象，更加灵活：
1
con.Query<User>(sql, new { IsActive = true, City = "Beijing" }).ToList();
sql = """ select * from (select * from users order by id desc limit 10) u left join orders o on o.userid = u.id """;
Dictionary<int, User> dic = new(); var r = con.Query<User, Order, User>(sql, (u, o) => { if (!dic.TryGetValue(u.Id, out User result)) { result = u; result.Orders = new List<Order>(); dic.Add(result.Id, result); }
result.Orders.Add(o);
return result; }, splitOn: "Id").ToList();
Query<T1, T2, TReturn>() 是 Dapper 的多映射 API。splitOn: "Id" 告诉 Dapper 在 Id 列处分拆两个对象（左表字段映射到 User，从 Id 开始映射到 Order）。
由于 LEFT JOIN 会产生笛卡尔积式的重复行（一个用户如果有 3 个订单，则该用户在结果集中会出现 3 次），需要通过 Dictionary<int, User> 做去重，将多个 Order 归并到同一个 User 的 Orders 列表中。
✦ 方法二：QueryMultiple + LINQ (Approach 2)
1 2 3 4 5 6 7 8 9 10 11 12 13
sql = """ select * from users order by id desc limit 10; select * from orders where userid in (select id from users order by id desc limit 10); """;
sql = """ create table if not exists Categories ( Id integer primary key autoincrement, Name text not null, Description text null, IsActive integer not null default 1, CreatedAt text not null ); """; (await con.ExecuteAsync(sql)).Dump();
//查询未激活的用户信息 sql = @"select * from users where IsActive = @IsActive"; con.Query<User>(sql, new User() { IsActive = false }).ToList().Dump();
//查询未激活的用户数量 sql = @"select count(*) from users where IsActive = @IsActive"; (await con.ExecuteScalarAsync<int>(sql, new User() { IsActive = false })).Dump();
//查询城市是北京且激活的用户信息 sql = @"select * from users where City = @City and IsActive = @IsActive"; con.Query<User>(sql, new User() { IsActive = true, City = "Beijing" }).ToList().Dump();
//查询前十条用户信息和前十条订单信息 sql = @"select * from users limit 10;select * from orders limit 10"; usingvar multi = con.QueryMultiple(sql); multi.Read<User>().ToList().Dump(); multi.Read<Order>().ToList().Dump();
//查询前十条用户信息并挂载对应的订单信息——第一种方法 sql = """ select * from (select * from users order by id desc limit 10) u left join orders o on o.userid = u.id """; Dictionary<int, User> dic = new(); var r = con.Query<User, Order, User>(sql, (u, o) => { if (!dic.TryGetValue(u.Id, out User result)) { result = u; result.Orders = new List<Order>(); dic.Add(result.Id, result); } result.Orders.Add(o); return result; }, splitOn: "Id").ToList(); dic.Values.ToList().Dump();
//查询前十条用户信息并挂载对应的订单信息——第二种方法 sql = """ select * from users order by id desc limit 10; select * from orders where userid in (select id from users order by id desc limit 10); """; usingvar multi2 = con.QueryMultiple(sql); IEnumerable<User> users = multi2.Read<User>().ToList(); IEnumerable<Order> orders = multi2.Read<Order>().ToList(); foreach (User user in users) user.Orders = orders.Where(r => r.UserId == user.Id).ToList(); users.Dump();
//建表 sql = """ create table if not exists Categories ( Id integer primary key autoincrement, Name text not null, Description text null, IsActive integer not null default 1, CreatedAt text not null ); """; (await con.ExecuteAsync(sql)).Dump(); sql = "pragma table_info(Categories);"; con.Query(sql).ToList().Dump();
//批量插入数据 sql = @"insert into categories(Name, Description, IsActive, CreatedAt) values(@Name,@Description,@IsActive,@CreatedAt)"; var categories = new[] { new { Name = "Books", Description = "Books, guides, and learning materials", IsActive = true, CreatedAt = DateTime.Now }, new { Name = "Electronics", Description = "Computer accessories and electronic devices", IsActive = true, CreatedAt = DateTime.Now }, new { Name = "Office", Description = "Office supplies and productivity tools", IsActive = true, CreatedAt = DateTime.Now } }; (await con.ExecuteAsync(sql, categories)).Dump();
//删除数据 sql = @"delete from categories where Name = @Name"; (await con.ExecuteAsync(sql, new { Name = "Office" })).Dump(); sql = @"select * from categories"; (await con.QueryAsync(sql)).ToList().Dump();
//更新数据 sql = @"update categories set IsActive = @IsActive where Name = @Name"; (await con.ExecuteAsync(sql, new { IsActive = 0, Name = "Books" })).Dump(); sql = @"select * from categories"; (await con.QueryAsync(sql)).ToList().Dump();
//批量更新数据+事务 usingvar tran = await con.BeginTransactionAsync(); try { sql = @"update users set IsActive = @IsActive where id in @ids"; int rows = await con.ExecuteAsync(sql, new { IsActive = false, ids = new[] { 1, 2, 3, 4, 5 } }, tran); await tran.CommitAsync(); rows.Dump(); sql = @"select * from users order by id asc limit 5"; (await con.QueryAsync<User>(sql)).Dump(); } catch { tran.Rollback(); throw; } }
