Renaming your database columns because your code cannot cope with the data is not a good idea. In the world of separation of concerns, why should your database care? There are good database reasons to name ID columns "Id", and you may not even have the option to change them.
There's another issue with Dapper mapping that renaming columns does not get around; repeated types. If you are trying to map to more than one instance of a class Dapper gets confused, and renaming columns won't work because you will rename both instances.
Here is the solution I have come up with. It's similar to a lot of examples that use a dictionary, except:
- it can nest to as many levels as you like
- can cope with Dappers 7 item limit
- can cope with duplicates of the same class
- can be reused e.g., for Get, GetCurrent and GetAll
In this example there is an Auction that has many Lots. Each Lot may have 1 or many Items. Items might be packs of Items. The Items are from a limited catalogue and we like relational data, so a Things table contains the details on each Item, like colour, size, etc. Here we are only getting a single Lot, but getting an Auction is the same with another level on top for Auction.
Parameter 1 - The SQL to get everything in one go
Parameter 2 - A Type array of each object we'll get back. For this reason it's best to order your SELECT to group the fields into the classes
Parameter 3 - Call the method we're about to write with the SQL result
Parameter 4 - Standard parameter array for the SQL. SQL Injection is bad, m'kay?
public async Task<List<Lot>> GetAll(int auctionId)
{
using (var connection = new SqlConnection(_appSettings.ConnectionString))
{
await connection.OpenAsync();
var result = new List<Lot>();
await connection.QueryAsync($@"
SELECT [Lot].*,
[Item].[Id],
[Item].[LotId],
[Item].[Notes],
itemDetails.[Id],
itemDetails.[ThingId],
itemDetails.[Colour],
itemDetails.[Size],
[SubItem].[Id],
[SubItem].[ItemId],
[SubItem].[Notes],
subItemDetails.[Id],
subItemDetails.[ThingId],
subItemDetails.[Colour],
subItemDetails.[Size]
FROM [Lot]
INNER JOIN [Item] ON [Item].[LotId] = [Lot].[Id]
LEFT JOIN [Thing] AS itemDetails ON itemDetails.[Id] = [Item].[ThingId]
LEFT JOIN [SubItem] ON [SubItem].[ItemId] = [Item].[Id]
LEFT JOIN [Thing] AS subItemDetails ON subItemDetails.[Id] = [SubItem].[ThingId]
WHERE [AuctionId] = @{nameof(auctionId)}
ORDER BY [Lot].[Id], [Item].[Id], [Expansion].[Id];",
new Type[] {
typeof(Lot),
typeof(Item),
typeof(Thing),
typeof(Expansion),
typeof(Thing)
},
MapResult(result),
new
{
AuctionId = auctionId
}
);
return result.ToList();
}
}
private Func<object[], Lot> MapResult(List<Lot> result)
{
return (obj) =>
{
Lot lot = (Lot)obj[0];
Item item = (Item)obj[1];
Thing itemDetails = (Thing)obj[2];
SubItem subItem = (SubItem)obj[3];
Thing subItemDetails = (Thing)obj[4];
if (lot != null)
{
if (result.Any(a => a.Id == lot.Id))
{
lot = result.First(a => a.Id == lot.Id);
}
else
{
result.Add(lot);
}
}
if (item != null)
{
if (lot.Items.Any(i => i.Id == item.Id))
{
item = lot.Items.First(i => i.Id == item.Id);
}
else
{
lot.Items.Add(item.FromThing(itemDetails));
}
}
if (subItem != null)
{
if (item.SubItems.Any(e => e.Id == subItem.Id) == false)
{
item.SubItems.Add(subItem.FromThing(subItemDetails));
}
}
return null;
};
}
MapResult is the meat of the code. It returns a Func with two types, the Type array we defined above and the return Type, and takes a List of the top level object.
I then map each item from the object array to another of it's actual type. This keeps the code easier to read, and enables properties and methods of the object to be accessed without issue.
Then it's a case of stepping down the hierarchy, checking at each step if one already exists with a matching id, and swapping the iterator to a reference to it if it does. This means that following code will add to the existing item.
In this particular case I've also added a FromThing function to allow easier combining of object properties.
