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

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 PersonReposi开发者_JAVA百科tory 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)?

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

I'd stick with the single responsibility principle and stick with your repository classes. If your database grows over time, imagine how big the DataAccess class could become. You'll get away with it for a while, but it will grow and grow to the point where you have thousands of lines of code in a class (I have seen 15000+ lines in such a class before) and you're stuck!

You'll want to use a repository to connect directly with your DBML using L2S.

Then you'll want to create a service that talks to your repository.

Finally you'll use the service in things like your code-behind or your MVC controller.

User Repository

Namespace Data

#Region "Interface"
    Public Interface IUserRepository
        Function GetAllUsers() As IList(Of User)
        Function GetUserByID(ByVal id As Integer) As User
    End Interface
#End Region

#Region "Repository"
    Public Class UserRepository : Implements IUserRepository
        Private dc As MyDataContext
        Public Sub New()
            dc = New MyDataContext
        End Sub

        Public Function GetAllUsers() As System.Collections.Generic.IList(Of User) Implements IUserRepository.GetAllUsers
            Dim users = From u In dc.Users
                        Select u
            Return users.ToList
        End Function

        Public Function GetUserByID(ByVal id As Integer) As User Implements IUserRepository.GetUserByID
            Dim viewUser = (From u In dc.Users
                       Where u.ID = id
                       Select u).FirstOrDefault
            Return viewUser
        End Function

    End Class
#End Region
End Namespace

User Service

Namespace Data

#Region "Interface"
    Public Interface IUserService
        Function GetAllUsers() As IList(Of User)
        Function GetUserByID(ByVal id As Integer) As User
    End Interface
#End Region


#Region "Service"
    Public Class UserService : Implements IUserService

        Private _ValidationDictionary As IValidationDictionary
        Private _UserRepository As IUserRepository

        Public Sub New(ByVal validationDictionary As IValidationDictionary, ByVal UserRepository As IUserRepository)
            _ValidationDictionary = validationDictionary
            _UserRepository = UserRepository
        End Sub

        Public Function GetAllUsers() As System.Collections.Generic.IList(Of User) Implements IUserService.GetAllUsers
            Return _UserRepository.GetAllUsers
        End Function


        Public Function GetUserByID(ByVal id As Integer) As User Implements IUserService.GetUserByID
            Return _UserRepository.GetUserByID(id)
        End Function

    End Class
#End Region
End Namespace

Now just make sure to use CRUD like you mentioned above.

Also, please forgive my VB. You might need to run it through the http://converter.telerik.com code converter to get the C# stuff.