Solved

return LINQ results and bind to gridview - VB.NET

Posted on 2012-03-14
9
1,658 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
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 100 total points
ID: 37722966
Do you have a parameterless constructor on the employee class?
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to pass a connection through class/Method 1 39
Host asp.net pages 5 25
Receiving a string from a WebService Push 21 36
ASP.NET 5 Templates 2 65
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now