Read-only LINQ to SQL

Question

I'm using LINQ to SQL to access my database but I'm only reading, I never insert, update or delete anything. Are there ways to optimize LINQ2SQL for this?

Answer 1

Yes there is. Linq 2 SQL will by default cache all data that you read from the DB. It needs to do this to track any changes you apply to your objects, so it can generate the necessary insert/update/delete statements when you call SubmitChanges()

If you're only reading data, this is unnessecary. You can turn off object tracking by setting the ObjectTrackingEnabled property to false on your DataContext.

Answer 2

One thing I've been told, is to avoid using the generated record class.

That is, if you have a Users table, L2S will create for you a User class, which is what it returns from the database. Instead of using that directly, you should create a "shadow" class --- all the same Properties, but nothing else, and immedaitely copy the data into those records for your use. In fact, if it's going to be exclusively read-only, you can assign them in the ctor, and only have public getters:

class myUser
{
   public string FName {get; private set}
   public string LName {get; private set}
   public myUser(User user)
   {
       this.FName = user.FName;
       this.LName = user.LName;
   }
}



var users = from u in db.Users
            where .....
            select new myUsers(u);

This avoids a lot of overhead needed to deal with the possibility of writing the object out again.