Home > Extension Methods, IQueryable, Linq > Extending Linq.

Extending Linq.

Sorry for the delay in continuing with the discussions on regular expressions, but I got a little sidetracked with the upgrade to Visual Studio 2008 (.NET 3.5), and all the “goodies” that it brings to you. For the moment I’m going to be putting the discussion on regular expressions to one side. I will get back to them but, for the moment, I want to take you on a journey into .NET 3.5.

Possibly the biggest headline in .NET 3.5 is the introduction of Linq. I have to admit that I was highly skeptical about how useful it will actually be in the “real world”, but having played around with it for a little while I have to admit that I’m actually quite impressed. It’s a well thought out area that actually does do a lot to increase productivity. Now, in this entry I’m not going to delve into the internals of Linq and how to use it. Instead, I’m going to talk about a feature of .NET 3.5 and how it can be used to “extend” the behaviour of Linq. Specifically, we’re going to look at how to use Extension Methods to add the ability to return a DataSet and DataTable from Linq in the same way you’d return a generic list.

The following code shows what you actually need to do to extend the IQueryable object in Linq. For the moment, don’t worry about what IQueryable actually does – once you’ve used Linq for a little while it becomes apparent what this interface is for, and where it fits into the big Linq picture.

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Data.Linq; 
using System.Data.Linq.Mapping; 
using System.Reflection; 
using System.Data.Common; 
using System.Data; 
using System.Data.SqlClient; namespace LinqTesting 
{ 
 /// <summary> 
 /// This class contains miscellaneous extension methods for Linq to Sql. 
 /// </summary> 
 public static class LinqDataExtensions 
 { 
  /// <summary> 
  /// Add the ability to return a DataTable based on a particular query 
  /// to a queryable object. 
  /// </summary> 
  /// <param name="extenderItem">The IQueryable object to extend.</param> 
  /// <param name="query">The query to execute.</param> 
  /// <param name="tableName">The name of the datatable to be added.</param> 
  /// <returns>The populated DataTable.</returns> 
  public static DataTable ToDataTable(this IQueryable extenderItem, 
    DbCommand query, 
    string tableName) 
  { 
    if (query == null) 
    {       throw new ArgumentNullException("query"); 
    } 
    SqlCommand cmd = (SqlCommand)query; 
    SqlDataAdapter adapter = new SqlDataAdapter(); 
    adapter.SelectCommand = cmd; 
    DataTable dt = new DataTable(tableName); 
    try 
    { 
    cmd.Connection.Open(); 
    adapter.Fill(dt); 
    } 
    finally 
    { 
    cmd.Connection.Close();     } 
    return dt; 
  } 
  /// <summary> 
  /// Add the ability to return a DataSet based on a particular query 
  /// to a queryable object. 
  /// </summary> 
  /// <param name="extenderItem">The IQueryable object to extend.</param> 
  /// <param name="query">The query to execute.</param> 
  /// <param name="tableName">The name of the DataTable to be added.</param> 
  /// <returns>The populated dataset.</returns> 
  public static DataSet ToDataSet(this IQueryable extenderItem, 
    DbCommand query, 
    string tableName) 
  { 
    if (query == null) 
    { 
    throw new ArgumentNullException("query"); 
    } 
    return ToDataSet(extenderItem, query, null, tableName); 
  }   /// <summary> 
  /// Add the ability to return a dataset based on a particular query 
  /// to a queryable object. 
  /// </summary> 
  /// <param name="extenderItem">The IQueryable object to extend.</param> 
  /// <param name="query">A generic dictionary containing the 
  /// query to execute along with the name of the table to add it to.</param> 
  /// <returns>The populated dataset.</returns> 
  public static DataSet ToDataSet(this IQueryable extenderItem, 
    Dictionary<string, DbCommand> query) 
  { 
    if (query == null) 
    { 
    throw new ArgumentNullException("query"); 
    } 
    if (query.Count == 0) 
    { 
    throw new ArgumentException("query"); 
    } 
    return ToDataSet(extenderItem, query, null); 
  }   /// <summary> 
  /// Add the ability to return a dataset based on a particular query   /// to a queryable object. 
  /// </summary> 
  /// <param name="extenderItem">The IQueryable object to extend.</param> 
  /// <param name="query">A generic dictionary containing the 
  /// query to execute along with the name of the table to add it to.</param> 
  /// <param name="dataSet">An optional DataSet. This allows application 
  /// to add multiple tables to the dataset.</param> 
  /// <returns>The populated dataset.</returns> 
  public static DataSet ToDataSet(this IQueryable extenderItem, 
    Dictionary<string, 
    DbCommand> query, 
    DataSet dataSet) 
  { 
    if (query == null) 
    { 
    throw new ArgumentNullException("query"); 
    } 
    if (query.Count == 0) 
    { 
    throw new ArgumentException("query"); 
    } 
    if (dataSet == null)       dataSet = new DataSet();  

    foreach (KeyValuePair<string, DbCommand> kvp in query) 
    { 
      dataSet = LinqDataExtensions.ToDataSet(extenderItem, 
        kvp.Value, 
        dataSet, 
        kvp.Key); 
    } 
    return dataSet; 
  }   /// <summary> 
  /// Add the ability to return a dataset based on a particular 
  /// query to a queryable object. 
  /// </summary> 
  /// <param name="extenderItem">The IQueryable object to extend.</param> 
  /// <param name="query">The query to execute.</param> 
  /// <param name="dataSet">An optional DataSet. This allows 
  /// application to add multiple tables to the dataset.</param> 
  /// <param name="tableName">The name of the DataTable to be added.</param> 
  /// <returns>The populated dataset.</returns> 
  public static DataSet ToDataSet(this IQueryable extenderItem, 
    DbCommand query, 
    DataSet dataSet, 
    string tableName) 
  { 
    if (query == null) 
    { 
    throw new ArgumentNullException("query"); 
    } 
    if (dataSet == null) 
      dataSet = new DataSet(); 
    DataTable tbl = LinqDataExtensions.ToDataTable(extenderItem, 
      query, 
      tableName); 
    if (tbl != null) 
    { 
      if (dataSet.Tables.Contains(tableName)) 
        dataSet.Tables.Remove(tableName); 
      dataSet.Tables.Add(tbl); 
    } 
    return dataSet; 
  } 
 } 
}

A couple of points to note about the class. Extension methods have to be in static classes, and they have to be static methods. In order to note what class is being extended, you use the “this” keyword before the first parameter. The code itself is fairly self explanatory and doesn’t do anything clever. The clever bit actually occurs in the Linq side. Here’s an example:

public DataSet GetDataSet() 
{ 
    IQueryable q = GetAllQuery(); 
    return q.ToDataSet(context.GetCommand(GetAllQuery()), null, "MyTable"); 
} 

private IQueryable<MyTable> GetAllQuery() 
{ 
    IQueryable<MyTable> q = from p in context.MyTables 
                        select p;     return q; 
}

The context is the DataContext item that you will create when you import the tables using Visual Studio. And that’s it. Using similar techniques you can extend other parts of Linq as you see fit.

About these ads
  1. March 26, 2008 at 10:32 am

    Brilliant! Thanks Pete!

  2. April 26, 2008 at 12:12 pm

    Pl.help for vb.net

    Tried it with this code not able to convert into data table

    Dim q As IQueryable(Of Account) = From c In db.Accounts Select c
    Dim hg As New LinqDataExtensions
    Dim t As DataTable = hg.ToDataTable(q, Nothing, “Account”)
    DataGridView1.DataSource = t ‘(From c In db.Accounts Select c).ToList

    Public Class LinqDataExtensions

    Public Shared Function ToDataTable(ByVal extenderItem As IQueryable, ByVal query As DbCommand, ByVal tableName As String) As DataTable


    ‘ Add the ability to return a DataTable based on a particular query
    ‘ to a queryable object.

    ‘ The IQueryable object to extend.
    ‘ The query to execute.
    ‘ The name of the datatable to be added.
    ‘ The populated DataTable.

    If query Is Nothing Then Throw New ArgumentException(“query”)
    Dim cmd As SqlCommand = CType(query, SqlCommand)
    Dim adapter As SqlDataAdapter = New SqlDataAdapter()
    adapter.SelectCommand = cmd
    Dim dt As DataTable = New DataTable(tableName)
    Try
    cmd.Connection.Open()
    adapter.Fill(dt)
    Catch ex As Exception
    Throw New ArgumentException(“query”)
    Finally
    cmd.Connection.Close()
    End Try
    Return dt
    End Function


    ‘ Add the ability to return a DataSet based on a particular query
    ‘ to a queryable object.

    ‘ The IQueryable object to extend.
    ‘ The query to execute.
    ‘ The name of the DataTable to be added.
    ‘ The populated dataset.

    Public Shared Function ToDataSet(ByVal extenderItem As IQueryable, ByVal query As DbCommand, ByVal tableName As String) As DataSet
    If query Is Nothing Then Throw New ArgumentException(“query”)
    Return ToDataSet(extenderItem, query, Nothing, tableName)
    End Function


    ‘ Add the ability to return a dataset based on a particular query
    ‘ to a queryable object.

    ‘ The IQueryable object to extend.
    ‘ A generic dictionary containing the
    ‘ query to execute along with the name of the table to add it to.
    ‘ The populated dataset.

    Public Shared Function ToDataSet(ByVal extenderItem As IQueryable, ByVal query As Dictionary(Of String, DbCommand)) As DataSet
    If query Is Nothing Then Throw New ArgumentException(“query”)
    If query.Count = 0 Then Throw New ArgumentException(“query”)
    Return ToDataSet(extenderItem, query, Nothing)
    End Function


    ‘ Add the ability to return a dataset based on a particular query ‘ to a queryable object.

    ‘ The IQueryable object to extend.
    ‘ A generic dictionary containing the
    ‘ query to execute along with the name of the table to add it to.
    ‘ An optional DataSet. This allows application
    ‘ to add multiple tables to the dataset.
    ‘ The populated dataset.

    Public Shared Function ToDataSet(ByVal extenderItem As IQueryable, ByVal query As Dictionary(Of String, DbCommand), ByVal _dataSet As DataSet) As DataSet
    If query Is Nothing Then Throw New ArgumentException(“query”)
    If query.Count = 0 Then Throw New ArgumentException(“query”)
    If _dataSet Is Nothing Then _dataSet = New DataSet
    For Each kvp As KeyValuePair(Of String, DbCommand) In query
    _dataSet = LinqDataExtensions.ToDataSet(extenderItem, kvp.Value, _dataSet, kvp.Key)
    Next
    Return _dataSet
    End Function


    ‘ Add the ability to return a dataset based on a particular
    ‘ query to a queryable object.

    ‘ The IQueryable object to extend.
    ‘ The query to execute.
    ‘ An optional DataSet. This allows
    ‘ application to add multiple tables to the dataset.
    ‘ The name of the DataTable to be added.
    ‘ The populated dataset.

    Public Shared Function ToDataSet(ByVal extenderItem As IQueryable, ByVal query As DbCommand, ByVal _dataSet As DataSet, ByVal tableName As String) As DataSet
    If query Is Nothing Then Throw New ArgumentException(“query”)
    If _dataSet Is Nothing Then
    _dataSet = New DataSet
    Dim tbl As DataTable = LinqDataExtensions.ToDataTable(extenderItem, query, tableName)
    If tbl IsNot Nothing Then
    If _dataSet.Tables.Contains(tableName) Then
    _dataSet.Tables.Remove(tableName)
    _dataSet.Tables.Add(tbl)
    End If
    Return _dataSet
    End If
    End If
    Return Nothing
    End Function
    End Class

  3. May 30, 2008 at 10:04 pm

    Hi Pete,

    I’ve just finally gotten around to testing this. Have you looked at using this with SQLCE? I’m just trying out your class but things aren’t working …

    public DataSet GetDataSet()
    {
    IQueryable q = GetAllQuery();
    return q.ToDataSet(context.GetCommand(GetAllQuery()), null, “MyTable”);
    }

    I’m not seeing ToDataSet hanging off the q variable.

    strange …

  4. May 30, 2008 at 10:12 pm

    Ok … scratch that … what a complete dunce!!! I was using an s instead of an S in my namespace name!!!!

    HOW stupid can I get????

  5. peteohanlon
    May 31, 2008 at 6:37 pm

    Jammer – don’t worry about it; we all have those doh moments.

  6. October 22, 2010 at 2:42 pm

    I don’t undestand, you never use your instance of extenderItem, so… Why do you need it?! it’s just another SQL Helper and there are thousands out there…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Canny Coder

Java 8 Functional Programming with Lambda Expressions

pihole.org

Adventures in theoretical computer science, with your host, chaiguy1337

Confessions of a coder

Confessions of a WPF lover

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Follow

Get every new post delivered to your Inbox.

Join 38 other followers

%d bloggers like this: