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
capcapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
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.
0
capcapAuthor Commented:
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?
0
Bob LearnedCommented:
Here is an example, which connects to the SQL Server 2005 AdventureWorks sample database:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Data access layer to the SQL Server 2005 AdventureWorks sample database
/// </summary>
public class DataLayer
{

    // A single instance of a LINQ-to-SQL 
    private AdventureWorksDataContext _context;

    /// <summary>
    /// Opens the connection to the database, using the specified connection.
    /// </summary>
    /// <param name="connectionString">The connection string to the database.</param>
    public DataLayer(string connectionString)
    {
        _context = new AdventureWorksDataContext(connectionString);
    }

    /// <summary>
    /// Gets the product by id.
    /// </summary>
    /// <param name="productId">The product id.</param>
    /// <returns></returns>
    public Product GetProductById(int productId)
    {
        return _context.Products.Where(p => p.ProductID == productId).FirstOrDefault();
    }

    /// <summary>
    /// Gets the product list for the specified category.
    /// </summary>
    /// <param name="categoryName">Name of the category.</param>
    /// <returns>A list of Product objects</returns>
    public List<Product> GetProductListForCategory(string categoryName)
    {
        var query = from product in _context.Products
                    join subCategory in _context.ProductSubcategories
                        on product.ProductSubcategoryID equals subCategory.ProductSubcategoryID
                    join category in _context.ProductCategories
                        on subCategory.ProductCategoryID equals category.ProductCategoryID
                    where category.Name == categoryName
                    select product;

        return query.ToList();
    }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Bob LearnedCommented:
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

0
capcapAuthor Commented:
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?
0
Bob LearnedCommented:
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.
0
capcapAuthor Commented:
Was looking for example
0
Bob LearnedCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.