Can I do this easily with LINQ??

  • Thread starter Thread starter John Dann
  • Start date Start date
J

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
 
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?
Click to expand...

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
 
If it's a normal database why don't you use a simple SQL query to get the
information you want?
Click to expand...

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
 
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).
Click to expand...

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

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

-Teemu
 
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
Click to expand...

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
 
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.
Click to expand...

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 said:
And of course you can modify the view of your datatable like this:

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

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
 
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?
Click to expand...

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 said:
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)
Click to expand...

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
 
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
 
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.
Click to expand...

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
Click to expand...

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
 
Tom,,

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

Cor
 
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)
Click to expand...

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

-Teemu
 
Back
Top