Since it was introduced in VB.NET 2008, LINQ has become fundamental in VB.NET - but it's still widely misunderstood. This article is one of a series at About Visual Basic that covers the major ways to program LINQ. The goal is for a beginning Visual Basic .NET programmer to get up to speed on LINQ. If you found this article from a search and you would like to start at the beginning, you can find an introduction to the overall LINQ technology - what it covers and what you can do with it - in the article LINQ - An Example Driven Introduction with an example showing how LINQ could be used to start web pages using selections made with CheckBox controls. There is an index to all of the articles in the series in the beginning of the article.
LINQ is really just a top-level name for a whole bunch of objects that generally help you access data as part of your program source code. The biggest advantage is that you get Visual Studio support in programming, such as compiler syntax checking and IntelliSense, for easier coding with fewer bugs. You can use LINQ if your data is in a almost any database, but not necessarily the same flavor of LINQ. If your data is in a SQL Server database, you might be better off using LINQ to SQL (or the Entity Framework, depending on how complex your requirements are) since it's generally easier and faster to work with. But if your data is some other database, or if you need to do something that LINQ to SQL doesn't support like dynamically selecting a database at runtime, then LINQ to DataSet could be the right choice.
LINQ to DataSet is built on ADO.NET and gives you the advantages that technology provides. Some of those include:
-> Set operations on sequences of DataRow Objects
-> Get and set DataColumn values
-> Queries on IEnumerable(Of T) sequences from a DataTable
-> Copy updated DataRow objects back to a DataTable
The list above demonstrates that, although it's called "LINQ to DataSet", most of the functionality is really in DataTable, DataRow, and DataColumn objects. But to get started using LINQ to DataSet, we probably should start with DataSet. If you need a quick refresher on DataSet, you can find it here: A Fast Introduction to DataSet in VB.NET. I'm going to code the examples using the same SQL Server database I used in the LINQ to SQL article.
As noted in the list above, LINQ to DataSet is really a collection of new objects that can be used with DataSet objects. So learning about LINQ to DataSet is really learning all of these new objects - which can be really deadly boring and besides, nobody I know can remember all the details anyway. You will normally look them up to get the details. So in this article, just one will be explained in detail. The theory is that if you get the idea clearly in mind about one, you can get whatever you need from Microsoft's documentation about the others.
SequenceEqual
The method I have selected is SequenceEqual. Since it's in the System.Linq namespace, it's a LINQ method. The fully qualified name is:
System.Linq.Enumerable.SequenceEqual(Of TSource)
The Microsoft documentation states that this method, "Determines whether two sequences are equal according to an equality comparer." So to test this one out, we need two sequences to test. Here's the code to get these sequences.
Dim connectionString As String =
"Data Source=MUKUNTUWEAP;" &
"Initial Catalog=Booze;" &
"Integrated Security=True"
Dim cn As New SqlConnection(connectionString)
Dim myDataSet1 As DataSet = New DataSet
Dim myDataSet2 As DataSet = New DataSet
Dim dataAdapter As SqlDataAdapter =
New SqlDataAdapter
Dim selsq As SqlCommand =
New SqlCommand("SELECT * FROM RECIPES", cn)
dataAdapter.SelectCommand = selsq
dataAdapter.Fill(myDataSet1, "Recipes")
dataAdapter.Fill(myDataSet2, "Recipes")
Dim seq1 As IEnumerable(Of DataRow) = myDataSet1.Tables("Recipes").AsEnumerable
Dim seq2 As IEnumerable(Of DataRow) = myDataSet2.Tables("Recipes").AsEnumerable
The code creates two IEnumerable sequences that should be exactly the same. From the top down to the last two statements, it's just standard database code. Since the method we're looking at needs an IEnumerable sequence, the last two statements create them using the AsEnumerable method. The Microsoft documentation specifically states, "This object can be used in a LINQ expression."
To test these sequences, we simply call the method and display the result:
areEqual = seq1.SequenceEqual(seq2)
Console.WriteLine(areEqual)
The result is ... False!
How can this be? They're exactly the same!
Not quite, actually. They have identical content, but they're different sequences. That is, they're different references. (I discuss references in an article that explains the error, Object reference not set to an instance of an object. This particular one has quite a few comments from readers that might be more educational than the article.) The Microsoft documentation explains it in detail, including a custom implementation of an IEqualityComparer(Of T) generic interface. That might be a bit much, so the easy way to do it is use System.Data.DataRowComparer.Default, precoded for you by Microsoft:
areEqual =
seq1.SequenceEqual(
seq2, System.Data.DataRowComparer.Default
)
This gives us the "True" result we are looking for.
Here are other methods for just sequences of DataRows added by LINQ to DataSet:
-> Distinct
-> Except
-> Intersect
-> Union
And this doesn't include all of the other LINQ to DataSet categories. The bottom line is, if you're working with DataSets, check and see what LINQ to DataSet has to offer for more productivity.
