Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-17
8
Medium Priority
?
841 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

664 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