vb.net Linq to Datarow，选择多列作为不同的列？

Linq to Datarow, Select multiple columns as distinct?

提问by Beta033

basically i'm trying to reproduce the following mssql query as LINQ

基本上我正在尝试将以下 mssql 查询重现为 LINQ

SELECT DISTINCT [TABLENAME], [COLUMNNAME] FROM [DATATABLE]

the closest i've got is

我最接近的是

Dim query = (From row As DataRow In ds.Tables("DATATABLE").Rows _
                  Select row("COLUMNNAME") ,row("TABLENAME").Distinct

when i do the above i get the error

当我执行上述操作时出现错误

Range variable name can be inferred only from a simple or qualified name with no arguments.

只能从不带参数的简单名称或限定名称推断范围变量名称。

i was sort of expecting it to return a collection that i could then iterate through and perform actions for each entry. maybe a datarow collection?

我有点期望它返回一个集合，然后我可以遍历该集合并为每个条目执行操作。也许是数据行集合？

As a complete LINQ newb, i'm not sure what i'm missing. i've tried variations on

作为一个完整的 LINQ 新手，我不确定我错过了什么。我试过变体

Select new with { row("COLUMNNAME") ,row("TABLENAME")}

and get:

并得到：

Anonymous type member name can be inferred only from a simple or qualified name with no arguments.

匿名类型成员名称只能从不带参数的简单名称或限定名称推断出来。

to get around this i've tried

为了解决这个问题，我试过了

 Dim query = From r In ds.Tables("DATATABLE").AsEnumerable _
        Select New String(1) {r("TABLENAME"), r("COLUMNNAME")} Distinct

however it doesn't seem to be doing the distinct thing properly.

但是它似乎没有正确地做不同的事情。

Also, does anyone know of any good books/resources to get fluent?

另外，有没有人知道任何可以流利的好书/资源？

采纳答案by Beta033

I think i've figured it out. Thanks for your help.

我想我已经想通了。谢谢你的帮助。

Maybe there's an easier way though?

也许有更简单的方法？

What i've done is

我所做的是

Dim comp As StringArrayComparer = New StringArrayComparer
Dim query = (From r In ds.Tables("DATATABLE").AsEnumerable _
        Select New String(1) {r("TABLENAME"), r("COLUMNNAME")}).Distinct(comp)

this returns a new string array (2 elements) running a custom comparer

这将返回一个运行自定义比较器的新字符串数组（2 个元素）

Public Class StringArrayComparer
    Implements IEqualityComparer(Of String())

    Public Shadows Function Equals(ByVal x() As String, ByVal y() As String) As Boolean Implements System.Collections.Generic.IEqualityComparer(Of String()).Equals

        Dim retVal As Boolean = True

        For i As Integer = 0 To x.Length - 1
            If x(i) = y(i) And retVal Then
                retVal = True

            Else
                retVal = False
            End If

        Next

        Return retVal

    End Function

    Public Shadows Function GetHashCode(ByVal obj() As String) As Integer Implements System.Collections.Generic.IEqualityComparer(Of String()).GetHashCode

    End Function
End Class

回答by Patrick Karcher

You start using LINQ on your datatable objects, you run the query against dt.AsEnumberable, which returns an IEnumerable collection of DataRow objects.

您开始在数据表对象上使用 LINQ，然后针对 dt.AsEnumberable 运行查询，该查询返回 DataRow 对象的 IEnumerable 集合。

Dim query = From row As DataRow In ds.Tables("DATATABLE").AsEnumerable _
              Select row("COLUMNNAME") ,row("TABLENAME")

You might want to say row("COLUMNNAME").ToString(), etc. Query will end up being an IEnumerable of an anonymous type with 2 string properties; is that what you're after? You mightneed to specify the names of the properties; I don't think the compiler will infer them.

您可能想说row("COLUMNNAME").ToString()，等等。Query 最终将成为具有 2 个字符串属性的匿名类型的 IEnumerable；这就是你所追求的吗？您可能需要指定属性的名称；我认为编译器不会推断它们。

Dim query = From row As DataRow In ds.Tables("DATATABLE").AsEnumerable _
              Select .ColumnName = row("COLUMNNAME"), .TableName = row("TABLENAME")

This assumes that in your original sql query, for which you used ADO to get this dataset, you made sure your results were distinct.

这假设在您使用 ADO 获取此数据集的原始 sql 查询中，您确保结果是不同的。

Common cause of confusion:

常见的混淆原因：

One key is that Linq-to-SQLand (the Linq-to-objectactivity commonly called) LINQ-to-Datasetare two very different things. In both you'll see LINQ being used, so it often causes confusion.

一个关键是Linq-to-SQL和（通常称为Linq-to-object活动）LINQ-to-Dataset是两个非常不同的东西。在两者中你都会看到 LINQ 被使用，所以它经常引起混淆。

LINQ-to-Dataset

LINQ 到数据集

is:

是：

1 getting your datatable the same old way you always have, with data adapters and connections etc., ending up with the traditional datatable object. And then instead of iterating through the rows as you did before, you're:

1 以与以往相同的旧方式获取数据表，使用数据适配器和连接等，最终得到传统的数据表对象。然后不是像以前那样遍历行，而是：

2 running linq queries against dt.AsEnumerable, which is an IEnumerable of datarow objects.

2 对 运行 linq 查询dt.AsEnumerable，它是数据行对象的 IEnumerable。

Linq-to-dataset is choosing to (A) NOTuse Linq-to-SQL but instead use traditional ADO.NET, but then (B) once you have your datatable, using LINQ(-to-object) to retrieve/arrange/filter the data in your datatables, rather than how we've been doing it for 6 years. I do this a lot. I love my regular ado sql (with the tools I've developed), but LINQ is great

Linq-to-dataset 选择（A）不使用 Linq-to-SQL 而是使用传统的 ADO.NET，但是（B）一旦你有了数据表，使用 LINQ(-to-object) 来检索/排列/过滤数据表中的数据，而不是我们 6 年来的做法。我经常这样做。我喜欢我的常规 ado sql（使用我开发的工具），但LINQ 很棒

LINQ-to-SQL

LINQ 到 SQL

is a different beast, with vastly different things happening under the hood. In LINQ-To-SQL, you:

是一个不同的野兽，在引擎盖下发生了截然不同的事情。在 LINQ-To-SQL 中，您：

1 define a schema that matches your db, using the tools in in Visual Studio, which gives you simple entity objects matching your schema.
2 You write linq queries using the db Context, and get these entities returned as results.

1 使用 Visual Studio 中的工具定义一个与您的数据库匹配的架构，它为您提供与您的架构匹配的简单实体对象。
2 您使用 db Context编写 linq 查询，并将这些实体作为结果返回。

Under the hood, at runtime .NET translates these LINQ queries to SQL and sends them to the DB, and then translates the data return to your entity objects that you defined in your schema.

在后台，.NET 在运行时将这些 LINQ 查询转换为 SQL 并将它们发送到数据库，然后将返回的数据转换为您在架构中定义的实体对象。

Other resources:

其他资源：

Well, that's quite a truncated summary. To further understand these two very separate things, check out:
LINQ-to-SQL
LINQ-to-Dataset

嗯，这是一个相当截断的摘要。要进一步了解这两个非常独立的东西，请查看：
LINQ-to-SQL
LINQ-to-Dataset

A fantastic book on LINQ is LINQ in Action, my Fabrice Marguerie, Steve Eichert and Jim Wooley (Manning). Go get it! Just what you're after. Very good. LINQ is not a flash in the pan, and worth getting a book about. In .NET there's way to much to learn, but time spent mastering LINQ is time well spent.

一本关于 LINQ 的绝妙书是LINQ in Action，我的 Fabrice Marguerie、Steve Eichert 和 Jim Wooley（Manning）。去实现它（梦想）;去得到它（东西！正是你所追求的。非常好。LINQ 不是昙花一现，值得一读。在 .NET 中有很多东西需要学习，但是花在掌握 LINQ 上的时间是值得的。

回答by bmadtiger

I had the same question and from various bits I'm learning about LINQ and IEnumerables, the following worked for me:

我有同样的问题，从我学习 LINQ 和 IEnumerables 的各个方面来看，以下对我有用：

Dim query = (From row As DataRow In ds.Tables("DATATABLE").Rows _
              Select row!COLUMNNAME, row!TABLENAME).Distinct

Strangely using the old VB bang (!) syntax got rid of the "Range variable name..."error BUT the key difference isusing the .Distinctmethod on the query result (IEnumerable) object rather than trying to use the Distinctkeyword within the query.

奇怪的是，使用旧的 VB bang (!) 语法摆脱了“Range variable name...”错误，但关键区别在于使用.Distinct查询结果 (IEnumerable) 对象上的方法，而不是尝试Distinct在查询中使用关键字。

This LINQ query then returns an IEnumerable collection of anonymous typewith properties matching the selected columns from the DataRow, so the following code is then accessible:

此 LINQ 查询然后返回匿名类型的 IEnumerable 集合，其属性与 DataRow 中的选定列匹配，因此可以访问以下代码：

For Each result In query
   Msgbox(result.TABLENAME & "." & result.COLUMNNAME)
Next

Hoping this helps somebody else stumbling across this question...

希望这可以帮助其他人绊倒这个问题......

回答by Raja

Check out the linq to sql samples:

查看 linq to sql 示例：

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

Pretty useful to learn SQL. And if you want to practice then use LinqPad

学习SQL非常有用。如果您想练习，请使用LinqPad

HTH

HTH