Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 7th, 2009, 10:00 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default How to get access to value in LINQ query result

I am using LINQ to pull out data from a database. Setting up the LINQ query seems to be ok. However, I am having problems with getting access to the results from the query.

In a simplifed example, I have created a table ("Test_table") with three fields (VariableA, VariableB, and Value). I then use a LINQ query (query variable "data") to select the whole table (all records from all three fields). Finally, I want to get access to the individual items and records in the query result. For example, to access the value of "VariableA" from the first case in the table.

When I run the procedure and look at the query variable "data" in the watch window, it contains a collection called "results", which has one item for each case in the table. I therefore assumed that I could use code such as this to access the value for VariableA for the first case:
Code:
        Dim test As Integer
        test = data(0).VariableA
I don't get an error when writing the code, so at first I thought this would be ok. However, when I run it, I get the following error message:
"The query operator 'ElementAtOrDefault' is not supported."

If I had only selected a single field in my LINQ query, things would have been more straightforward. With multiple fields, they seem more complicated. Any pointers on how to work with results from LINQ queries, especially when they contain more than one field, would be much appreciated.

Here's a screenshot illustrating my situation:

http://img136.imageshack.us/img136/8...linqresult.jpg
Here's the full code I used:

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

        Dim newDataContext As New LinqTestDataContext()

        Dim data = newDataContext.Test_tables _

        Dim test As Integer
        test = data(0).VariableA
    End Sub
  #2 (permalink)  
Old May 7th, 2009, 04:25 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

LINQ to SQL cannot translate queries which speciy an index into SQL. It does not support translating any of the query operators that have an index argument.

Try this instead:

Code:
Imports System.Linq
Partial Class LinqTestPage
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click (ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
 
      Dim data As LinqTestDataContext = New LinqTestDataContext()
      Dim test = data.Test_tables.First().VariableA
 
    End Sub
End Class
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
StevenF (May 9th, 2009)
  #3 (permalink)  
Old May 9th, 2009, 10:46 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks very much for this. It is very useful to know what cannot be done with LINQ. I will look at another data access approach where I can get access to the index of the query result.

Just a quick follow up question on your suggested alternative code:
The code
Code:
Dim test = data.Test_tables.First().VariableA
gets me the first item in the table, because there happens to be a ".First" operator. As we can't use the index, I'm assuming there's no way to get, say the fifth or eight item?
  #4 (permalink)  
Old May 9th, 2009, 05:25 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Usually, you would probably want to select a record based on ID or some other useful criteria.

If you really need to select an item by index, you could easily do that by fetching all the records, converting to some sort of in-memory collection object, then selecting by index from the collection as you usually would.

Code:
Imports System.Linq
Partial Class LinqTestPage
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click (ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
 
      Dim data As LinqTestDataContext = New LinqTestDataContext()
 
      'convert to in-memory Array
      Dim coll = data.Test_tables.ToArray()
 
      'select VariableA from 3rd record
      Dim test As Integer = coll(2).VariableA
 
    End Sub
End Class
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #5 (permalink)  
Old May 10th, 2009, 03:06 AM
Imar's Avatar
Wrox Author
Points: 72,038, Level: 100
Points: 72,038, Level: 100 Points: 72,038, Level: 100 Points: 72,038, Level: 100
Activity: 50%
Activity: 50% Activity: 50% Activity: 50%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,080
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

What about Skip and Take? While using ToArray would work, you don't want to do this in a scenario where you have a table with a million records as the query is then executed and all records are brought over from the database. Using Skip and Take you can get indexed or paged results with the filter applied at the server. The following example (written against my PlanetWrox data context from my book Beginning ASP.NET 3.5) shows how it works:
Code:
 
Using myDataContext As New PlanetWroxDataContext()
  ' Using Arrays
  ' Gets all records from the database
  Dim allPictures = myDataContext.Pictures.ToArray() 
  Dim secondPicture = allPictures(1)
 
  ' Using Skip and Take
  Gets just the third record if it exists
  Dim thirdPicture = myDataContext.Pictures.Skip(2).Take(1).FirstOrDefault()
End Using
Take doesn't really know you're only taking one, so you'll need to call FirstOrDefault on its result to get a single Picture rather than a query.

When you execute this, the Skip and Take are converted into SQL that effectively pages a single record:
Code:
SELECT TOP (1) [t2].[Id], [t2].[Description], [t2].[Tooltip], [t2].[ImageUrl], [t2].PhotoAlbumId]
FROM (
SELECT [t1].[Id], [t1].[Description], [t1].[Tooltip], [t1].[ImageUrl], [t1].[PhotoAlbumId], [t1].[ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Id], [t0].[Description], [t0].[Tooltip], [t0].[ImageUrl], [t0].[PhotoAlbumId]) AS [ROW_NUMBER], [t0].[Id], [t0].[Description], [t0].[Tooltip], [t0].[ImageUrl], [t0].[PhotoAlbumId]
FROM [dbo].[Picture] AS [t0]
) AS [t1] 
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
) AS [t2] 
ORDER BY [t2].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
While the query looks a bit messy, it's reasonable efficient, especially when querying large result sets.

Hope this helps,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Last edited by Imar; May 10th, 2009 at 04:12 PM..
  #6 (permalink)  
Old May 10th, 2009, 02:08 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

More than one way to get to Rome.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #7 (permalink)  
Old May 12th, 2009, 05:46 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Lee, Imar,
Thanks guys for your suggestions. Now I have two approaches that work, and my understanding of LINQ has improved...!
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINQ query \ LAMBDA expression - complex requirement sumitshah4u .NET Framework 3.5 0 March 16th, 2009 04:36 AM
Binding IEnumerable result set of a LINQ query prakashbpl .NET Framework 3.5 0 November 11th, 2008 04:41 AM
Using LINQ, with BeerHouse /Instant Result Project SoftMind BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 June 21st, 2007 03:08 AM
Query Result mateenmohd SQL Server 2000 0 November 1st, 2003 03:53 AM
Archiving query result Ned SQL Server 2000 5 October 8th, 2003 03:34 PM



All times are GMT -4. The time now is 05:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.