Linq查询到DataTable [英] Linq Query to DataTable

问题描述

大家好，



非常遗憾地问这个，一旦你知道，我相信它很简单。

我想加入到数据表使用linq并返回两个表中的列，然后将结果插入到另一个数据表中。

问题是我无法使用query.CopyToDataTable（）方法，因为查询中存在匿名类型（或东西）。请看下面的代码。



我一直在网上看绝对年龄！我已经看到了MSDN上给出的ObjectShredder类建议但是不能很好地解决它，我已经尝试了各种各样的其他潜在解决方案，但无济于事。



有人可以指出我出错的地方，或者可能是如何让ObjectShredder类解决这个问题的快速指针？

现阶段的任何帮助都非常受欢迎！

提前致谢



Chris



原始代码：

Hi all,

Really sorry to ask this as im sure its fairly simple once you know.
Im trying to join to datatables using linq and return columns from both tables and then insert the result into another datatable.
The problem is that I cant use the query.CopyToDataTable() method as there are anonymous types in the query (or something). Please see the code below.

I have been looking online now for absolutely ages! I have seen the ObjectShredder class advice given on MSDN but cant quite work it out, and i have tried a huge variety of other potential solutions, alas to no avail.

Could someone please either point out where im going wrong or may be a quick pointer on how to get the ObjectShredder Class to resolve this?
Any help at this stage is more than welcome!
Thanks in advance

Chris

The original bit of code:

Dim query = From a In dt _    Join b In dtLossEvents _    On _    a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _    Select New With { _                    a, _                    b _                    }

这是最新的有缺陷的化身：

This it the latest flawed incarnation:

Dim query = From a In dt _            Join b In dtLossEvents _            On _            a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _            Select New With { _                             .Gross_Loss = a.Field(Of Double ("Gross_Loss"), _                             .Net_Loss = a.Field(Of Double)("Net_Loss"), _                             .EventID = b.Field(Of Integer)("EventID") _}

推荐答案

我使用的是C＃，而不是VB.N等，请原谅我回答使用C＃中的样本，但我希望我能提供一些帮助。



上面的第二个例子似乎是90％的一种工作解决方案的方式，有两件事（可能只有一件，因为我不知道VB.Net为Linq向DataTables提供了什么便利），这些都无法阻止你从Linq获得DataTable。



我创建了一个测试应用程序并尝试创建一个Linq查询，就像你在上面建模的那样。我发现我必须在tableName.AsEnumerable（）中使用xxx中的 ... （其中调用AsEnumerable（）是关键的洞察力）让Linq接受来自子句的中的DataTable名称。如果您的Linq查询无法编译，这可能是您解决问题的那部分问题。



我尝试的第二件事是让Linq返回一个DataTable - 因为，正如你所说，Linq本身会返回一个匿名类型的IEnumerable。这就是ObjectShredder的用武之地。我逐字复制了ObjectShredder示例代码，并且它有效。要使用ObjectShredder，可以将Linq查询返回的IEnumerable转换为具有以下内容的DataTable： DataTable result = queryResult.CopyToDataTable（）; 。



这是我的测试方法：



I use C#, not VB.Net, so please forgive me for answering using samples in C#, but I hope I can provide some help nevertheless.

Your second example above seems to be 90% of the way to a working solution, with two things (possibly only one, because I don't know what conveniences VB.Net provides for Linq to DataTables) missing that are preventing you from getting a DataTable from Linq.

I created a test app and attempted to create a Linq query like the ones you model above. I discovered that I had to use from xxx in tableName.AsEnumerable() ... (where the call to AsEnumerable() was the crucial insight) to get Linq to accept a DataTable name in the from clause. If your Linq query would not compile, this might be your solution to that part of the problem.

The second thing I experimented with was getting Linq to return a DataTable - because, as you say, Linq by itself returns an IEnumerable of an anonymous type. This is where the ObjectShredder comes in. I copied the ObjectShredder example code verbatim, and it worked. To use the ObjectShredder, you convert the IEnumerable returned by your Linq query into a DataTable with the following: DataTable result = queryResult.CopyToDataTable();.

Here's my test method:

private static DataTable JoinDataTablesWithLinq()
    {
    var query = from inv in _tblInvoice.AsEnumerable()
                join item in _tblLineItem.AsEnumerable()
                    on inv["InvoiceId"] equals item["InvoiceId"]
                select new
                        {
                            CustomerName = inv["CustomerName"],
                            ItemName = item["ItemName"],
                            Quantity = item["Quantity"]
                        };

    return query.CopyToDataTable();
    }

这里有一些有用的链接：

ObjectShredder（你可能已经拥有） ： http://msdn.microsoft.com/en-us/library/bb669096.aspx [ ^ ]

使用Linq和DataTables： http：// dotnetarchitect。 wordpress.com/2009/03/18/using-linq-to-manipulate-data-in-datasetdatatable/ [ ^ ]

你好



非常感谢您的帮助。抱歉，它花了几天时间来测试。



不幸的是我仍然收到错误CopyToDataTable不是System.Collections.Generic.IEnumerable（匿名）的成员类型）。它非常令人沮丧，因为我觉得你让我在那里正确的路线。以下是我的代码，现在欢迎任何帮助！



预先全部谢谢



Chris

Hi there

Thanks so much for your help. Sorry its taken a few days to test.

Unfortunately I'm still getting the error "CopyToDataTable is not a member of System.Collections.Generic.IEnumerable(Of anonymous type)". Its very frustrating as i felt you had put me on the right course there. Below is my code, any help would be most welcome now!

Thanks all in advance

Chris

Dim items = From a In dt.AsEnumerable() _
    Join b In dtLossEvents.AsEnumerable() On _
    a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _
    Select New With { _
                     .Gross_Loss = dt("Gross_Loss"), _
                     .Net_Loss = dt("Net_Loss"), _
                     .EventID = dtLossEvents("EventID") _
                    }



Dim result As DataTable = items.CopyToDataTable()

这篇关于Linq查询到DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！