Solved

return LINQ results and bind to gridview - VB.NET

Posted on 2012-03-14
9
1,676 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

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

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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