Data Access Layer - LINQ-To-SQL and generics. Can I optimize this?

Question

Hi,

We are working on improving our DAL which is written in LINQ that talks to the MS SQL database. Our goal is to achieve good re-usability with as little code as possible.

LINQ generated files are making a use of generics and reflection to map LINQ generated classes to the SQL objects (tables and views in our case).

Please see the example of the existing accessor. This method resides in the partial class that contains custom constructors, accessors and mutators:

public clsDVD getDVD(int dvdId)
{
   try
   {
      using (DataContext dvdDC = new DataContext(ConnectionStringManager.getLiveConnStr()))
      {
                    // Deferred loading
                    dvdDC.DeferredLoadingEnabled = false;

                    var tDVD = dvdDC.GetTable<DVD>();

                    return (from t in tDVD 
                            // Filter on DVD Id
                            where t.DVDId == (dvdId)
                            select t).Single();                   
      }
   catch (Exception e)
   {
       Logger.Log("Can't get requested DVD.", e);
       throw;
   }
 }

I believe that this is very easy to maintain, since the most of the work is done after var tDVD

It has been suggested not to declare tDVD at all and use dataContext.TableName, but behind the scenes it still calls GetTable<>.

The only way I can see of improving this is breaking this one partial class into 4 (CRUD) partial classes. E.g.

clsDVD_Select, clsDVD_Update, clsDVD_Insert, clsDVD_Delete

In this case each class will represent a set of behaviours.

The idea that we are discussing is to see whether it's possible to use generics on top of LINQ generics.

For example, instead of having the partial classes, we would figure out the properties of the class on the go by using reflection against the SQL database. My first concern here is performance impact. How significant will it be.

Instead of ClsDVD.getDVD(1231) we'd have something on the lines of: GenericDC.Select<DVD>(1231)

.Select method would figure out the primary key and run a select query on that table. I'm struggling to understand how can this work. Lets say we can get this to work for the simple select, i.e. select with a filter on primary key, but what is going to happen when we start doing complex joins and group by selects. What happens when we want to have multiple selects per DVD class?

My final concern is to do with good practices. I have been told before that it's good to have consistant code. For example, If I decide to use datatables , than I should stick to datatables throughout the project. It's a bad idea to have half of the project with datatables and another half with user defined classes. Do you agree on this?

I'm in a position where I think that existing implementation is quite good but maybe I'm missing out something very obvious and there is a much easier, more OO way of achieving the same results?

Thank you

Answer 1

Here is one way to make this situation a little more generic. Rince and repeat for the other CRUD opperations. For some sitiations the performance may be unacceptable. In those cases I would restructure that part of the program to call a non generic version.

public T GetSingleItem(Func<T,bool> idSelector ) where T : ??? // forgot what type it needs to be off the top of my head
{ 
    try 
    { 
        using (DataContext context = new DataContext(ConnectionStringManager.getLiveConnStr())) 
        { 
            context.DeferredLoadingEnabled = false; 
            return context.GetTable<T>().Single( item => idSelector( item );
        } 
    }
    catch (Exception e) 
    { 
        Logger.Log("Can't get requested item.", e); 
        throw; 
    } 
} 

This would be how you woudl have to get the item. Not quite as elegant becase you have to tell the generic function which column you are going to be using.

GenericDC.GetSingleItem<DVD>( dvd => dvd.ID == 1231 )

To make this even more generic that limiting it to a single item with an ID...

public IEnumerable<T> GetItems(Func<T,bool> selectFunction ) where T : ??? // forgot what type it needs to be off the top of my head
{ 
    try 
    { 
        using (DataContext context = new DataContext(ConnectionStringManager.getLiveConnStr())) 
        { 
            context.DeferredLoadingEnabled = false; 
            return context.GetTable<T>().Select( item => selectFunction( item );
        } 
    }
    catch (Exception e) 
    { 
        Logger.Log("Can't get requested item.", e); 
        throw; 
    } 
} 

Then you can call it like:

GenericDC.GetItems<DVD>( dvd => dvd.Title == "Title" && dvd.Cast.Contains( "Actor" ) );

Another possible solution would be to create a custom code generator that could you could modify in one place and create the similar routines for all other types. This would probably be a good solution if you are running into performace problems. You would want to limit the changes to the template piece of code that you use.