asked on

LINQ-to-SQL DAL and Generics. Can I optimize this?

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; 
   } 
 }

Open in new window

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

var tDVD

Open in new window

It has been suggested not to declare tDVD at all and use

dataContext.TableName

Open in new window

, but behind the scenes it still calls

GetTable<>

Open in new window

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

Open in new window

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)

Open in new window

we'd have something on the lines of:

GenericDC.Select<DVD>(1231)

Open in new window

.Select

Open in new window

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

Bob Learned

Musings:

1) Less code is not always better code

2) Reflection is, by its very nature, a pretty heavy operation, and should be used with caution.

3) I would suggest using a single instance of a DataContext, rather than creating a new instance for each call to a DAL method.

4) I have read some Scott Guthrie blogs about dynamic LINQ, but I don't think that it the best course of action, since it is not compiled, so you would need to depend on run-time errors, instead of compile-time checking.

5) Generics can really simplify your code, but is not always a good choice. If you have to use a big hammer to whack it into place, or if it requires some fancy footwork, that makes it more difficult to understand and maintain.

capcap

ASKER

Hi, thanks for this.

How would you use a single instance of a data context? As far as I'm aware, the data context is used for change tracking. How would it work only with one instance?

Your points about run-time errors are great, I don't think that we have considered those yet.

In terms of maintenance - yes, I think that to start with it'll be more difficult to maintain the code, but once we are all up to speed on generics it should become easier.

So yes, can you please provide an example of using a single data context or point to a resource that describes this approach?

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

Sample usage:

        DataLayer _dataLayer = new DataLayer(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString);

        Product product = _dataLayer.GetProductById(1);
        List<Product> categoryProductList = _dataLayer.GetProductListForCategory("Bikes");

Open in new window

capcap

ASKER

I can see how this makes the code nicer to work with. I guess there is still an overhead of calling GetTable<SomeType>() , for example _context.Products. behind the scenes would call GetTable<Product>(). Do you know whether it's possible to cache this or call GetTable only once?

For example if I run 500 selects, I'd call GetTable<Type>() 500 times, is it possible to avoid this?

Bob Learned

There are different possibilities for caching, depending on your development platform (Windows Forms, ASP.NET), such as the Enterprise Library caching block, or the ASP.NET caching mechanisms.

If you need to run 500 selects, then you would need to determine how you would want to get those 500 in a single statement.

capcap

ASKER

Was looking for example

Bob Learned

If you didn't get what you needed, you can continue to ask for clarification. I could have given you an example, but I wasn't quite sure what you were looking for, and where your question was headed. My intent is not to leave you hanging, but to give you as much information as you need--without resorting to a never-ending question.

What is your project like? What is your data layer usage like? How we can get 500 selects into a single statement depends on your usage?