Tranfer data between Object-relational mapper and Data Access Layer using DTO

Question

Does it make sense to transfer data between Object-relational and Data Access Layer using DTO? When would this pattern be useful and when would it be an anti-pattern

Answer 1

It is very useful when you want to isolate your business code from your database. In very simple terms the DTO's are created by selecting from the database, and changes to the DTO are translated back to the database by the Data Access Layer. In some cases you can even achieve database vendor independence (at least on the business side of your code).

The common downside is when you have very specific SQL to adress complicated joins, unions, etc or to maximize performance. In these cases it is useful to have the DAL allow for some sort of "bypass" method where you send dynamic sql or a stored procedure and get the data back.