c# - Dapper 一对多关系

Question

我有 3 个表，结构如下:

CREATE TABLE [User](
    Id int NOT NULL,
    Name varchar(50)
    PRIMARY KEY (Id)
)

CREATE TABLE [Role](
    Id int NOT NULL,
    UserId int NOT NULL,
    Name varchar(50),
    PRIMARY KEY (Id),
    FOREIGN KEY (UserId) REFERENCES [User](Id)
)


CREATE TABLE [Description](
    Id int NOT NULL,
    RoleId int NOT NULL,
    Name varchar(50)
    FOREIGN KEY (RoleId) REFERENCES [Role](Id)
)

如您所见，它是嵌套两次的一对多关系。在代码中，我有以下类来表示它们:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }

    public IEnumerable<Role> Roles { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Name { get; set; }

    public IEnumerable<Description> Descriptions { get; set; }
}

public class Description
{
    public int Id { get; set; }
    public int RoleId { get; set; }
    public string Name { get; set; }
}

现在我需要查询用户并获取它附带的所有字段。我想出了一种使用 QueryMultiple 的方法，例如:

var queryOne = "SELECT Id, Name FROM [User] WHERE Id = 1";
var queryTwo = "SELECT r.Id, r.UserId, r.Name FROM  [User] u INNER JOIN [Role] r ON u.Id = r.UserId WHERE u.Id = 1";
var queryThree = "SELECT d.Id, d.RoleId, d.Name FROM  [User] u INNER JOIN [Role] r ON u.Id = r.UserId INNER JOIN [Description] d ON r.Id = d.RoleId WHERE u.Id = 1";

var conn = new SqlConnection();

using (var con = conn)
{
    var result = con.QueryMultiple(queryOne + " " + queryTwo + " " + queryThree);
    var users = result.Read<User>().FirstOrDefault();
    var roles = result.Read<Role>();
    var descriptions = result.Read<Description>();
    if (users != null && roles != null)
    {
        users.Roles = roles;
        Console.WriteLine("User: " + users.Name);
        foreach (var role in users.Roles)
        {
            Console.WriteLine("Role: " + role.Name);
            if (descriptions != null)
            {
                role.Descriptions = descriptions.Where(d => d.RoleId == role.Id);
                foreach (var roleDescription in role.Descriptions)
                {
                    Console.WriteLine("Description: " + roleDescription.Name);
                }
            }
        }
    }
}

结果是:

User: Bob
Role: Tester
Description: Tester First Description
Description: Tester Second Description
Description: Tester Third Description
Role: Manager
Description: Manager First Description
Description: Manager Second Description
Description: Manager Third Description
Role: Programmer
Description: Programmer First Description
Description: Programmer Second Description
Description: Programmer Third Description

主要问题:虽然上面的代码有效，但感觉太乱了。我想知道是否有更好/更简单的方法来实现这一目标？

奖励积分:也请随时提出比使用内部联接更好的查询方法。我的目标是提高性能。

编辑:

我也想到了选项二，但我还是认为这不是一个好的解决方案。使用选项 2，我创建了第 4 个对象，它将包含 3 个对象的组合结果，如下所示:

public class Combination
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public int RoleId { get; set; }
    public string RoleName { get; set; }
    public int DescriptionId { get; set; }
    public string DescriptionName { get; set; }
}

然后我这样处理:

var queryFour = "SELECT u.Id as 'UserId', u.Name as 'UserName', r.Id as 'RoleId', r.Name as 'RoleName', d.Id as 'DescriptionId', d.Name as 'DescriptionName' FROM  [User] u INNER JOIN [Role] r ON u.Id = r.UserId INNER JOIN [Description] d ON r.Id = d.RoleId WHERE u.Id = 1";

var conn = new SqlConnection();
using (var con = conn)
{
    var myUser = new User();
    var result = con.Query<Combination>(queryFour);
    if (result != null)
    {
        var user = result.FirstOrDefault();
        myUser.Id = user.UserId;
        myUser.Name = user.UserName;
        var roles = result.GroupBy(x => x.RoleId).Select(x => x.FirstOrDefault());
        var myRoles = new List<Role>();
        if (roles != null)
        {
            foreach (var role in roles)
            {
                var myRole = new Role
                {
                    Id = role.RoleId,
                    Name = role.RoleName
                };

                var descriptions = result.Where(x => x.RoleId == myRole.Id);
                var descList = new List<Description>();
                foreach (var description in descriptions)
                {
                    var desc = new Description
                    {
                        Id = description.DescriptionId,
                        RoleId = description.RoleId,
                        Name = description.DescriptionName
                    };
                    descList.Add(desc);
                }
                myRole.Descriptions = descList;
                myRoles.Add(myRole);
            }
        }
        myUser.Roles = myRoles;
    }

    Console.WriteLine("User: " + myUser.Name);
    foreach (var myUserRole in myUser.Roles)
    {
        Console.WriteLine("Role: " + myUserRole.Name);
        foreach (var description in myUserRole.Descriptions)
        {
            Console.WriteLine("Description: " + description.Name);
        }
    }
}

两种方法的结果输出相同，第二种方法使用 1 个查询而不是 3 个查询。

编辑 2:需要考虑的一点是，我这 3 个表的数据经常更新。

编辑 3:

private static void SqlTest()
{
    using (IDbConnection connection = new SqlConnection())
    {
        var queryOne = "SELECT Id FROM [TestTable] With(nolock) WHERE Id = 1";
        var queryTwo = "SELECT B.Id, B.TestTableId FROM [TestTable] A With(nolock) INNER JOIN [TestTable2] B With(nolock) ON A.Id = B.TestTableId WHERE A.Id = 1";
        var queryThree = "SELECT C.Id, C.TestTable2Id FROM [TestTable3] C With(nolock) INNER JOIN [TestTable2] B With(nolock) ON B.Id = C.TestTable2Id INNER JOIN [TestTable] A With(nolock) ON A.Id = B.TestTableId WHERE A.Id = 1";

            var gridReader = connection.QueryMultiple(queryOne + " " + queryTwo + " " + queryThree);
            var user = gridReader.Read<Class1>().FirstOrDefault();

            var roles = gridReader.Read<Class2>().ToList();

            var descriptions = gridReader.Read<Class3>().ToLookup(d => d.Id);

            user.Roles= roles;

            user.Roles.ForEach(r => r.Properties = descriptions[r.Id].ToList());
    }
}

Answer 1

您的第一个选项可以简化为以下内容。它删除了深层控制结构嵌套。您可以将其概括为更深的嵌套，而无需添加更多的嵌套/复杂性。

var queryOne = "SELECT Id, Name FROM [User] WHERE Id = 1";
var queryTwo = "SELECT r.Id, r.UserId, r.Name FROM  [User] u INNER JOIN [Role] r ON u.Id = r.UserId WHERE u.Id = 1";
var queryThree = "SELECT d.Id, d.RoleId, d.Name FROM  [User] u INNER JOIN [Role] r ON u.Id = r.UserId INNER JOIN [Description] d ON r.Id = d.RoleId WHERE u.Id = 1";

var conn = new SqlConnection();

using (var con = conn)
{
    var gridReader = con.QueryMultiple(queryOne + " " + queryTwo + " " + queryThree);
    var user = gridReader.Read<User>().FirstOrDefault();
    if (user == null)
    {
        return;
    }

    var roles = gridReader.Read<Role>().ToList();
    var descriptions = gridReader.Read<Description>().ToLookup(d => d.RoleId);

    user.Roles = roles;
    roles.ForEach(r => r.Descriptions = descriptions[r.Id]);
}

在性能方面，它的行为与您的第一个选项相同。

我不会选择你的第二个选项(或类似的基于 View 的选项):如果你有 R 个角色，并且每个角色平均有 D 个描述，你将查询 6*R*D 个单元而不是 2+3 个*R+3*D。如果 R 和 D 很高，您将查询更多数据，与运行 3 个查询而不是 1 个查询相比，反序列化的成本会很高。