Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / C#

LINQ to SQL versus ADO.NET

Rate me:
Please Sign up or sign in to vote.
2.16/5 (18 votes)
13 Dec 2007CPOL1 min read 61.8K   518   16   13
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:

C#
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.
  • Image 2

  • 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..

C#
//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:

C#
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:

C#
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:

C#
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:

C#
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)


Written By
Web Developer Business Development Gate
Egypt Egypt

Comments and Discussions

 
GeneralMy vote of 2 Pin
jpratik10-Apr-12 3:18
jpratik10-Apr-12 3:18 
GeneralMy vote of 1 Pin
Christian Wikander9-Jan-09 0:58
Christian Wikander9-Jan-09 0:58 
GeneralYou do realise... Pin
Colin Angus Mackay13-Dec-07 13:37
Colin Angus Mackay13-Dec-07 13:37 
GeneralRe: You do realise... Pin
mr_lasseter13-Dec-07 15:36
mr_lasseter13-Dec-07 15:36 
GeneralRe: You do realise... Pin
Islam ElDemery14-Dec-07 0:54
Islam ElDemery14-Dec-07 0:54 
GeneralRe: You do realise... Pin
mr_lasseter14-Dec-07 2:14
mr_lasseter14-Dec-07 2:14 
GeneralRe: You do realise... [modified] Pin
Taner Riffat23-Jul-08 19:20
Taner Riffat23-Jul-08 19:20 
I thought LINQ was about providing object based queries to anything that has structure like data and other objects. When it comes to data LINQ can query ADO.NET datasets, SQL Server tables and Views, XML Data (files and strings in memory), and more, like an array of data, anything that is an object really, so LINQ fully integrates with the programming language (C#/VB.NET) in a natural object oriented way.

Whats more LINQ can create any object as the "result", like a form object for example, try doing that with ADO.NET...

I suspect performance is really a secondary concern when talking about LINQ...

You can contact me at howartthou@hotmail.com.

Regards
Taner

modified on Thursday, July 24, 2008 1:43 AM

GeneralRe: You do realise... Pin
mr_lasseter24-Jul-08 3:35
mr_lasseter24-Jul-08 3:35 
GeneralRe: You do realise... Pin
Taner Riffat27-Jul-08 19:46
Taner Riffat27-Jul-08 19:46 
GeneralIt's good to compare Pin
Ben Daniel13-Dec-07 11:28
Ben Daniel13-Dec-07 11:28 
GeneralRe: It's good to compare Pin
Islam ElDemery14-Dec-07 0:48
Islam ElDemery14-Dec-07 0:48 
Generalwrong conclusions [modified] Pin
Thanks for all the fish13-Dec-07 10:21
Thanks for all the fish13-Dec-07 10:21 
GeneralRe: wrong conclusions [modified] Pin
Islam ElDemery14-Dec-07 0:36
Islam ElDemery14-Dec-07 0:36 

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.