Design Patterns Architecture – Data Access Layer, Business Class or Repository

Although you can just put LINQ queries in your BLL this may lead to code duplication. I would still create repositories that will be a wrapper to LINQ queries. This will separate Database code from BLL code and will be useful when you decide to modify your data access code (ie. move to Dapper, or to precompiled queries).

It will also help with unit testing, as repositories can be easily mocked.

Your DAL should return your data objects

Ideally your DAL should be a "black box" object, that your application code can use to ask for a data object or manipulate existing data objects. Sometimes there is another layer put between the DAL and the application code called the Repository, which further separates the two layers, although this is not always needed.

In addition, you usually do not want your business objects being able to create themselves. This can cause security holes where someone can use your library, and create a new instance of your object by calling .Load(someId) on it, and it merges together two layers that should be completely separate.

I also don't recommend providing a.Load(DataSet ds) method because if the data set definition changes, you'll have to hunt down the data objects which use that data set and change them. It's easier to keep all your data access code in one place, so if you change the data access query, you should only have to change your DAL layer.