How to get access to value in LINQ query result

StevenF · May 7th, 2009, 10:00 AM

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

Lee Dumond · May 7th, 2009, 04:25 PM

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

StevenF · May 9th, 2009, 10:46 AM

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?

Lee Dumond · May 9th, 2009, 05:25 PM

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

Imar · May 10th, 2009, 03:06 AM

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

Lee Dumond · May 10th, 2009, 02:08 PM

More than one way to get to Rome.

StevenF · May 12th, 2009, 05:46 AM

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