我有以下结构的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);
}
}
}
}
}
结果是：
主要问题： 虽然上面的代码可以工作，但感觉太凌乱了。我想知道是否有更好/更容易的方法来实现这个？用户：Bob
角色：测试人员
描述：测试人员第一描述
描述：测试人员第二描述
描述：测试人员第三描述
角色：经理
描述：经理第一描述
描述：经理第二描述
描述：经理第三描述
角色：程序员
描述：程序员第一描述
描述：程序员第二描述
描述：程序员第三描述
额外加分： 请随意提出比使用内部连接更好的查询方式。我的目标是提高性能。
编辑：
我也想到了选项2，但我认为这不是一个好的解决方案。使用选项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：
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());
}
}
Slapper.Automapper的库来通过一个查询解决它：https://github.com/SlapperAutoMapper/Slapper.AutoMapper - silkfire.ToLookup()调用来进行内存过滤。这非常简单且高效。在这个简单的情况下，您的第二个选项也可以工作，但如果您有许多列和许多连接，那么所选数据的指数增长会导致查询序列化性能差。 - Tamas