Dapper关联查询

1、一对一：

  using (IDbConnection connecton = new MySqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
            {
                string sql = @"SELECT *
                               from expand_userbasicinformation b
                               JOIN expand_users u on b.UserId = u.UserId";
                var infos = connecton.Query<UserInfo, User, UserInfo>(sql, (info, user) =>
                {
                    info.User = user;
                    return info;
                }, splitOn: "UserId");
            }

其中，实体定义如下：

  public class UserInfo
    {
        public string InformationId { get; set; }

        public string UserId { get; set; }

        public User User { get; set; }

        public string RealName { get; set; }
    }

    public class User
    {
        public string UserId { get; set; }

        public string LoginName { get; set; }

        public string LoginPassword { get; set; }
    }

 

2、一对多：

using (IDbConnection connecton = new MySqlConnection(ConfigurationManager.ConnectionStrings["crmcontroller"].ConnectionString))
            {
                List<Client> clients = new List<Client>();
                string sql = @"SELECT *
                               from crm_client_list
                               join crm_client_field_value on ClientId = ModelId";
                var infos = connecton.Query<Client, ClientField, Client>(sql, (c, f) =>
                {
                    var currentClient = clients.Find(x => x.ClientId == c.ClientId);
                    if (currentClient == null)
                    {
                        c.Fields.Add(f);
                        clients.Add(c);
                        return c;
                    }
                    else
                    {
                        currentClient.Fields.Add(f);
                        return currentClient;
                    }
                }, splitOn: "ModelId");
            }

其中，实体定义如下：

  public class Client
    {
        public Client()
        {
            this.Fields = new List<ClientField>();
        }

        public string ClientId { get; set; }

        public string Name { get; set; }

        public string PhoneNumber { get; set; }

        public string IDNumber { get; set; }

        public List<ClientField> Fields { get; private set; }
    }

    public class ClientField
    {
        public string FieldValueId { get; set; }

        public string ModelId { get; set; }

        public string FieldId { get; set; }

        public string FieldValue { get; set; }
    }