Solved

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

Posted on 2010-08-17
8
820 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:capcap
  • 5
  • 3
8 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33454084
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
 

Author Comment

by:capcap
ID: 33454139
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 33454309
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33454326
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:capcap
ID: 33454552
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33460526
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
 

Author Closing Comment

by:capcap
ID: 33462350
Was looking for example
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33468926
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now