ADO.NET 3.5 Cookbook, 2nd Edition

3.27. Querying a DataSet Using LINQ

Problem

You need to query a DataSet using a LINQ query.

Solution

Use LINQ to DataSet as shown in this solution.

The solution fills two DataTable objects in a DataSet with all records from the Production.Product and Production.ProductInventory tables in the AdoDotNet35Cookbook database. A relationship is created between the tables on the ProductID column. A LINQ query that retrieves joined data from both columns in the DataSet is created and executed and the results are output to the console.

The C# code in Program.cs in the project LinqToDataSetQuery is shown in Example 3-39.

Example 3-39. File: Program.cs for LinqToDataSetQuery solution

using System; using System.Data; using System.Data.SqlClient; using System.Linq; namespace LinqToDataSetQuery { class Program { static void Main(string[] args) { string connectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Production.Product; " + "SELECT * FROM Production.ProductInventory;"; // Create the data adapter to retrieve data from the database SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString); // Create table mappings da.TableMappings.Add("Table", "Product"); da.TableMappings.Add("Table1", "ProductInventory"); // Create and fill the DataSet DataSet ds = new DataSet( ); da.Fill(ds); // Create the relationship between the Product and // ProductInventory tables DataRelation dr = ds.Relations.Add("Product_ProductInventory", ...