Solved

return LINQ results and bind to gridview - VB.NET

Posted on 2012-03-14
9
1,698 Views
Last Modified: 2012-03-14
Hello EE!

Trying to query a datatable using LINQ to enhance my application.  I'm able to get the query working just fine but want to return it as a list and further bind it to a gridview.








    

Public Class Employee

_code as string
_title as string
_min as decimal

public sub new(byval c as string, byval t as string, byval m as decimal)

Me.Code = c
Me.Title = t
Me.SalaryMin = m

end sub

End Class

'my function to return the query results
Protected Function rtnLINQ() As IQueryable(Of Employee)

        Dim ds As New DataSet
        ds = DirectCast(Session("oDS"), DataSet)

        Dim dtGroups As DataTable = ds.Tables(0)

        Dim qLINQ As List(Of Employee) = From group In dtGroups.AsEnumerable() Where _
                    group.Field(Of Integer)("POS_ID") = 1321 _
        Select New Employee With { _
        .JobCode = group.Field(Of String)("JOB_CODE"), _
        .JobTitle = group.Field(Of String)("JOB_TITLE"), _
        .SalaryMin = group.Field(Of Decimal)("SALARY_MIN") _
        }

        Return qLINQ

End Function


Public Sub GetMyFilter()

gv.datasource = rtnLINQ()
gv.databind()

End Sub

Open in new window

0
Comment
Question by:sk1922
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:markmiddlemist
ID: 37722891
What you've got there should work. What's not doing what you expect?
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 350 total points
ID: 37722915
You don't have to return the object as a List in order to databind it. Usually, you can assign the Linq query itself as the DataSource.

I do see that you've got a bit of implicit conversion attempting to go on. My suggestion would be to change the function's return type from IQueryable to IEnumerable and use a type-less Dim for your Linq query reference variable. In other words:

Protected Function rtnLINQ() As IEnumerable(Of Employee)

        Dim ds As New DataSet

        ds = DirectCast(Session("oDS"), DataSet)

        Dim dtGroups As DataTable = ds.Tables(0)

        Dim qLINQ = From group In dtGroups.AsEnumerable() _
                    Where group.Field(Of Integer)("POS_ID") = 1321 _
                    Select New Employee With _
                           { _
                               .JobCode = group.Field(Of String)("JOB_CODE"), _
                               .JobTitle = group.Field(Of String)("JOB_TITLE"), _
                               .SalaryMin = group.Field(Of Decimal)("SALARY_MIN") _
                           }

        Return qLINQ

End Function

Open in new window

0
 

Author Comment

by:sk1922
ID: 37722958
Thank you both for the response.  

Before and even after changing from IQueryable to IEnumberable, I get this error:


Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30455: Argument not specified for parameter 'code' of 'Public Sub New(code As String, title As String, salMin As Decimal)'.

Source Error:

Line 46:         Dim qLINQ = From group In dtGroups.AsEnumerable() Where _
Line 47:                     group.Field(Of Integer)("POS_ID") = 1321 _
Line 48:         Select New Employee With {.JobCode = group.Field(Of String)("JOB_CODE"), .JobTitle = group.Field(Of String)("JOB_TITLE"), .SalaryMin = group.Field(Of Decimal)("SALARY_MIN")}
Line 49:
Line 50:         Return qLINQ
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 100 total points
ID: 37722966
Do you have a parameterless constructor on the employee class?
0
 

Author Comment

by:sk1922
ID: 37722988
Ahhhhh haaaaa!!! That's was it. Awesome!


Now, in your comment above, you mentioned "usually you can bind the LINQ results as a DataSource..."  in what instances (high-level) do you typically need to convert the results into a list?  I've seen, plastered all over the web, examples that use .ToList()... any words of wisdom you can offer up on this?  I just want to get some understanding before I go off trying to LINQ-ify some of my applications.... :) fun times.
0
 
LVL 7

Expert Comment

by:markmiddlemist
ID: 37722996
The way your code is set up the linq query is trying to create the Employee class with the parameterless constructor as CodeCruiser says, then populate each of the parameters in the With claue. As your constructor is actually expecting each of the values, what you should actually do if you don't want to change the constructor is pass the values in as parameters to the constructor as so:

Line 46:         Dim qLINQ = From group In dtGroups.AsEnumerable() Where _
Line 47:                     group.Field(Of Integer)("POS_ID") = 1321 _
Line 48:         Select New Employee(group.Field(Of String)("JOB_CODE"), group.Field(Of String)("JOB_TITLE"), group.Field(Of Decimal)("SALARY_MIN"))
Line 49:
Line 50:         Return qLINQ
0
 
LVL 7

Assisted Solution

by:markmiddlemist
markmiddlemist earned 50 total points
ID: 37723000
The IEnumerable is fine for your databinding here. I think you'll find that most of the examples with .ToList are when using Ling2Sql or EntityFramework, where the ToList is often used to force actual execution of the generated SQL and loading of the data in to your classes, so thats not going to be required in your model.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37723042
I think you'll find that most of the examples with .ToList are when using Ling2Sql or EntityFramework, where the ToList is often used to force actual execution of the generated SQL and loading of the data in to your classes, so thats not going to be required in your model.
Agreed. But I believe in databinding the query itself you get the same effect as calling ToList. I'd have to test it out though.
0
 

Author Closing Comment

by:sk1922
ID: 37723052
Thank you for the clarification and the code... really helped.  I'm on my way.... slowly but surely :)
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question