从数据库中选择多个对象时，定位器只映射一个对象
我有一些代表数据库对象的C＃类，其中一些包含一个或多个其他自定义对象或自定义对象的枚举类型。我正在使用Dapper进行查询，并使用定位器来映射到自定义对象。它适用于单个对象。我可以轻松地从数据库中获取具有特定ID的父对象，执行一些内部连接，并将它和它拥有的所有东西映射到C＃中的自定义对象。当我想要对多个父ID进行选择时出现问题。从数据库中选择多个对象时，定位器只映射一个对象
某些情况下，比如说我有一个人，那个人有一份爱好列表，里面有一个ID和一个描述，他们可用的日期列表也有一个ID和描述，也可能是另一个自定义字段比如他们是否已经或者甚至愿意在孩子身边，也可以归结为一个简单的ID和描述。我们会调用最后一个字段的孩子状态。我会写一个select语句是这样的：
SELECT
,person.id as Id
,person.first_name as FirstName
,person.last_name as LastName
,hobby.Id as Hobbies_Id
,hobby.Description as Hobbies_Description
,avail.Id as Availabilities_Id
,avail.Description as Availabities_Description
,child.Id as ChildStatus_Id
,child.Description as ChildStatus_Description
FROM
users.users person
JOIN
users.userhobbies uhobby
ON
person.id = uhobby.UserId -- one-to-many with relational table
JOIN
users.avail hobby
ON
uhobby.HobbyId = hobby.Id
JOIN
users.useravailabilities uavail
ON
person.id = uavail.UserId -- one-to-many with relational table
JOIN
users.availabilities avail
ON
uavail.AvailId = avail.Id
JOIN
users.childstatuses child
ON
person.ChildStatusId = child.Id
然后我想这映射到用户是这样的：
class User
{
public Guid Id {get; set;}
public string FirstName {get; set;}
public string LastName {get; set;}
public IEnumerable<Hobby> Hobbies {get; set;}
public IEnumerable<Availability> Availabilities {get; set;}
public ChildStatus ChildStatus {get; set;}
}
由于我使用精确命名惯例和这里的一切，从查询Dapper和Automapping的工作很好，就像这样：
// Using the above sql in a variable
var data = Connection.Query<dynamic>(sql);
var dataReal = Slapper.AutoMapper.MapDynamic<User>(data);
return dataReal;
这很好，但它只返回一个用户。我有一个类似的方法，它需要一个ID，我的所有测试用户都可以通过传递ID完美地检索。我试过浏览互联网，查看文档，而我发现的所有内容都是这样的：https://github.com/SlapperAutoMapper/Slapper.AutoMapper/issues/57谁似乎只是通过裂缝。我也试图将动态数据映射到各种其他结构，但没有运气。提前致谢！
更新： 我想出了一个有点残酷的“大锤”型解决方案。我不知道在这个时候，如果有更方便的解决方案，我强迫自己使用Slapper。不过，我想确保任何处于类似情况的人都有机会使其工作。这里是新的C＃部分：
var data = Connection.Query<dynamic>(sql);
IEnumerable<Guid> Ids = data.Select(row => (Guid)row.id).Distinct();
List<User> results = new List<User>();
foreach (Guid Id in Ids)
{
IEnumerable<dynamic> rows = data.Where(x => { return ((Guid) x.id).Equals(Id); });
User model = (Slapper.AutoMapper.MapDynamic<User>(rows, false) as IEnumerable<User>).FirstOrDefault();
if (model != null)
{
results.Add(model);
}
}
return results;
正如你所看到的，我产生的独特的“主要目标” ID的列表，并选择那些行到自己的名单，然后我传给搭接。我已经通过了“cache = false”参数，以避免在第一个对象之后将不相关的数据压缩到每个对象中。我可以通过实际保持UserHobby/UserAvailability/UserPhoto ID来解决这个问题，但我不喜欢让我的对象看起来的方式。希望这有助于某人。
我不熟悉Slapper，但是我会告诉你我用Dapper做了什么来构建一个双向引用对象的复杂图形。
简而言之，构造一个字典或KeyedCollection主叫connection.Query <>，然后引用它的小巧玲珑lambda表达式内之前。
这个方法返回的服务电话列表。每个服务呼叫分配给一名技术人员和一名客户。但是，技术人员可能会为多个客户分配多个服务呼叫。一位客户可能有多名技术人员在现场。
public ServiceCallResponse GetServiceCallsDapper(ServiceCallRequest Request)
{
var queryParameters = new {statuses = Request.Statuses, createDate = Request.CreateDate};
const string splitOn = "Number,Id"; // Id indicates beginning of second class (Technician). Number indicates begining of third class (Customer).
// Note multiple columns are named "Number". See note below about how Dapper maps columns to class properties.
// Note Dapper supports parameterized queries to protect against SQL injection attacks, including parameterized "where in" clauses.
const string query = @"sql query here..."
ServiceCallResponse response = new ServiceCallResponse(); // Keyed collection properties created in constructor.
using (IDbConnection connection = new SqlConnection("DB connection string here..."))
{
connection.Open();
// Dapper adds a generic method, Query<>, to the IDbConnection interface.
// Query<(1)ServiceCall, (2)Technician, (3)Customer, (4)ServiceCall> means
// construct a (1)ServiceCall, (2)Technician, and (3)Customer class per row, add to an IEnumerable<(4)ServiceCall> collection, and return the collection.
// Query<TFirst, TSecond, TThird, TReturn> expects SQL columns to appear in the same order as the generic types.
// It maps columns to the first class, once it finds a column named "Id" it maps to the second class, etc.
// To split on a column other than "Id", specify a splitOn parameter.
// To split for more than two classes, specify a comma-delimited splitOn parameter.
response.ServiceCalls.AddRange(connection.Query<ServiceCall, Technician, Customer, ServiceCall>(query, (ServiceCall, Technician, Customer) =>
{
// Notice Dapper creates many objects that will be discarded immediately (Technician & Customer parameters to lambda expression).
// The lambda expression sets references to existing objects, so the Dapper-constructed objects will be garbage-collected.
// So this is the cost of using Dapper. We trade unnecessary object construction for simpler code (compared to constructing objects from IDataReader).
// Each row in query results represents a single service call.
// However, rows repeat technician and customer data through joined tables.
// Avoid constructing duplicate technician and customer classes.
// Refer to existing objects in global collections, or add Dapper-mapped objects to global collections.
// This avoid creating duplicate objects to represent same data.
// Newtonsoft JSON serializer preserves object instances from service to client.
Technician technician;
Customer customer;
if (response.Technicians.Contains(Technician.Id))
{
technician = response.Technicians[Technician.Id];
}
else
{
response.Technicians.Add(Technician);
technician = Technician;
}
if (response.Customers.Contains(Customer.Number))
{
customer = response.Customers[Customer.Number];
}
else
{
response.Customers.Add(Customer);
customer = Customer;
}
// Set object associations.
ServiceCall.Technician = technician;
ServiceCall.Customer = customer;
technician.ServiceCalls.Add(ServiceCall);
if (!technician.Customers.Contains(customer))
{
technician.Customers.Add(customer);
}
customer.ServiceCalls.Add(ServiceCall);
if (!customer.Technicians.Contains(technician))
{
customer.Technicians.Add(technician);
}
return ServiceCall;
}, queryParameters, splitOn: splitOn));
}
return response;
}
使用这种技术，需要你设置PreserveReferencesHandling =真正的JsonSerializer类，所以对象引用的客户端保留。否则，Json。NET将构建重复对象和技术人员。客户总数将始终为1.
例如，如果John Doe在Acme和另一个Contoso上分配了服务电话，则他的技术人员.Customers.Count将等于1保留PreserveReferencesHandling == false（Json.NET将构造两个名为John Doe的技术人员对象）。