Can I do this easily with LINQ??

[John Dann]

Currently using VB2005 Pro hence I haven't really got to grips with
LINQ yet. An upgrade to V2008 is planned, but for a part-time private
coder it's not cheap and so it needs to await a good justification!
I'm wondering if this question might provide the trigger, but want to
check my facts first.

I've got a largish datatable - several thousand rows and 60-100
columns. The primary index column is a datetime type.

I want to query this datatable and retrieve rows for a segment of
dates (ie >datetime1 AND <datetime2) and return only a small and
specified subset of columns. Presumably the returned block of cells is
or could readily be in the form of another - obviously much smaller -
datatable?

So, does LINQ make this easy in VB2008 or not? By 'easy' I'm guessing
that once a suitable query statement had been assembled then it might
just be one or two lines of further code to actually execute the
query?

I'm currently doing this in VB2005 but it's rather clumsy (unless I've
missed a trick) and I'd feel more comfortable with a neater and lower
maintenance solution.

JGD

 

[John Dann]

Anyone, please? Or are not many people using LINQ?

JGD

 

[Teemu]

I've got a largish datatable - several thousand rows and 60-100
columns. The primary index column is a datetime type.

I want to query this datatable and retrieve rows for a segment of
dates (ie >datetime1 AND <datetime2) and return only a small and
specified subset of columns. Presumably the returned block of cells is
or could readily be in the form of another - obviously much smaller -
datatable?

If it's a normal database why don't you use a simple SQL query to get the
information you want?

For example:

SELECT column1, column2 FROM Table WHERE column3 > '2008-11-14';


-Teemu

 

[John Dann]

If it's a normal database why don't you use a simple SQL query to get the
information you want?

Thanks, but it's just a bare datatable - there's no database involved.
(and not even a dataset, though it would obviously be easy enough to
wrap the datatable in a dataset).

JGD

 

[Teemu]

Thanks, but it's just a bare datatable - there's no database involved.
(and not even a dataset, though it would obviously be easy enough to
wrap the datatable in a dataset).

Well, it's not game over yet.

There is Select method in DataTable class.

Check out this:
http://msdn.microsoft.com/en-us/library/det4aw50.aspx

-Teemu

 

[Teemu]

Thanks, but it's just a bare datatable - there's no database involved.
(and not even a dataset, though it would obviously be easy enough to
wrap the datatable in a dataset).

And of course you can modify the view of your datatable like this:

Dim table As New DataTable
table.DefaultView.RowFilter = "Column1 < 0"

-Teemu

 

[John Dann]

Well, it's not game over yet.

There is Select method in DataTable class.

Check out this:
http://msdn.microsoft.com/en-us/library/det4aw50.aspx

Thanks again for the reply.

Actually I am already using this Select method on my datatable in my
existing code. The problem is, as I understand it, that this Select
method is less flexible/powerful than the standard SQL implementation.

Remember that what I want to do is to perform two selection actions
simultaneously, ie:

1. Retrieve a set of rows that correspond to a specified range of
datetime values:

2. Retrieve only a specified subset of columns.

I've been told previously that I can't do this with the datatable
Select command - it's fine for relatively simple queries but not for
more complex/compound ones. Are you suggesting that this may not be
correct advice?

I'm currently using the datatable.select to perform stage 1 above, but
AIUI it won't do stage 2 as well, hence my interest in LINQ as a
possible solution.

JGD

 

[John Dann]

I've been told previously that I can't do this with the datatable
Select command - it's fine for relatively simple queries but not for
more complex/compound ones.

Just to put this another way: that the Select command is OK for use as
a row filter but not as a column filter.

JGD

 

[Teemu]

And of course you can modify the view of your datatable like this:

Dim table As New DataTable
table.DefaultView.RowFilter = "Column1 < 0"

And this is the way to get only some columns:

Dim table As New DataTable
Dim columns() As String = {"Column3", "Column4"}

table.DefaultView.RowFilter = "Column1 < 0"
table.DefaultView.ToTable(False, columns)

-Teemu

 

[Teemu]

I've been told previously that I can't do this with the datatable
Select command - it's fine for relatively simple queries but not for
more complex/compound ones. Are you suggesting that this may not be
correct advice?

Check out the other message that I posted about DataView. I think it will be
the solution you are looking for.

It is true that DataTable's Select method is not so powerful as in SQL but
using also DataView some nice tricks can be done.

-Teemu

 

[Teemu]

And this is the way to get only some columns:

Dim table As New DataTable
Dim columns() As String = {"Column3", "Column4"}

table.DefaultView.RowFilter = "Column1 < 0"
table.DefaultView.ToTable(False, columns)

A small correction:

Dim table As New DataTable
Dim columns() As String = {"Column3", "Column4"}

table.DefaultView.RowFilter = "Column1 < 0"
Dim newTable As DataTable = table.DefaultView.ToTable(False,
columns)

-Teemu

 

[Cor Ligthert[MVP]]

Hi John,

First of all, VB2008 Express cost nothing therefore I don't see why you
would not try that for your problem.

Second, as you want to use Linq then probably any other enumerable
collection is bettter than the datatable as that is used only in memory.

Thirth, you don't need Linq for your problem, the "dataview overload" that
started with 2005 and a rowfilter are in my idea perfect for your described
problem

http://msdn.microsoft.com/en-us/library/8sd1cd0a.aspx

Succes

Cor

 

[Tom Shelton]

Currently using VB2005 Pro hence I haven't really got to grips with
LINQ yet. An upgrade to V2008 is planned, but for a part-time private
coder it's not cheap and so it needs to await a good justification!
I'm wondering if this question might provide the trigger, but want to
check my facts first.

VS2008 Express is free.

I've got a largish datatable - several thousand rows and 60-100
columns. The primary index column is a datetime type.

I want to query this datatable and retrieve rows for a segment of
dates (ie >datetime1 AND <datetime2) and return only a small and
specified subset of columns. Presumably the returned block of cells is
or could readily be in the form of another - obviously much smaller -
datatable?

So, does LINQ make this easy in VB2008 or not? By 'easy' I'm guessing
that once a suitable query statement had been assembled then it might
just be one or two lines of further code to actually execute the
query?

I'm currently doing this in VB2005 but it's rather clumsy (unless I've
missed a trick) and I'd feel more comfortable with a neater and lower
maintenance solution.

JGD

You can use Linq using the System.Data.DataTableExtensions extension
methods... Particularly, the AsEnumerable() extension method. Here is a
short example, that queries a DataTable and only returns the FirtName and
LastName where the BirthDate is in Aug. 2008 (watch for wrapping):

Option Explicit On
Option Strict On

Imports System
Imports System.Data

Module Module1

Sub Main()
Dim current As New Date(2008, 7, 1)
Dim endDate As New Date(2008, 9, 30)
Dim rnd As New Random()
Dim firstNames() As String = {"Tom", "Bill", "Raymond", "Edward", "Steve", "Greg", "Dick", "Charles", "Fred", "Gary"}
Dim lastNames() As String = {"Wibert", "Vieira", "Shelton", "Allen", "Williams", "Smith", "Jones", "Crawford", "Fey", "Meyers"}

' Build the data table
Dim dt As New DataTable("Test Table")
dt.Columns.Add(New DataColumn("FirstName", GetType(String)))
dt.Columns.Add(New DataColumn("LastName", GetType(String)))
dt.Columns.Add(New DataColumn("BirthDate", GetType(Date)))

' Populate the data table
While (Date.Compare(current, endDate) < 1)
Dim values(2) As Object

values(0) = firstNames(rnd.Next(0, 10))
values(1) = lastNames(rnd.Next(0, 10))
values(2) = current
dt.Rows.Add(values)
current = current.AddDays(1)
End While

' Create our query
Dim q = From r In dt.AsEnumerable() _
Where _
Date.Compare(r.Field(Of Date)("BirthDate"), New Date(2008, 8, 1)) >= 0 _
And Date.Compare(r.Field(Of Date)("BirthDate"), New Date(2008, 8, 31)) <= 0 _
Select New With { .FirstName = r.Field(Of String)("FirstName"), .LastName = r.Field(Of String)("LastName")}

' Write out the results
For Each result In q
Console.WriteLine("{0}, {1}", result.LastName, result.FirstName)
Next
End Sub

End Module

HTH

 

[Cor Ligthert[MVP]]

Tom,,

In my idea very nice code,
It has also the in my idea the most correct way to create a DateTime value.

Cor

 

[Teemu]

Dim table As New DataTable
Dim columns() As String = {"Column3", "Column4"}

table.DefaultView.RowFilter = "Column1 < 0"
Dim newTable As DataTable = table.DefaultView.ToTable(False,
columns)

Did you try this? It would be nice to know if it worked or not.

-Teemu