Basically, DAL (Data Access Layer) should provide simple CRUD (Create/Read/Update/Delete) methods but I always have a temptation to create more sophisticated methods in order to minimize database access roundtrips from Business Logic Layer.

What do you think about following extensions to CRUD (most of them are OK I suppose):

1. Read: GetById, GetByName, GetPaged, GetByFilter... e.t.c. methods
2. Create: GetOrCreate methods (model entity is returned from DB or created if not found and returned), Create(lots-of-relations) instead of Create and multiple AssignTo methods calls
3. Update: Merge methods (entities list are updated, created and deleted in one call)
4. Delete: Delete(bool children) - optional children delete, Cleanup methods

Where do you usually implement Entity Cache capabilities? DAL or BLL? (My choice is BLL, but I have seen DAL implementations also)

Where is the boundary when you decide: this operation is too specific so I should implement it in Business Logic Layer as DAL multiple calls? I often found insufficient BLL operations that were implemented in dozen database roundtrips because developer was afraid to create a bit more sophisticated DAL.

Thank you in advance!