What is the best-practice way to use LINQ to SQL in a C# application? [Design-pattern]

Question

OK, I'm always trying to improve the way I code, because it's my passion. I have a .dbml file (LINQ to SQL), and I use it to access my SQL Server database.

Imagine, if you will, that you have a Person table in your database and you want to provide a way to delete, add, and modify a Person record.

The way I'm handling things currently is creating classes called PersonRepository, CarRepository, DocumentRepository, etc. For each table in my database, I create a repository class.

These repository classes generally consist of something similar to this:

MyDatabaseContext db = new MyDatabaseContext();

public Person GetPersonByID(int id)
{
    return db.Person.Where(p => p.ID == id);
}

Pretty much the same for the basic CRUD functionality of each table.

If I need something more specific, for example "Sergio, I need a list of all people born between x and y"; then I just add the method to the PersonRepository class.

public List<Person> GetPeopleFromDOB(DateTime x, DateTime y)
{
    // Do the logic here.
}

Another idea I had was to create a DataAccess.cs class and have all of these methods in there (we would be talking around 4-5 methods per tables in existence) and have them divided by regions.

What are the more knowledgeable programmers doing and what suggestions would you offer for an eager young programmer (I'm 20 years old)?

Answer 1

Here are the problems with what you're doing above:

Using List when you should be using IQueryable

Why use AsQueryable() instead of List()?

Basically, you should never get data until you need it. With your approach you're always getting data from the database and subsequent LINQ queries will act on all data. Why not just build up queries and let LINQ refine things down to where it only gets what you need and only when you need it?

Using Methods when Extension Methods would be perfect

You are creating a utility class of methods with things like GetPeopleFromDOB. Why not make this an Extension method? If you make all of them return IQueryable you could use them in succession as well. E.g. GetPeople().StatusEnabled().BornInJuly().AgeIsGreaterThan( 57 ); Also, if you must do this at least consider doing so in a partial class or a static utility class.

Consider Using ActiveRecord/Repository Pattern as the root

http://compiledexperience.com/blog/posts/Implementing-an-ActiveRecord-pattern-in-Linq-to-SQL

Right now you're creating several hardcoded repositories, but you should be basing them off of Repository?

Why not use validators?

If you're using ASP.NET MVC, validation is part and parcel, however with webforms you can also use the Data Annotation Validators.

http://adventuresdotnet.blogspot.com/2009/08/aspnet-webforms-validation-with-data.html