Here's the scenario:
public class MyPoco
{
public int ID { get; set; }
public string Name { get; set; }
// ... a bunch of other properties ...
}
Here's what I do if I'm interested in results containing the ID and Name only:
var list = db.FetchBy<MyPoco>(sql => sql
.Select(x => new { x.ID, x.Name }));
This works fine, i.e. both the ID and the Name property of POCO's in the list are correctly populated.
However, if one of the properties is mapped to a DB column with a different name then that property is not populated. E.g., let's say the ID is mapped to a column named "Key" and Name to "Title", i.e.:
public class MyPocoMap : Map<MyPoco>
{
public MyPocoMap()
{
Columns(x =>
{
x.Column(c => c.ID).WithName("Key");
x.Column(c => c.Name).WithName("Title");
}
}
}
Then when I read the data using FetchBy
like before, the list elements' ID and Name properties are all null. The SELECT statement generated by NPoco looks like this:
SELECT [Key] AS [ID], [Title] AS [Name] FROM MyPocos
And because the ID property is mapped to "Key" and Name is mapped to "Title", NPoco fails to populate these two properties because it cannot find those fields in the resultset.
The only way to trick NPoco is by doing this:
var list = db.FetchBy<MyPoco>(sql => sql
.Select(x => new { Key = x.ID, Title = x.Name }));
Then SQL looks like so:
SELECT [Key], [Title] FROM MyPocos
and NPoco happily populates the properties.
However, it's not a good solution because it requires that the developer of the business logic layer must know the names of database fields mapped to POCO properties.
Any thoughts on how this could be fixed?