The Transition to LINQ

We started our first ASP.Net 3.5 project and today I had to work with the Linq database access. Heralded by many as a complete revolution in the way of doing ORM, LInQ is not that simple to move to. A lot of stuff that has become second nature for me as a programmer now must be thrown in the garbage bin.

I'll skip the "how to do LInQ" (there are far too many tutorials on the net already) and get down to the problem. Let me give you a simple example. You want to select a User from the Users table. Let's see how I would have done it until now:

User u=User.SelectById(idUser);

or maybe

User u=new User(); u.IdUser=idUser;
u.SelectOne();

In LInQ you do it like this:

var c=new MyDataContext();
User user=(from u in users where u.idUser=idUser select u).FirstOrDefault();

or maybe

var c=new MyDataContext();
User user=c.Users.Select(u=>u.IdUser=idUser).FirstOrDefault();

I am typing this by hand, forgive me the occasional typos or syntax errors.

Well, my eyes scream for an encapsulation of this into the User class. We'll do that by creating a new User.cs file that contains a User partial class that will just mold on the LInQ generated one, then adding methods like SelectById.

Ok, I have the bloody user. I want to change something, let's say rename him or changing the password, then save the changes to the database. Here it gets tricky.

User u=User.SelectById(idUser);
u.Password="New Password";
....?!

In LInQ you need to do a DataContext.SubmitChanges(); but since I encapsulated the select functionality in the User class, I have no reference to the DataContext. Now here are a few solutions for doing this without saving the linq queries in the interface:

1. Add the methods to the context class itself, stuff like SelectUserById();. You would still need to instantiate the DataContext though, in every query
2. Add a second out or ref parameter to the methods so that you can get a reference to the DataContext used.
3. Make a method for each operation, like User.SetPasswordById();, that would become quickly quite cumbersome.
4. Add a reference to the DataContext in the User object, but that would become troublesome for operations like SelectAll

.

I am still not satisfied with any of these solutions. I am open to suggestions. I will link to this little article I found that suggests encapsulating the LInQ queries in separate extensions methods: Implementing ORM-independent Linq queries