You should use an ORM to save you writing your own.

It sounds like your problem is more that there is no consistent design choice in where you put your data access logic.

I think EF contributes to this problem by not working well with a repository pattern.

If you were using a simple ORM or writing your own SQL, and denied yourself "business logic on the db" in the form of sprocs and views, then you are almost forced to put all your "selection logic" in a DAL or Repository class. You code becomes neat and all your data access is compartmentalised naturally.

EF provides generic repository objects and; (glossing over code first a bit) generates entity objects which might not be quite what you want.

So its quick and easy, but leaves an open question about where you put filtering and joining, or mapping more complex objects, to which there is no good answer.

Throw in views, sprocs and random sql and you further complicate the situation.

However, EF is a pretty tried and true solution these days, I don't think you need to worry about it generating bad sql or not being able to handle complex databases.

Just make that architectural design choice and ask if it works better with EF or a lighter ORM

You should use an ORM to save you writing your own.

It sounds like your problem is more that there is no consistent design choice in where you put your data access logic.

I think EF contributes to this problem by not working well with a repository pattern.

If you were using a simple ORM or writing your own SQL, and denied yourself "business logic on the db" in the form of stored procedures and views, then you are almost forced to put all your "selection logic" in a DAL or Repository class. You code becomes neat and all your data access is compartmentalised naturally.

EF provides generic repository objects and; (glossing over code first a bit) generates entity objects which might not be quite what you want.

So its quick and easy, but leaves an open question about where you put filtering and joining, or mapping more complex objects, to which there is no good answer.

Throw in views, stored procedures and random sql and you further complicate the situation.

However, EF is a pretty tried and true solution these days, I don't think you need to worry about it generating bad sql or not being able to handle complex databases.

Just make that architectural design choice and ask if it works better with EF or a lighter ORM.