Query Expression Syntax Examples: Projection (LINQ to DataSet)
The examples in this topic demonstrate how to use the Select and SelectMany methods to query a DataSet using the query expression syntax.
The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.
The examples in this topic use the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
The examples in this topic use the following using/Imports statements:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
Option Explicit On
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization
For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.
Select
Example
This example uses the Select method to return all the rows from the Product table and display the product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable<DataRow> query =
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("Name"));
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = From product In products.AsEnumerable() _
Select product
Console.WriteLine("Product Names:")
For Each p In query
Console.WriteLine(p.Field(Of String)("Name"))
Next
Example
This example uses Select to return a sequence of only product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable<string> query =
from product in products.AsEnumerable()
select product.Field<string>("Name");
Console.WriteLine("Product Names:");
foreach (string productName in query)
{
Console.WriteLine(productName);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = From product In products.AsEnumerable() _
Select product.Field(Of String)("Name")
Console.WriteLine("Product Names:")
For Each productName In query
Console.WriteLine(productName)
Next
SelectMany
Example
This example uses From …, … (the equivalent of the SelectMany method) to select all orders where TotalDue is less than 500.00.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from contact in contacts.AsEnumerable()
from order in orders.AsEnumerable()
where contact.Field<int>("ContactID") == order.Field<int>("ContactID")
&& order.Field<decimal>("TotalDue") < 500.00M
select new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
Total = order.Field<decimal>("TotalDue")
};
foreach (var smallOrder in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
smallOrder.OrderID, smallOrder.Total);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From contact In contacts.AsEnumerable() _
From order In orders.AsEnumerable() _
Where (contact.Field(Of Integer)("ContactID") = _
order.Field(Of Integer)("ContactID")) _
And (order.Field(Of Decimal)("TotalDue") < 500D) _
Select New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.TotalDue = order.Field(Of Decimal)("TotalDue") _
}
For Each smallOrder In query
Console.Write("ContactID: " & smallOrder.ContactID)
Console.Write(" Name: {0}, {1}", smallOrder.LastName, _
smallOrder.FirstName)
Console.Write(" OrderID: " & smallOrder.OrderID)
Console.WriteLine(" TotalDue: $" & smallOrder.TotalDue)
Next
Example
This example uses From …, … (the equivalent of the SelectMany method) to select all orders where the order was made on October 1, 2002 or later.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from contact in contacts.AsEnumerable()
from order in orders.AsEnumerable()
where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1)
select new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
OrderDate = order.Field<DateTime>("OrderDate")
};
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
order.ContactID, order.LastName, order.FirstName,
order.OrderID, order.OrderDate);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From contact In contacts.AsEnumerable() _
From order In orders.AsEnumerable() _
Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1) _
Select New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate") _
}
For Each order In query
Console.Write("Contact ID: " & order.ContactID)
Console.Write(" Name: " & order.LastName & ", " & order.FirstName)
Console.Write(" Order ID: " & order.OrderID)
Console.WriteLine(" Order date: {0:d} ", order.OrderDate)
Next
Example
This example uses a From …, … (the equivalent of the SelectMany method) to select all orders where the order total is greater than 10000.00 and uses From assignment to avoid requesting the total twice.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from contact in contacts.AsEnumerable()
from order in orders.AsEnumerable()
let total = order.Field<decimal>("TotalDue")
where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
total >= 10000.0M
select new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
OrderID = order.Field<int>("SalesOrderID"),
total
};
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}",
order.ContactID, order.LastName, order.OrderID, order.total);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From contact In contacts.AsEnumerable() _
From order In orders.AsEnumerable() _
Let total = order.Field(Of Decimal)("TotalDue") _
Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
total >= 10000D _
Select New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate"), _
total _
}
For Each order In query
Console.Write("Contact ID: " & order.ContactID)
Console.Write(" Last Name: " & order.LastName)
Console.Write(" Order ID: " & order.OrderID)
Console.WriteLine(" Total: $" & order.total)
Next