Which layer to use for Raw SQL queries using NPoco?

Question

I am using NPoco for my BLA. I need to know what are best practices for using custom/raw SQL queries with NPoco in .NET application ?

In general I know raw SQL queries are discourages but with NPoco I have scenarios where it make sense to join multiple tables (5,6 or more) to get 8,9 columns table result.

I have a generic repository for my DB and I use it to access my models. So in which layer should I write raw sql queries, of which result , I need in my BL ? And shall I always create interim DTOs to receive/map result of such queries or receive them in dynamic ? (how expensive it would be to map such results in dynamic object ?)

Thanks in advance for your valuable feedback.

Dany

Answer 1

In my opinion, PetaPoco is your Data Layer. You should place your queries in the BL.

Using dynamic is good only for getting short temporary results from the DB, but I would return always a list of objects (can be business objects or DTOs) from the BL.