Linq to Datarow, Select multiple columns as distinct?

Question

basically i'm trying to reproduce the following mssql query as 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

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?

Answer 1

Check out the linq to sql samples:

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

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

HTH

Answer 2

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

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 might need to specify the names of the properties; I don't think the compiler will infer them.

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.

Common cause of confusion:

One key is that Linq-to-SQL and (the Linq-to-object activity commonly called) LINQ-to-Dataset are two very different things. In both you'll see LINQ being used, so it often causes confusion.

LINQ-to-Dataset

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:

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

Linq-to-dataset is choosing to (A) NOT use 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-SQL

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

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.

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.

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

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.

Answer 3

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

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