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)
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)
catch (Exception e)
Logger.Log("Can't get requested DVD.", e);
believe that this is very easy to maintain, since the most of the work is done after
It has been suggested not to declare tDVD at all and use
, but behind the scenes it still calls
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.
we'd have something on the lines of:
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?