Each table in my database has it's own POCO
class. Now, I have started to write complex SQL joins and the query resultset should be mapped to some Entity which can be sent to the Business Manager (another layer) for further processing. For an example, imagine my query returns the columns something like this (table name is prefixed with column name for simplicity's sake):
Customer.CustomerId,
Customer.CustomerName,
Customer.CustomerAddress,
[User].UserId,
[User].UserName,
[User].FirstName,
[User].LastName,
UserRole.RoleId,
UserRole.RoleName,
Employee.EmployeeId,
Employee.EmployeeName,
Employee.JoinDate,
MAX(AuditTrail.LastLoginDate)
etc
Questions:
- What design pattern should I use?
- I should be able to write multiple queries with a bunch of mix and match column's retrieved on every query. Maybe, not a good idea to map this type of resultset to POCO classes?
- I may have other queries with more or less the same type of columns needed to return from SQL resultset.
- Should I maintain seperate Entities just to support queries?
Note: Am using Dapper ORM to talk to SQL Server 2012 with .NET 4.5 Framework (C#). Please let know, if the question is unclear.