Using Dapper to populate objects from a T-SQL View

Question

I'm trying out using Dapper for my data access (in ASP.NET MVC3 FWIW). I have a a T-SQL view (in SQL Server) which is something like this:

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

So pretty simple. Essentially a list of staff each of which have a single company.

The problem I'm having is that I'm trying to map the output of this query onto my POCOs, but because each field in the View has to be unique (i.e. CompanyName instead of Name which already exists in tblStaff) the mapping to POCOs isn't working.

Here's the code:

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

Any advice how I might solve this puzzle? I'm open to changing the way I do views as right now I'm stumped about how to progress.

Answer 1

You should explicitly list all the fields returned from you view (no asterisks!) and where the field names are not unique, make use of aliases to deduplicate. As an exmaple:

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

The fields listed and the aliases you might use depend, of course, entirely on your code. Typically you adjust the aliases in your query to match the property names of the POCO.

Also, as a general rule of thumb, it's good to stay away from wildcards in SQL queries exactly because issues like this are introduced. Here's a decent article on SQL query best practices.

Excerpt:

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.