基于查询创建数据表格（LINQ to DataSet）

2025-06-19

数据绑定是 DataTable 对象的常见用法。该方法 CopyToDataTable 获取查询的结果并将数据复制到其中 DataTable，然后可用于数据绑定。在执行数据操作后，新的 DataTable 将合并回源 DataTable。

CopyToDataTable 方法使用下面的过程来通过查询创建 DataTable：

CopyToDataTable 方法克隆源表中的 DataTable（实现 DataTable 接口的 IQueryable<T> 对象）。源 IEnumerable 通常源自 LINQ to DataSet 表达式或方法查询。
克隆 DataTable 的架构是从源表中第一个枚举 DataRow 对象的列生成的，克隆表的名称是源表的名称，其中追加了“query”一词。
对于源表中的每一行，该行的内容将复制到新 DataRow 对象中，然后插入克隆的表中。在复制操作过程中，RowState 和 RowError 属性被保留。如果源中的 ArgumentException 对象来自不同的表，则会引发 DataRow。
在输入可查询表中所有 DataTable 对象被复制后，克隆的 DataRow 将返回。如果源序列不包含任何 DataRow 对象，该方法将返回一个空 DataTable。

调用CopyToDataTable方法会导致执行绑定到源表的查询。

当CopyToDataTable方法在源表的某一行中遇到 null 引用或可空值类型时，它将值替换为 Value。这样，可以在返回的 DataTable 中正确处理 null 值。

注意：该方法 CopyToDataTable 接受输入查询，该查询可以返回来自多个 DataTable 或 DataSet 对象的行。该 CopyToDataTable 方法会将来源于 DataTable 和 DataSet 对象的数据复制到返回的 DataTable，但不会复制属性。需要显式设置返回 DataTable的属性，例如 Locale 和 TableName。

下面的示例查询 SalesOrderHeader 表中 2001 年 8 月 8 日以后的订单，并使用 CopyToDataTable 方法通过查询创建 DataTable。 DataTable然后绑定到 BindingSource，它作为 DataGridView 的代理。

// Bind the System.Windows.Forms.DataGridView object
// to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource;

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderHeader"];

// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
    select order;

// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();

// Bind the table to a System.Windows.Forms.BindingSource object,
// which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable;

' Bind the System.Windows.Forms.DataGridView object
' to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource

' 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 orders As DataTable = ds.Tables("SalesOrderHeader")

' Query the SalesOrderHeader table for orders placed
'  after August 8, 2001.
Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of DateTime)("OrderDate") > New DateTime(2001, 8, 1) _
    Select order

' Create a table from the query.
Dim boundTable As DataTable = query.CopyToDataTable()

' Bind the table to a System.Windows.Forms.BindingSource object,
' which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable

创建自定义 CopyToDataTable<T> 方法

现有CopyToDataTable方法仅操作于IEnumerable<T>源，其中泛型参数T的类型为DataRow。尽管这很有用，但它不允许从一系列标量类型、从返回匿名类型的查询或从执行表联接的查询创建表。有关如何实现从一系列标量或匿名类型加载表的两个自定义 CopyToDataTable 方法的示例，请参阅如何：在泛型类型 T 不是数据行的情况下实现 CopyToDataTable<T>。

本节中的示例使用以下自定义类型：

public class Item
{
    public int Id { get; set; }
    public double Price { get; set; }
    public string Genre { get; set; }
}

public class Book : Item
{
    public string Author { get; set; }
}

public class Movie : Item
{
    public string Director { get; set; }
}

Public Class Item
    Private _Id As Int32
    Private _Price As Double
    Private _Genre As String

    Public Property Id() As Int32
        Get
            Return Id
        End Get
        Set(ByVal value As Int32)
            _Id = value
        End Set
    End Property

    Public Property Price() As Double
        Get
            Return _Price
        End Get
        Set(ByVal value As Double)
            _Price = value
        End Set
    End Property

    Public Property Genre() As String
        Get
            Return _Genre
        End Get
        Set(ByVal value As String)
            _Genre = value
        End Set
    End Property

End Class
Public Class Book
    Inherits Item
    Private _Author As String
    Public Property Author() As String
        Get
            Return _Author
        End Get
        Set(ByVal value As String)
            _Author = value
        End Set
    End Property
End Class

Public Class Movie
    Inherits Item
    Private _Director As String
    Public Property Director() As String
        Get
            Return _Director
        End Get
        Set(ByVal value As String)
            _Director = value
        End Set
    End Property

End Class

示例：

本示例对表SalesOrderHeaderSalesOrderDetail执行联接，以获取8月份的联机订单，并根据查询结果创建表。

// Fill the DataSet.
var ds = new DataSet
{
    Locale = CultureInfo.InvariantCulture
};
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable details = ds.Tables["SalesOrderDetail"];

var query =
    from order in orders.AsEnumerable()
    join detail in details.AsEnumerable()
    on order.Field<int>("SalesOrderID") equals
        detail.Field<int>("SalesOrderID")
    where order.Field<bool>("OnlineOrderFlag")
        && order.Field<DateTime>("OrderDate").Month == 8
    select new
    {
        SalesOrderID =
            order.Field<int>("SalesOrderID"),
        SalesOrderDetailID =
            detail.Field<int>("SalesOrderDetailID"),
        OrderDate =
            order.Field<DateTime>("OrderDate"),
        ProductID =
            detail.Field<int>("ProductID")
    };

DataTable orderTable = query.CopyToDataTable();

' 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 orders As DataTable = ds.Tables("SalesOrderHeader")
Dim details As DataTable = ds.Tables("SalesOrderDetail")


Dim query = _
    From order In orders.AsEnumerable() _
    Join detail In details.AsEnumerable() _
    On order.Field(Of Integer)("SalesOrderID") Equals _
            detail.Field(Of Integer)("SalesOrderID") _
    Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
            order.Field(Of DateTime)("OrderDate").Month = 8 _
    Select New With _
    { _
        .SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
        .SalesOrderDetailID = detail.Field(Of Integer)("SalesOrderDetailID"), _
        .OrderDate = order.Field(Of DateTime)("OrderDate"), _
        .ProductID = detail.Field(Of Integer)("ProductID") _
    }

Dim table As DataTable = query.CopyToDataTable()

示例：

以下示例查询价格大于 $9.99 的项的集合，并从查询结果创建表。

// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
  new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
  new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
  new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};

// Query for items with price greater than 9.99.
IOrderedEnumerable<Item> query = from i in items
                                 where i.Price > 9.99
                                 orderby i.Price
                                 select i;

// Load the query results into new DataTable.
DataTable table = query.CopyToDataTable();

Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"

Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"

Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"

Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"

Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2

' Query for items with price greater than 9.99.
Dim query = From i In items _
            Where i.Price > 9.99 _
            Order By i.Price _
            Select New With {i.Price, i.Genre}

Dim table As DataTable
table = query.CopyToDataTable()

示例：

以下示例查询价格大于 9.99 的项目的集合，并投影结果。返回的匿名类型的序列将加载到现有表中。

// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
  new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
  new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
  new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};

// Create a table with a schema that matches that of the query results.
var table = new DataTable();
table.Columns.Add("Price", typeof(int));
table.Columns.Add("Genre", typeof(string));

var query = from i in items
            where i.Price > 9.99
            orderby i.Price
            select new { i.Price, i.Genre };

query.CopyToDataTable(table, LoadOption.PreserveChanges);

Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"

Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"

Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"

Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"

Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2

' Create a table with a schema that matches that of the query results.
Dim table As DataTable = New DataTable()
table.Columns.Add("Price", GetType(Integer))
table.Columns.Add("Genre", GetType(String))

' Query for items with price greater than 9.99.
Dim query = From i In items _
            Where i.Price > 9.99 _
            Order By i.Price _
            Select New With {i.Price, i.Genre}

query.CopyToDataTable(table, LoadOption.PreserveChanges)

示例：

以下示例查询价格大于 9.99 美元的项的集合，并投影结果。返回的匿名类型的序列将加载到现有表中。表架构会自动展开，因为 Book 和 Movies 类型是从 Item 类型派生的。

// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
  new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
  new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
  new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};

// Load into an existing DataTable, expand the schema and
// autogenerate a new Id.
var table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("ExtraColumn", typeof(string));

var query = from i in items
            where i.Price > 9.99
            orderby i.Price
            select new { i.Price, i.Genre };

query.CopyToDataTable(table, LoadOption.PreserveChanges);

Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"

Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"

Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"

Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"

Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2

' Load into an existing DataTable, expand the schema and
' autogenerate a new Id.
Dim table As DataTable = New DataTable()
Dim dc As DataColumn = table.Columns.Add("NewId", GetType(Integer))
dc.AutoIncrement = True
table.Columns.Add("ExtraColumn", GetType(String))

Dim query = From i In items _
            Where i.Price > 9.99 _
            Order By i.Price _
            Select New With {i.Price, i.Genre}

query.CopyToDataTable(table, LoadOption.PreserveChanges)

示例：

以下示例查询价格大于 $9.99 的项的集合，并返回一个序列，该序列 Double将加载到新表中。

// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
  new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
  new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
  new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};

// load sequence of scalars.
IEnumerable<double> query = from i in items
                            where i.Price > 9.99
                            orderby i.Price
                            select i.Price;

DataTable table = query.CopyToDataTable();

Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"

Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"

Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"

Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"

Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2

Dim query = From i In items _
            Where i.Price > 9.99 _
            Order By i.Price _
            Select i.Price

Dim table As DataTable
table = query.CopyToDataTable()

通过

基于查询创建数据表格（LINQ to DataSet）

创建自定义 CopyToDataTable<T> 方法

示例：

示例：

示例：

示例：

示例：

另请参阅

其他资源