Need help with LINQ Query

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

If I perform a simple select query in my project I'm able to fill my
gridview with no problem. However, once I wonder over to an aggregated
query were I sum two columns I run into tons of problems.

I also could never get this to work with a dataset. Since I only had one
table I opted to use only a datatable. Is that problematic?

Can someone help me out please?

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim dt As New Data.DataTable
Dim dr As Data.DataRow
Dim LineIn As String
Dim XQ
Dim LIN
Dim ZA
Dim StreamReader As System.IO.StreamReader

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(Int64)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))


StreamReader = IO.File.OpenText("txt.txt")

While StreamReader.Peek <> -1
LineIn = StreamReader.ReadLine()

If Mid(LineIn, 1, 2) = "XQ" Then
XQ = Split(LineIn, "*")
ElseIf Mid(LineIn, 1, 3) = "LIN" Then
LIN = Split(LineIn, "*")
Else
ZA = Split(LineIn, "*")
dr = dt.NewRow()
dr("Date") = Mid(XQ(2), 5, 2) & "/" & Mid(XQ(2), 7, 2) & "/"
& Mid(XQ(2), 1, 4)
dr("UPC") = LIN(3)
dr("QTY_Sold") = "0"
dr("QTY_OnHand") = ZA(2)
dr("Unit_Type") = ZA(3)
dt.Rows.Add(dr)
End If
End While

StreamReader.Close()

'SELECT DATE, UPC, SUM(QTY_Sold), SUM(QTY_OnHand), Unit_Type FROM dt
Dim results As IEnumerable(Of DataRow) = Aggregate o In dt _
Group By UPC_Group =
o.Field(Of Int64)("UPC") _
Into test = Sum(o.Field(Of
Integer)("QTY_Sold")), test2 = Sum(o.Field(Of Integer)("QTY_OnHand"))

DataGridView1.DataSource = results.CopyToDataTable()

End Sub

End Class
 
I took a quick glance at it, but before going much further, can you give me
a quick summary of what you want the end results to look like and what
problems you're having so far. I can surmise you want to aggregate (SUM)
Qty_Sold and Qty_On_Hand but what do you want to see in the grid. Do you
want details with a rollup at the bottom or something along those lines? If
you can just let me know what the end results should look like I can be of
more help.

Thanks,

Bill

--
Cordially,

W.G. Ryan, MVP
Principal Architect
Global Emergency Resources
http://www.ger911.com | Bill.Ryan@[LeaveThisOut].ger911.com
 
Thank you for your time Bill!

Here's an example (minus the date and type):

Text file:
UPC On Hand Sold
90056487 9 0
90056487 12 0
90056487 0 15
90056487 0 5
90056215 0 9

Resulting query:
90056487 21 20
90056215 0 9

Group by UPC and sum these two columns. Just a simple listing.

The way I last last left the code I believe it stated, "expected into" at
the end of my INTO line... This is after I changed From to Aggregate. I've
tried many other things and the average error I received was:

"When casting from a number, the value must be a number less then infinity".

I get that error on the query itself.

Thanks again!

William Ryan said:
I took a quick glance at it, but before going much further, can you give
me a quick summary of what you want the end results to look like and what
problems you're having so far. I can surmise you want to aggregate (SUM)
Qty_Sold and Qty_On_Hand but what do you want to see in the grid. Do you
want details with a rollup at the bottom or something along those lines?
If you can just let me know what the end results should look like I can be
of more help.

Thanks,

Bill

--
Cordially,

W.G. Ryan, MVP
Principal Architect
Global Emergency Resources
http://www.ger911.com | Bill.Ryan@[LeaveThisOut].ger911.com
Justin said:
If I perform a simple select query in my project I'm able to fill my
gridview with no problem. However, once I wonder over to an aggregated
query were I sum two columns I run into tons of problems.

I also could never get this to work with a dataset. Since I only had one
table I opted to use only a datatable. Is that problematic?

Can someone help me out please?

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim dt As New Data.DataTable
Dim dr As Data.DataRow
Dim LineIn As String
Dim XQ
Dim LIN
Dim ZA
Dim StreamReader As System.IO.StreamReader

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(Int64)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand",
GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))


StreamReader = IO.File.OpenText("txt.txt")

While StreamReader.Peek <> -1
LineIn = StreamReader.ReadLine()

If Mid(LineIn, 1, 2) = "XQ" Then
XQ = Split(LineIn, "*")
ElseIf Mid(LineIn, 1, 3) = "LIN" Then
LIN = Split(LineIn, "*")
Else
ZA = Split(LineIn, "*")
dr = dt.NewRow()
dr("Date") = Mid(XQ(2), 5, 2) & "/" & Mid(XQ(2), 7, 2) &
"/" & Mid(XQ(2), 1, 4)
dr("UPC") = LIN(3)
dr("QTY_Sold") = "0"
dr("QTY_OnHand") = ZA(2)
dr("Unit_Type") = ZA(3)
dt.Rows.Add(dr)
End If
End While

StreamReader.Close()

'SELECT DATE, UPC, SUM(QTY_Sold), SUM(QTY_OnHand), Unit_Type FROM
dt
Dim results As IEnumerable(Of DataRow) = Aggregate o In dt _
Group By UPC_Group =
o.Field(Of Int64)("UPC") _
Into test =
Sum(o.Field(Of Integer)("QTY_Sold")), test2 = Sum(o.Field(Of
Integer)("QTY_OnHand"))

DataGridView1.DataSource = results.CopyToDataTable()

End Sub

End Class
Click to expand...
Click to expand...
 
Hi Justin,

the error indicates an overflow on the numeric value. Would you try modify
the following code into two lines:

DataGridView1.DataSource = results.CopyToDataTable()

such as

DataTable dt1=results.CopyToDataTable()
DataGridView1.DataSource=dt1

thus, we can further get the line that raise the exception and get whether
it is the generation step of the linq result or the generated datatable
that cause the problem.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
For "results":

If I use Aggregate I still have, "Into expected".

If I use From I still have the same error:

Unable to cast object of type
'System.Linq.GroupedEnumerable`4[System.Data.DataRow,System.Int64,System.Data.DataRow,VB$AnonymousType_1`3[System.Int64,System.Int32,System.Int32]]'
to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Updated Code:

Dim dt As New Data.DataTable
Dim dr As Data.DataRow
Dim LineIn As String
Dim XQ
Dim LIN
Dim ZA
Dim StreamReader As System.IO.StreamReader

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(Int64)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))


StreamReader = IO.File.OpenText("walgreens.txt")

'Same While statement as before.

StreamReader.Close()

Dim results As IEnumerable(Of DataRow) =
Aggregate o In dt _
Group By UPC_Group = o.Field(Of Int64)("UPC") _
Into test = Sum(o.Field(Of Integer)("QTY_Sold")), test2 = Sum(o.Field(Of
Integer)("QTY_OnHand"))

Dim dt1 As New Data.DataTable
dt1 = results.CopyToDataTable()
DataGridView1.DataSource = dt1
 
Hi Justin,

I've performed some further research on this. After testing the query, I
found that the error is actually due to the anonymous object collection
returned by the LINQ query cannot be casted to DataTable (or you can say
the anonymous object type cannot be casted to a DataRow). Also, since
DataRow doesn't have an default constructor, I cannot explicitly use "new"
keyword to specify the return object type.

My current solution is directly bind the result set (executed from the LINQ
query) to the DataGridView. You need to use "ToList()" to convert the
result set into a IList interface collection. e.g.

===========================================
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim dt As New Data.DataTable

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(Int64)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))


Dim dr As DataRow = dt.NewRow()
dr("Date") = "2008/1/1"
dr("UPC") = 32
dr("QTY_Sold") = 1
dr("QTY_OnHand") = 22
dr("Unit_Type") = 30
dt.Rows.Add(dr)

Dim dr1 As DataRow = dt.NewRow()
dr1("Date") = "2008/1/1"
dr1("UPC") = 32
dr1("QTY_Sold") = 22
dr1("QTY_OnHand") = 22
dr1("Unit_Type") = 30
dt.Rows.Add(dr1)

Dim results = From o In dt Group o By num = o!UPC Into g = Group _
Select num, NumberGroup = g.Sum(Function(n)
n.Field(Of Integer)("QTY_Sold"))
DataGridView1.DataSource = results.ToList()

End Sub
=================================

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: "Justin" <[email protected]>
References: <[email protected]>
Click to expand...
Subject: Re: Need help with LINQ Query
Date: Tue, 10 Jun 2008 11:40:38 -0700
Click to expand...
For "results":

If I use Aggregate I still have, "Into expected".

If I use From I still have the same error:

Unable to cast object of type
'System.Linq.GroupedEnumerable`4[System.Data.DataRow,System.Int64,System.Da ta.DataRow,VB$AnonymousType_1`3[System.Int64,System.Int32,System.Int32]]'
to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Updated Code:

Dim dt As New Data.DataTable
Dim dr As Data.DataRow
Dim LineIn As String
Dim XQ
Dim LIN
Dim ZA
Dim StreamReader As System.IO.StreamReader

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(Int64)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))


StreamReader = IO.File.OpenText("walgreens.txt")

'Same While statement as before.

StreamReader.Close()

Dim results As IEnumerable(Of DataRow) =
Aggregate o In dt _
Group By UPC_Group = o.Field(Of Int64)("UPC") _
Into test = Sum(o.Field(Of Integer)("QTY_Sold")), test2 = Sum(o.Field(Of
Integer)("QTY_OnHand"))

Dim dt1 As New Data.DataTable
dt1 = results.CopyToDataTable()
DataGridView1.DataSource = dt1
Click to expand...
 
I understand where you went with that. However, ToList doesn't seem to be
available?:

Public member 'ToList' on type '<SelectIterator>d__d(Of
VB$AnonymousType_0(Of Object,IEnumerable(Of DataRow)),VB$AnonymousType_1(Of
Object,Integer))' not found.


New Code:

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim dt As New Data.DataTable
Dim LineIn As String
Dim XQ
Dim LIN
Dim ZA
Dim StreamReader As System.IO.StreamReader

dt.Columns.Add(New Data.DataColumn("Date", GetType(Date)))
dt.Columns.Add(New Data.DataColumn("UPC", GetType(String)))
dt.Columns.Add(New Data.DataColumn("QTY_Sold", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("QTY_OnHand", GetType(Integer)))
dt.Columns.Add(New Data.DataColumn("Unit_Type", GetType(String)))

StreamReader = IO.File.OpenText("txt.txt")

While StreamReader.Peek <> -1
LineIn = StreamReader.ReadLine()

If Mid(LineIn, 1, 2) = "XQ" Then
XQ = Split(LineIn, "*")
ElseIf Mid(LineIn, 1, 3) = "LIN" Then
LIN = Split(LineIn, "*")
ElseIf Mid(LineIn, 1, 5) = "ZA*QA" Then
ZA = Split(LineIn, "*")
Dim dr As DataRow = dt.NewRow()
dr("Date") = Mid(XQ(2), 5, 2) & "/" & Mid(XQ(2), 7, 2) & "/"
& Mid(XQ(2), 1, 4)
dr("UPC") = LIN(3)
dr("QTY_Sold") = "0"
dr("QTY_OnHand") = ZA(2)
dr("Unit_Type") = ZA(3)
dt.Rows.Add(dr)
ElseIf Mid(LineIn, 1, 5) = "ZA*QS" Then
ZA = Split(LineIn, "*")
Dim dr As DataRow = dt.NewRow()
dr = dt.NewRow()
dr("Date") = Mid(XQ(2), 5, 2) & "/" & Mid(XQ(2), 7, 2) & "/"
& Mid(XQ(2), 1, 4)
dr("UPC") = LIN(3)
dr("QTY_Sold") = ZA(2)
dr("QTY_OnHand") = "0"
dr("Unit_Type") = ZA(3)
dt.Rows.Add(dr)
End If
End While

StreamReader.Close()

Dim results = From o In dt _
Group o By UPC_Group = o!UPC _
Into g = Group _
Select UPC_Group, Sum1 = g.Sum(Function(n) n.Field(Of
Integer)("QTY_Sold"))

DataGridView1.DataSource = results.ToList()

End Sub

End Class
 
Justin,

What do you want to use a datacontext or a dataset (and members of those
like the datatable).

A pencil is not a roller pen.

You can write with both, but that does not mean that you can use them
exactly for the same results.

Just my idea seeing this long thread of you, where you persist in your own
idea that you should be able to use a roller pen and then get the same soft
results as with a pencil to make a drawing.

If you think you could, then do it, but don't ask us how.

Cor
 
Thanks for your reply Justin,

I'm not sure whether there is anything different in the project setting.
I'm using a vs 2008 vb.net (.net 3.5) winform project. Based on my
understanding, "ToList" should be an auto-generated method for the LINQ
query result. If necessary, I can send you my test project. You can first
email me via the following address:

"stcheng" + @ + "microsoft.com"

Sincerely,

Steven Cheng
Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
From: "Justin" <[email protected]>
References: <[email protected]>
Click to expand...
<[email protected]>
 
Hi Justin,

I've received the project you sent. Yes, based on the project you provided,
I've repro the same behavior. I found that the project contains some .net
2.0 properties, is this project comes from upgrading an existing .net 2.0
vb.net project? I think this maybe the cause and there may has something
different of the design-time code validation against the LINQ code.

I've tried move the same code in your project into a new created VS 2008
..NET 3.5 project and it works correctly.

Would you also have a try on this or whether your scenario is not as I've
described above?

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
From: (e-mail address removed) (Steven Cheng [MSFT])
Organization: Microsoft
Date: Thu, 12 Jun 2008 07:59:23 GMT
Subject: Re: Need help with LINQ Query
Click to expand...
 
Hum...I posted a reply but I don't see it here.

What you suggested is exactly what was wrong. I completely forgot this was
an upgraded project.

I created a new project and your code worked like a charm!

I can't thank you enough for your time on this issue.

Justin


Steven Cheng said:
Hi Justin,

I've received the project you sent. Yes, based on the project you
provided,
I've repro the same behavior. I found that the project contains some .net
2.0 properties, is this project comes from upgrading an existing .net 2.0
vb.net project? I think this maybe the cause and there may has something
different of the design-time code validation against the LINQ code.

I've tried move the same code in your project into a new created VS 2008
NET 3.5 project and it works correctly.

Would you also have a try on this or whether your scenario is not as I've
described above?

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
From: (e-mail address removed) (Steven Cheng [MSFT])
Organization: Microsoft
Date: Thu, 12 Jun 2008 07:59:23 GMT
Subject: Re: Need help with LINQ Query
Click to expand...
Click to expand...
 
Hi Justin,

Thanks for your followup.

I'm very glad that it helps you resolve the problem.

Have a nice day!

Sincerely,

Steven Cheng
Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
From: "Justin" <[email protected]>
References: <[email protected]>
Subject: Re: Need help with LINQ Query
Date: Mon, 16 Jun 2008 16:53:30 -0700
Click to expand...
 
Excuse me? I came here for help. If you want to help then great!
Otherwise, why are you here?

The concept is simple.

Create Datatable
Fill Datatable
Query Datatable
Display Results

Had you read so much as the FIRST LINE of my first post you would have seen
that this solution already works when I SELECT *. I just couldn't get the
aggregated stuff to work.

For the record, I haven't persisted in anything and I NEVER said I "should"
be able to do anything. I'm open to ALL ideas. Get your eyes checked.

In the end, your analogy is bogus, nonsense and a waste of time. As you can
see the problem was solved. Now I have a super simple solution all handled
in memory and I'm not looping like a mad man in a horrible looking, monkey
rigged array solution.
 
Back
Top