c# - 使用 Dapper 从 T-SQL View 填充对象

Question

我正在尝试使用 Dapper 进行数据访问(在 ASP.NET MVC3 FWIW 中)。我有一个 T-SQL View (在 SQL Server 中)，它是这样的:

SELECT s.*, c.CompanyId AS BreakPoint c.Name AS CompanyName
FROM tblStaff AS s
INNER JOIN tblCompanies AS c ON c.CompanyId = s.CompanyId

非常简单。本质上是一个员工列表，每个员工都有一家公司。

我遇到的问题是我试图将此查询的输出映射到我的 POCO，但是因为 View 中的每个字段都必须是唯一的(即 CompanyName 而不是 tblStaff 中已经存在的名称)映射到 POCO 无效。

代码如下:

var sql = @"select * from qryStaff";
var people = _db.Query<Person, Company, Person>(sql, (person, company) => {person.Company = company; return person;}, splitOn: "BreakPoint");

有什么建议可以解决这个难题吗？我愿意改变我做观点的方式，因为现在我对如何进步感到困惑。

Answer 1

您应该明确列出从您的 View 中返回的所有字段(没有星号!)并且在字段名称不唯一的地方，使用别名进行重复数据删除。例如:

SELECT 
    s.CompanyName as CompanyName1, 
    s.BreakPoint as BreakPoint1,
    ...
    c.CompanyId AS BreakPoint,
    c.Name AS CompanyName
FROM tblStaff AS s
INNER JOIN tblCompanies AS c ON c.CompanyId = s.CompanyId

当然，列出的字段和您可能使用的别名完全取决于您的代码。通常，您会调整查询中的别名以匹配 POCO 的属性名称。

此外，作为一般经验法则，最好远离 SQL 查询中的通配符，因为会引入此类问题。 Here's a decent article SQL 查询最佳实践。

摘录:

Using explicit names of columns in your SELECT statements within your code has a number of advantages. First, SQL Server is only returning the data your application needs, and not a bunch of additional data that your application will not use. By returning only the data you need you are optimizing the amount of work SQL Server needs to do to gather all the columns of information you require. Also by not using the asterisk (*) nomenclature you are also minimizing the amount of network traffic (number of bytes) required to send the data associated with your SELECT statement to your application.

Additionally by explicitly naming your columns, you are insulating your application from potential failures related to some database schema change that might happen to any table you reference in your SELECT statement. If you were to use the asterick (*) nomenclature and someone was to add a new column to a table, your application would start receiving data for this additional column of data, even without changing your application code. If your application were expecting only a specific number of columns to be returned, then it would fail as soon as someone added an additional column to one of your referenced tables. Therefore, by explicitly naming columns in your SELECT statement your application will always get the same number of columns returned, even if someone adds a new column to any one of the tables referenced in your SELECT statement.