Description and Querying Samples

Description / Code sample article
Table of Contents
Summary

LINQ to DataSet makes it easier and faster to query over data cached in a Dataset object.

Description

LINQ to DataSet simplifies querying by enabling developers to write Linq-based queries, instead of using a separate query language. This is especially useful at Visual Studio level due to compile-time syntax checking, static typing, and IntelliSense support.

The LINQ to DataSet functionality is exposed primarily through the extension methods in the DataRowExtensions (System.Data.DataRowExtensions namespace) and DataTableExtensions (System.Data.DataTableExtensions namespace) classes.

Linq to Datasets and ADO.NET


First step

The DataTable class does not implement the IEnumerable and IQueryable interfaces therefore to be used as datasource for Linq queries must be converted to an enumerable collection throught the extension AsEnumerable() emthod to obtain an IEnumerable<DataRow> collection.

Why to use

The existing methods that can be used to write queries against the DataSet all use string based expressions, which have a SQL like syntax.
Nevertheless these methods are restricted to a limited set of operators that exist in the DataSet and are string-based without any compile time checking for validity.

  • Provide Linq-based queries for datasets
  • Type-safe queries (type checking and validation at compile time)
Untyped Dataset

In case of an untyped dataset the values retrieved from a DataTable are returned as type “object”. Therefore:

  • The operator "Field" is used to return the value of a data column as the generic type T, thus enabling type checking
  • In case of a DBNull value, the operator "Field" will convert the value retrieved to a nullable type with a value of NUL that cn be used in the query
Required .NET Namespaces

The specific libraries required for Linq To Dataset component are:

  • System.Linq.dll
  • System.Data.dll
  • System.Data.DataSetExtensions.dll
Operations
Simple query
How to do it

// Linq Query Sintax

var query =
       from row in customerDataTable.AsEnumerable()
       where row.Field<string>("LastName") == "Smith"
       select row.Field<string>(“FirstName”);

// Linq Method Sintax

var query = customerDataTable.AsEnumerable()
          .Where(
             row =>
                row.Field<string>("LastName") == "Smith")
          .Select(row => row.Field<string>("FirstName"));

Query with COUNT operator
How to do it
// Linq Query Sintax

var query =
       from row in customerDataTable.AsEnumerable()
                  .Count(dr => dr.Field<int>("Total") > 100);

// Linq Method Sintax

var query = orderDataTable.AsEnumerable()
           	.Count(row => row.Field<int>("Total") > 100);
Query with GROUP operator
How to do it
// Linq Query Sintax

var query =
       from crow in customerDataTable.AsEnumerable()
       from orow in ordersDataTable.AsEnumerable()
       where crow.Field<string>("CustomerID") == orow.Field<string>("CustomerID")
       group orow by row2.Field<int>("OrderID")
       into  items
       select new {
                   Customer =
                      crow.Field<string>("CustomerName"),
                   OrderID = orow.Field<int>("OrderID"),
                   Total = orow.Field<decimal>("Total")
                  }	

// Linq Method Sintax

var query = customerDataTable.AsEnumerable()
		.Join
                   (orders,
                    o => o.Field<string>("CustomerID"),
                    c => c.Field<string>("CustomerID"),
                    (c, o) => new {
                                Customer = c.Field<string>("CustomerName"),
                                OrderID = o.Field<int>("OrderID"),
                                Total = o.Field<decimal>("Total")
                             }
                   )
	        .GroupBy(r => r.OrderID);
Query using UDF
How to do it
// Linq Query Sintax

public int Distance(int pZip)
{
	 // TODO: compute distance
	...
}

var query = from row in orderDataTable.AsEnumerable()
            orderby Distance(row.Field<int>("Zip")) descending
            select row.Field<int>("OrderID");
Query with filter by NULL
How to do it
// Linq Query Sintax

var query =
       from row in customerDataTable.AsEnumerable()
       where row.Field<datetime>("OrderDate") == null
       select row.Field<int>("OrderID");

// Linq Method Sintax

var query = orderDataTable.AsEnumerable()
    		.Where(row => row.Field<datetime>("OrderDate") == null)
		.Select(row => row.Field<int>("OrderID"));
Query with Filter by NULL (2)
How to do it
// Linq Query Sintax

var query =
       from row in customerDataTable.AsEnumerable()
       where row.IsNull("OrderDate") == true
       select row.Field<int>("OrderID");

// Linq Method Sintax

var query = orderDataTable.AsEnumerable()
		    .Where(row => row.IsNull("OrderDate") == true)
		    .Select(row => row.Field<int>("OrderID"));
Query with Filter by NOT NULL
Remarks

In the example below, applying the “year” method over a NULL column will originate a “NullReferenceException” exception. Therefore in order to avoid the exception the “IsNull” method must be used in the query.

How to do it
// Linq Query Sintax

var query = from row in orderDataTable.AsEnumerable()
             where
                 row.IsNull("OrderDate") == false
                  &&
                row.Field<DateTime>("OrderDate").Year == 2006
            select row.Field<int>("OrderID");

// Linq Method Sintax

var query = orderDataTable.AsEnumerable()
		    .Where(row => row.IsNull("OrderDate") == true
		            && row.Field<DateTime>("OrderDate").Year == 2006)
		    .Select(row => row.Field<int>("OrderID"));
Query to fill a data table
Remarks

1) The generic type T can only be “DataRow” or derived from “DataRow”
2) Copy table force immediate execution of the query
3) Copy table does not understand relationships and cannpt produce multiple datta tables
4) it is not possible to updat rows in the databaseif obtained from a copy table operation

How to do it
DataSet ds = ...

var q = from role in ds.Role
        select role;

DataTable dtRole = q.CopyToDataTable<DataRow>();
Query with WHERE clause using IN operator [1]
How to do it
// Linq Query Sintax

var query = from row in entitiesDataTable.AsEnumerable()
            where (new List<short>() {1,2,3}).Contains(row.Field<short>("estado"))))
            select row;

// Linq Method Sintax			

var query = entitiesDataTable.AsEnumerable()
				.Where(row => (new List<short>() {1,2,3}).Contains(row.Field<short>("estado"))))
Query with WHERE clause using IN operator [2]
How to do it
// Linq Query Sintax

var query0 = (from row in entitiesDataTable1.AsEnumerable()
			  where row.Field<short>("estado") > 0
			  select row.Field<short>("estado")).Distinct();

var query = from row in entitiesDataTable2.AsEnumerable()
			 where query0.Contains(row.Field<short>("estado"))
			 select row;

// Linq Method Sintax			

var query0 = entitiesDataTable1.AsEnumerable()
				.Where(row => row.Field<short>("estado") > 0);

var query = entitiesDataTable2.AsEnumerable()
				.Where(row => query0.Contains(row.Field<short>("estado"))))
Query with WHERE clause using NOT IN operator
How to do it
// Linq Query Sintax

var query0 = from row in entitiesDataTable1.AsEnumerable()
             where row.Field<short>("estado") > 0
             select row.Field<short>("estado");

var query = (from row in entitiesDataTable2.AsEnumerable()
             select row.Field<short>("estado")).Except(query0);

// Linq Method Sintax			

var query0 = entitiesDataTable1.AsEnumerable()
				.Where(row => row.Field<short>("estado") > 0);

var query = entitiesDataTable2.AsEnumerable().Except(query0);
DBNull and Nullable Types

In case of some columsn return NULL values the conversion must be using nullable types.

Remarks

Only non-string type values can be considered

How to do it

//option 1
single? fieldvalue = dt.Field<single?>( "FieldName" );

//option 2
single fieldvalue = dt.Field<single?>( "FieldName" ) ?? 0;

//option 3
var fieldvalue = dt.Field<single?>( "FieldName" );

// converting to string
string fieldvaluetxt = fieldvalue.HasValue ? ((int)fieldvalue).ToString(CultureInfo.CurrentCulture.NumberFormat).Replace(",", ".") : "0"
DBNull and Nullable Types (STRING type)
References

[1] http://msdn.microsoft.com/en-us/library/bb386977.aspx