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

Question

Well, I always try to improve the coding method, because it is my passion. I have a .dbml file ( LINQ to SQL ) and I use it to access the SQL Server database .

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

I am currently developing classes called PersonRepository, CarRepository, DocumentRepository, etc. For each table in my database, I create a repository class.

These repository classes usually consist of something like this:

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

Quite the same for the basic CRUD functions for 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 this method to the PersonRepository class.

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

Another idea I had was to create the DataAccess.cs class and use all of these methods (we will talk about 4-5 methods for existing tables) and share them by region.

What do more experienced programmers do and what suggestions do you offer for a young programmer (I’m 20 years old)?

Answer 1

Here are the problems with what you do above:

Using a List When You Should Use IQueryable

Why use AsQueryable () instead of List ()?

Basically, you should never receive data until you need it. Thanks to your approach, you always get data from the database, and subsequent LINQ queries will act on all the data. Why not just create queries and let LINQ refine things until they get only what you need, and only when you need it?

Using methods when extension methods are ideal

You create a utility class with things like GetPeopleFromDOB. Why not make it an extension method? If you do all of them, return IQueryable, you can also use them in turn. For instance. GetPeople().StatusEnabled().BornInJuly().AgeIsGreaterThan( 57 ); Also, if you should do this, at least think about it in a partial class or a static utility class.

Consider using the ActiveRecord / Repository template as the root

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

You are currently creating several hard-coded repositories, but should you base them on the repository?

Why not use validators?

If you use ASP.NET MVC, validation is part and parcel, however with webforms you can also use data annotation validators.

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

Answer 2

I would adhere to the principle of single responsibility and adhere to your repository classes. If your database grows over time, imagine how much the DataAccess class can become. You will leave him a little, but he will grow and grow to such an extent that you have thousands of lines of code in the class (I used to see 15,000 lines in this class), and you are stuck!

Answer 3

You will want to use the repository to connect directly to your DBML using L2S.

Then you want to create a service that will talk to your repository.

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

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 , as you mentioned above.

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