Click here to Skip to main content
13,146,742 members (79,944 online)
Click here to Skip to main content
Add your own
alternative version

Stats

46.7K views
459 downloads
13 bookmarked
Posted 13 Dec 2007

LINQ to SQL versus ADO.NET

, 13 Dec 2007
Rate this:
Please Sign up or sign in to vote.
A simple speed comparison.

Screenshot - linqAdoComplex.JPG

Introduction

The article shows a simple comparison between LINQ to SQL and ADO.NET with regards to the execution time of selecting data from a SQL Server database. So, what is LINQ to SQL? LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically. LINQ to SQL gives you your database, tables, and columns as objects that you can use directly in your C# or VB.NET code. Here, we are going to test it with ADO.NET:

myDatabase.CustomersTable.CustomerNameColumn;

How to add LINQ

  • LINQ is shipped with Microsoft Visual Studio 2008, you can download the beta for free from Microsoft.
  • Open Visual Studio and create a new Windows Application project.
  • Add New Item and choose LINQ to SQL classes.
  • You will get a class .dbml that is inherited from DataContext.
  • Now, from the Server Explorer, make a connection to the AdventureWorks database and then drag and drop the table Product in the designer.
  • Screenshot - productTable_small.jpg

  • Move to the code..

Using the code

This application uses both ADO and LINQ, and calculates the time they take to select data from the AdventureWorks database. We are going to do something like select * from Products. In the LINQ button click event..

//AdventureWorksDataContext is the dbml class we have already added above

AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
//Products is the table we draged and dropped in the dbml designer
var _data = from _products in AdventureDB.Products 
            select _products;
dataGridViewLinq.DataSource = _data;
//Thats it!

In the ADO button click event:

SqlDataAdapter adapter = new SqlDataAdapter("select * from Production.Products",
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);
dataGridViewADO.DataSource = ds.Tables[0];

Let's make it more complex; drag and drop the ProductCategroy table and the ProductSubCategory table in the DBML designer:

Screenshot - dbmlComplex_small.jpg

Using LINQ:

var _t = from o in AdventureDB.ProductSubcategories
                     //here we use the relations between these tables

                     where o.ProductCategory.ProductSubcategories.Count > 3
                     select new
                     {
                         o.ProductCategoryID,
                         o.Name,
                     };
                     
dataGridViewLinq.DataSource = _t;

Using ADO.NET:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT [t0].[ProductCategoryID],
     [t0].[Name]"
    + "FROM [Production].[ProductSubcategory] AS [t0]"
    + "WHERE (("
    + "SELECT COUNT(*)"
    + "FROM [Production].[ProductCategory] AS [t1],
         [Production].[ProductSubcategory] AS [t2]"
    + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
         [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
    + ")) > 3",
         "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);

dataGridViewADO.DataSource = ds.Tables[0];

The code:

private void buttonLinq_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;


    AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
   "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    //var _data = from _products in AdventureDB.Products
    //                 select _products;
    //dataGridViewLinq.DataSource = _data;

    var _t = from o in AdventureDB.ProductSubcategories
             where o.ProductCategory.ProductSubcategories.Count > 3
             select new
             {
                 o.ProductCategoryID,
                 o.Name,
             };
    dataGridViewLinq.DataSource = _t;

    #region time_Calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelLinqTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelLinqTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 
        throw new Exception("timeout");
    }
    #endregion

}

private void buttonADO_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;

    SqlDataAdapter adapter = new SqlDataAdapter(
          "SELECT [t0].[ProductCategoryID], [t0].[Name]"
        + "FROM [Production].[ProductSubcategory] AS [t0]"
        + "WHERE (("
        + "SELECT COUNT(*)"
        + "FROM [Production].[ProductCategory] AS [t1],
            [Production].[ProductSubcategory] AS [t2]"
        + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
            [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
        + ")) > 3",
          "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    DataSet ds = new DataSet();
    int x = adapter.Fill(ds);
    dataGridViewADO.DataSource = ds.Tables[0];

    #region time_calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelADOTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelADOTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 

        throw new Exception("timeout");
    }
    #endregion
}

Change the query each time to avoid caching. Learn more about LINQ to SQL here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Islam ElDemery
Web Developer Business Development Gate
Egypt Egypt

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 2 Pin
jpratik10-Apr-12 3:18
memberjpratik10-Apr-12 3:18 
GeneralMy vote of 1 Pin
Christian Wikander9-Jan-09 0:58
memberChristian Wikander9-Jan-09 0:58 
GeneralYou do realise... Pin
Colin Angus Mackay13-Dec-07 13:37
mvpColin Angus Mackay13-Dec-07 13:37 
GeneralRe: You do realise... Pin
mr_lasseter13-Dec-07 15:36
membermr_lasseter13-Dec-07 15:36 
GeneralRe: You do realise... Pin
Islam ElDemery14-Dec-07 0:54
memberIslam ElDemery14-Dec-07 0:54 
GeneralRe: You do realise... Pin
mr_lasseter14-Dec-07 2:14
membermr_lasseter14-Dec-07 2:14 
GeneralRe: You do realise... [modified] Pin
Taner Riffat23-Jul-08 19:20
memberTaner Riffat23-Jul-08 19:20 
GeneralRe: You do realise... Pin
mr_lasseter24-Jul-08 3:35
membermr_lasseter24-Jul-08 3:35 
GeneralRe: You do realise... Pin
Taner Riffat27-Jul-08 19:46
memberTaner Riffat27-Jul-08 19:46 
GeneralIt's good to compare Pin
Ben Daniel13-Dec-07 11:28
memberBen Daniel13-Dec-07 11:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 14 Dec 2007
Article Copyright 2007 by Islam ElDemery
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid