LINQ to DataSet

Hi,

- LINQ to DataSet is not intended to replace ADO.NET 2.0 code.

- Rather, the LINQ to DataSet code sits on top of and works in conjunction with the ADO.NET 2.0 code and might be useful in scenarios where data is coming from multiple data providers, is querying locally, and is performing reporting and analysis.

Applicability –

- LINQ for data is supported in two basic ways. You can write LINQ to DataSet code that provides for querying tables in DataSets, and you can define entities and use LINQ to SQL to query entities—classes that map to tables.

- Consider using LINQ to DataSets only if you are incorporating LINQ into existing code that uses ADO.NET DataSets and tables.

- If you are writing new code, consider using LINQ to SQL and object-relational maps entities mapped to SQL tables.

- Since a DataSet can be obtained using normal ADO.NET SQL queries, LINQ to DataSet allows LINQ queries over any database that can be queried with ADO.NET. This provides a far more dynamic database-querying interface than LINQ to SQL.

- Support for DataSets with LINQ is exposed primarily through the DataRowExtensions and the DataTableExtensions classes.

- DataRowExtensions introduces the extension methods Field and SetField, and the

- DataTableExtensions class introduces the extension methods AsDataView, AsEnumerable, and CopyToDataTable.

- Table extension methods yield a queryable sequence.

- Row extensions provide access to field-level data.

Table Extension –

DataSet data = new DataSet();

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

SqlCommand command = new SqlCommand(“SELECT * FROM Suppliers”, connection);

command.CommandType = CommandType.Text;

SqlDataAdapter adapter = new SqlDataAdapter(command);

adapter.Fill(data, “Suppliers”);

}

DataTable supplierTable = data.Tables[“Suppliers”];

IEnumerable<DataRow> suppliers = from supplier in supplierTable.AsEnumerable()

select supplier;

foreach(DataRow row in suppliers)

{

Console.WriteLine(row.Field<string>(“CompanyName”));

}

Row Extension –

IEnumerable<DataRow> details = from detail in detailTable.AsEnumerable()

where detail.Field<float>(“Discount”) > 0.10f

select detail;

foreach(DataRow row in details)

{

Console.WriteLine(row.Field<int>(“OrderID”));

}

Defining a Join with DataSets : Equi-Join

const string sql = “SELECT * FROM Orders;” “SELECT * FROM [Order Details];”;

DataSet data = new DataSet();

using(SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

SqlCommand command = new SqlCommand(sql, connection);

command.CommandType = CommandType.Text;

SqlDataAdapter adapter = new SqlDataAdapter(command);

adapter.Fill(data);

}

DataTable orders = data.Tables[0];

DataTable orderDetails = data.Tables[1];

var orderResults = from order in orders.AsEnumerable()

join detail in orderDetails.AsEnumerable()

on order.Field<int>(“OrderID”)

equals detail.Field<int>(“OrderID”)

select new {

OrderID=order.Field<int>(“OrderID”),

CustomerID=order.Field<string>(“CustomerID”),

ProductID=detail.Field<int>(“ProductID”),

UnitPrice=detail.Field<decimal>(“UnitPrice”),

Quantity=detail.Field<Int16>(“Quantity”),

Discount=detail.Field<float>(“Discount”)};

Console.WriteLine(“Orders & Details”);

foreach(var result in orderResults)

{

Console.WriteLine(“Order ID: {0}”, result.OrderID);

Console.WriteLine(“Customer ID: {0}”, result.CustomerID);

}

Another approach –

var productsFromMultipleSuppliers

from product1 in products.AsEnumerable()

from product2 in products.AsEnumerable()

where product1.Field<string>(“ProductName”) == product2.Field<string>(“ProductName”)

&& product1.Field<int>(“SupplierID”) != product2.Field<int>(“SupplierID”)

select new

{

ProductName = product1.Field<string>(“ProductName”),

Supplier1 = product1.Field<int>(“SupplierID”),

Supplier1Price = product1.Field<decimal>(“UnitPrice”),

Supplier2 = product2.Field<int>(“SupplierID”),

Supplier2Price = product1.Field<decimal>(“UnitPrice”)

};

Sorting Against DataTables

IEnumerable<DataRow> details = from detail in detailTable.AsEnumerable()

where detail.Field<float>(“Discount”) > 0.10f

orderby detail.Field<int>(“ProductID”)

select detail;

Defining a Left Join and a Word about Right Joins

- The left join and right join are not supported by keywords in LINQ.

- However, the left join (or left outer join) can be constructed by flattening the group join with the DefaultIfEmpty extension method.

- A left outer join is constructed with two sequences: a Group Join and the DefaultIfEmpty extension method.

- In short, the left join needs an empty child DataRow in the event there is no matching child row.

- This required DataTable.NewRow method —in select new—to use nullable types for child data.

- This is necessary because empty new rows for missing children will be initialized with nulls.

DataSet dataset = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

const string SQL = “SELECT * FROM CUSTOMERS;SELECT * FROM ORDERS”;

SqlDataAdapter adapter = new SqlDataAdapter(SQL, connection);

adapter.Fill(dataset);

}

DataTable customers = dataset.Tables[0];

DataTable orders = dataset.Tables[1];

var customersWithoutOrders = from customer in customers.AsEnumerable()

join order in orders.AsEnumerable()

on customer.Field<string>(“CustomerID”) equals

order.Field<string>(“CustomerID”) into children

from child in children.DefaultIfEmpty(orders.NewRow())

let OrderID = child.Field<int?>(“OrderID”)

where OrderID == null

select new

{

Company=customer.Field<string>(“CompanyName”),

City=customer.Field<string>(“City”),

Phone=customer.Field<string>(“Phone”),

OrderID=child.Field<int?>(“OrderID”),

OrderDate=child.Field<DateTime?>(“OrderDate”),

RequiredDate=child.Field<DateTime?>(“RequiredDate”),

ShipCity=child.Field<string>(“ShipCity”)

};

foreach(var item in customersWithoutOrders)

{

Console.WriteLine(“Company: {0}”, item.Company);

}

DataRow Set Operators

- There are a handful of Standard Query Operators that exist for the purpose of making sequence set-type comparisons.

- They are - Distinct, Except, Intersect, Union, and SequenceEqual operators

- For each of these set-type operators, determining sequence element equality is necessary to perform the appropriate set operation. These operators perform Element Comparisons by calling the GetHashCode and Equals methods on the elements. For a DataRow, this results in a Reference Comparison, which is not the desired behavior. This will result in the incorrect determination of element equality, thereby causing the operators to return erroneous results. Because of this, each of these operators has an additional prototype that is not requird in the LINQ to Objects.

- This additional prototype allows an IEqualityComparer object to be provided as an argument. Conveniently, a comparer object has been provided for us specifically for these versions of the operators - System.Data.DataRowComparer.Default

Distinct operator -

public static IEnumerable<T> Distinct<T> (this IEnumerable<T> source, IEqualityComparer<T> comparer);

- Samples

DataTable dt = GetDataTable(students);

IEnumerable<DataRow> distinct = dt.AsEnumerable().Distinct(DataRowComparer.Default);

Output –

Before

After

Id Name

=============================

1 Joe Rattz

6 Ulyses Hutchens

19 Bob Tanko

45 Erin Doutensal

1 Joe Rattz

12 Bob Mapplethorpe

17 Anthony Adams

32 Dignan Stephens

Id Name

=============================

1 Joe Rattz

6 Ulyses Hutchens

19 Bob Tanko

45 Erin Doutensal

~~1 Joe Rattz~~

12 Bob Mapplethorpe

17 Anthony Adams

32 Dignan Stephens

Except operator -

public static IEnumerable<T> Except<T> (this IEnumerable<T> first, IEnumerable<T> second, IEqualityComparer<T> comparer);

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

Student[] students2 = {

new Student { Id = 5, Name = "Abe Henry" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 29, Name = "Future Man" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataTable dt2 = GetDataTable(students2);

IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

IEnumerable<DataRow> except = seq1.Except(seq2, System.Data.DataRowComparer.Default);

Id Name

=============================

1 Joe Rattz

13 Stacy Sinclair

Intersect operator -

public static IEnumerable<T> Intersect<T> (this IEnumerable<T> first, IEnumerable<T> second, IEqualityComparer<T> comparer);

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataTable dt2 = GetDataTable(students2);

IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

IEnumerable<DataRow> intersect= seq1.Intersect(seq2, System.Data.DataRowComparer.Default);

Id Name

=============================

7 Anthony Adams

72 Dignan Stephens

Union operator -

public static IEnumerable<T> Union<T> (this IEnumerable<T> first, IEnumerable<T> second, IEqualityComparer<T> comparer);

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataTable dt2 = GetDataTable(students2);

IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

IEnumerable<DataRow> union= seq1.Union(seq2, System.Data.DataRowComparer.Default);

Id Name

=============================

1 Joe Rattz

7 Anthony Adams

13 Stacy Sinclair

72 Dignan Stephens

5 Abe Henry

29 Future Man

Note – No repeative records

SequenceEqual operator -

- The SequenceEqual operator compares two sequences of DataRow objects to determine whether they are equal.

- It enumerates two source sequences, comparing the corresponding DataRow objects. If the two source sequences have the Same Number Of Records, and if all the Corresponding Datarow Objects Are Equal, true is returned. Otherwise, the two sequences are not equal, and false is returned.

public static IEnumerable<T> SequenceEqual<T> (this IEnumerable<T> first, IEnumerable<T> second, IEqualityComparer<T> comparer);

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataTable dt2 = GetDataTable(students2);

IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

bool equal = seq1.SequenceEqual(seq2, System.Data.DataRowComparer.Default);

Output - True

DataRow Field Operators

Field<T>

- With DataRow objects, we have a problem. Their DataColumn values do not get compared properly for equality when they are accessed with the DataRow object’s indexer if the Column Is A Value-Type.

- The reason is that because the column’s data type could be any type, the indexer returns an object of type System.Object. This means that if a column is of type int, it is a value-type, and it must get packaged into an object of type Object. This boxing is where the problem lies.

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

StudentClass[] classDesignations = {

new StudentClass { Id = 1, Class = "Sophmore" },

new StudentClass { Id = 7, Class = "Freshman" },

new StudentClass { Id = 13, Class = "Graduate" },

new StudentClass { Id = 72, Class = "Senior" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataTable dt2 = GetDataTable2(classDesignations);

IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

string anthonysClass = (from s in seq1

where s.Field<string>("Name") == "Anthony Adams"

from c in seq2

where c["Id"] == s["Id"]

select (string)c["Class"]).SingleOrDefault<string>();

Console.WriteLine("Anthony's Class is: {0}", anthonysClass != null ? anthonysClass : "null");3

Output - Anthony's Class is: null

Reason - Since the column value data types are strings, they will get boxed to System.Object, which means there will be a problem determining equality.

- Lets solve the Boxing Problem.

where (int)c["Id"] == (int) s["Id"]

Output - Anthony's Class is: Freshman

Problem Solved – But ??

- So that solves the boxing problem. However, there is still one other problem.

- What if in case if the value is Null - DBNull.Value. Then if that column’s value is DBNull.Value, casting it to another data type will Throw An Exception.

- Lets solve the Both Boxing & Null Problem.

- Perfect Soluion - Here comes the usage of Field<T> and SetField<T> operators - Provide solution to both of these problems— Boxed Value Comparisons and Null Handling.

where c.Field<int>("Id") == s.Field<int>("Id")

Output - Anthony's Class is: Freshman

- Syntaxs-

1	public static T Field ( this DataRow first, System.Data.DataColumn column, System.Data.DataRowVersion version);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>(dt1.Columns[0], DataRowVersion.Current)).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 1 is: {0}", id);
2	public static T Field ( this DataRow first, string columnName, System.Data.DataRowVersion version);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>("Id", DataRowVersion.Current)).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 2 is: {0}", id);
3	public static T Field (this DataRow first, int ordinal, System.Data.DataRowVersion version);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>(0, DataRowVersion.Current)).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 3 is: {0}", id);
4	public static T Field ( this DataRow first, System.Data.DataColumn column);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>(dt1.Columns[0])).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 4 is: {0}", id);
5	public static T Field (this DataRow first, string columnName);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>("Id")).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 5 is: {0}", id);
6	public static T Field (this DataRow first, int ordinal);	id = (from s in seq1 where s.Field<string>("Name") == "Anthony Adams" select s.Field<int>(0)).Single<int>(); Console.WriteLine("Anthony's Id retrieved with prototype 6 is: {0}", id);

Input

Output

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

int id;

Anthony's Id retrieved with prototype 1 is: 7

Anthony's Id retrieved with prototype 2 is: 7

Anthony's Id retrieved with prototype 3 is: 7

Anthony's Id retrieved with prototype 4 is: 7

Anthony's Id retrieved with prototype 5 is: 7

Anthony's Id retrieved with prototype 6 is: 7

SetField<T>

- Similar to the Field operator this also solve the Both Boxing & Null Problem.

- Syntaxs-

public static void SetField ( this DataRow first, System.Data.DataColumn column, T value);

(from s in seq1

where s.Field<string>("Name") == "Anthony Adams"

select s).Single<DataRow>().SetField(dt1.Columns[1], "George Oscar Bluth");

Console.WriteLine("{0}Results after calling prototype 1:", System.Environment.NewLine);

foreach (DataRow dataRow in seq1)

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),

dataRow.Field<string>("Name"));

}

public static void SetField (this DataRow first, string columnName, T value);

(from s in seq1

where s.Field<string>("Name") == "George Oscar Bluth"

select s).Single<DataRow>().SetField("Name", "Michael Bluth");

Console.WriteLine("{0}Results after calling prototype 1:", System.Environment.NewLine);

foreach (DataRow dataRow in seq1)

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),

dataRow.Field<string>("Name"));

}

public static void SetField (this DataRow first, int ordinal, T value);

(from s in seq1

where s.Field<string>("Name") == "Michael Bluth"

select s).Single<DataRow>().SetField("Name", "Tony Wonder");

Console.WriteLine("{0}Results after calling prototype 1:", System.Environment.NewLine);

foreach (DataRow dataRow in seq1)

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),

dataRow.Field<string>("Name"));

}

Input

Output

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

Console.WriteLine("{0}Results before calling any prototype:",System.Environment.NewLine);

foreach (DataRow dataRow in seq1)

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),

dataRow.Field<string>("Name"));

}

Results before calling any prototype:

Student Id = 1 is Joe Rattz

Student Id = 7 is Anthony Adams

Student Id = 13 is Stacy Sinclair

Student Id = 72 is Dignan Stephens

Results after calling prototype 1,2 & 3:

Student Id = 1 is Joe Rattz

Student Id = 7 is George Oscar Bluth

Student Id = 13 is Stacy Sinclair

Student Id = 72 is Dignan Stephens

Student Id = 7 is Michael Bluth

Student Id = 7 is Tony Wonder

DataTable Operators

- As the DataRowExtensions introduces the extension methods Field and SetField, the

- DataTableExtensions class introduces the extension methods AsDataView, AsEnumerable, and CopyToDataTable [To be discussed here].

AsEnumerable

- AsEnumerable operator specifically for the DataTable class that returns a sequence of DataRow objects.

- The purpose of this operator is to return a sequence of type IEnumerable<DataRow> from a DataTable object.

- Syntax & Sample

public static IEnumerable<DataRow> AsEnumerable ( this DataTable source)

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

DataTable dt1 = GetDataTable(students);

IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

CopyToDataTable<DataRow>

- AsEnumerable allows to get the sequence of DataRow objects from a DataTable.

- CopyToDataTable does the reverse operation. It allows to return the retrieved & modified DataRow objects back into a DataTable.

- Syntax 1

public static DataTable CopyToDataTable<T> (this IEnumerable<T> source) where T : DataRow;

DataTable dt1 = GetDataTable(students);

Console.WriteLine("Original DataTable:");

foreach (DataRow dataRow in dt1.AsEnumerable())

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"), dataRow.Field<string>("Name"));

}

(from s in dt1.AsEnumerable()

where s.Field<string>("Name") == "Anthony Adams"

select s).Single<DataRow>().SetField("Name", "George Oscar Bluth");

DataTable newTable = dt1.AsEnumerable().CopyToDataTable();

Console.WriteLine("{0}New DataTable:", System.Environment.NewLine);

foreach (DataRow dataRow in newTable.AsEnumerable())

{

Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"), dataRow.Field<string>("Name"));

}

Input

Output

Student[] students = {

new Student { Id = 1, Name = "Joe Rattz" },

new Student { Id = 7, Name = "Anthony Adams" },

new Student { Id = 13, Name = "Stacy Sinclair" },

new Student { Id = 72, Name = "Dignan Stephens" }

};

Original DataTable:

Student Id = 1 is Joe Rattz

Student Id = 7 is Anthony Adams

Student Id = 13 is Stacy Sinclair

Student Id = 72 is Dignan Stephens

New DataTable:

Student Id = 1 is Joe Rattz

Student Id = 7 is George Oscar Bluth

Student Id = 13 is Stacy Sinclair

Student Id = 72 is Dignan Stephens

- Syntax 2

public static void CopyToDataTable<T> (this IEnumerable<T> source, DataTable table, LoadOption options ) where T : DataRow;

The possible LoadOptions are as –

- PreserveChanges: Only Original Column values should be changed

- Upsert: Only Current Column values should be changed

- OverwriteChanges: Both

- To make the CopyToDataTable operator work properly, the destination DataTable object must have the Primary Key Fields. Without specifying primary keys, this prototype will result in appending all the records from the source DataTable to the destination DataTable.

DataTable dt1 = GetDataTable(students);

DataTable newTable = dt1.AsEnumerable().CopyToDataTable();

(from s in dt1.AsEnumerable()

where s.Field<string>("Name") == "Anthony Adams"

select s).Single<DataRow>().SetField("Name", "George Oscar Bluth");

dt1.AsEnumerable().CopyToDataTable(newTable, LoadOption.Upsert);

foreach (DataRow dataRow in newTable.AsEnumerable())

{

Console.WriteLine("Student Id = {0} : original {1} : current {2}",

dataRow.Field<int>("Id"),

dataRow.HasVersion(DataRowVersion.Original) ?

dataRow.Field<string>("Name", DataRowVersion.Original) : "-does not exist-",

dataRow.Field<string>("Name", DataRowVersion.Current));

}

Before upserting DataTable:

Student Id = 1 : original Joe Rattz : current Joe Rattz

Student Id = 7 : original Anthony Adams : current Anthony Adams

Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair

Student Id = 72 : original Dignan Stephens : current Dignan Stephens

After upserting DataTable:

Student Id = 1 : original Joe Rattz : current Joe Rattz

Student Id = 7 : original Anthony Adams : current Anthony Adams

Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair

Student Id = 72 : original Dignan Stephens : current Dignan Stephens

Student Id = 1 : original -does not exist- : current Joe Rattz

Student Id = 7 : original -does not exist- : current George Oscar Bluth

Student Id = 13 : original -does not exist- : current Stacy Sinclair

Student Id = 72 : original -does not exist- : current Dignan Stephens

Note – As there was no Primary key the changed rows have been appended to the original table.

DataTable dt1 = GetDataTable(students);

DataTable newTable = dt1.AsEnumerable().CopyToDataTable();

newTable.PrimaryKey = new DataColumn[] { newTable.Columns[0] };

Rest is same as above

Before upserting DataTable:

Student Id = 1 : original Joe Rattz : current Joe Rattz

Student Id = 7 : original Anthony Adams : current Anthony Adams

Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair

Student Id = 72 : original Dignan Stephens : current Dignan Stephens

After upserting DataTable:

Student Id = 1 : original Joe Rattz : current Joe Rattz

Student Id = 7 : original Anthony Adams : current Anthony Adams

Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair

Student Id = 72 : original Dignan Stephens : current Dignan Stephens

Hope this helps.

Thanks & Regards,

Arun Manglick || Senior Tech Lead

Arun Manglick - Technical View

Labels

Tuesday, April 28, 2009

LINQ to DataSet

No comments:

Post a Comment