[RESOLVED] Value of type 'System.Linq.IQueryable(Of Integer)' cannot be converted to 'Integer'.

[simply_misunderstood]

VB 2008, SQL Server 2005 Express, Using LINQ

Dim db As New CasinoHRDataContext
Dim deptid = From d In db.Departments _
Where d.Department _
Like Me.ListBoxDepartments.SelectedItem.ToString _
Order By d.DepartmentID _
Select d.DepartmentID

'Label1.Text = deptid.Single.ToString

Dim add As New Position With {.DepartmentID = deptid, .Position = txtPosition.Text}
db.Positions.InsertOnSubmit(add)
db.SubmitChanges()
txtPosition.Text = ""
showTable()

Hi i'm trying to add a new record to table positions this has a fk from table departments. I use a query to return the departmentID based on what is selected in listboxdepartments. I've checked that the right departmentID is being returned (hence the label1.text = deptid.single.tostring).

In the add variable i'm getting the following error for deptid (Value of type 'System.Linq.IQueryable(Of Integer)' cannot be converted to 'Integer'.)

it's some kind of conversion error but i'm not sure where to go from here. All help appreciated.

regards

[jmcilhinney]

When you perform a LINQ query like that you don't get back a single value. Your deptid variable is NOT type Integer. A LINQ query returns, as the error message suggests, an IQueryable object. You need to enumerate that object to get the individual values it contains, which you do with a For Each loop. Even if it only contains a single value it doesn't matter. You still need to use a loop to get at that one value.

Dim deptIDs = From d ...
 
Dim deptID As Integer
 
For Each deptID In deptIDs
Next

After that loop deptID will contain the last value returned by the query. If the query only returned one value then the last value is the only value.

In a special case like this, where want the one and only value, you could also do this:

Dim deptID As Integer = deptIDs.ElementAt(0)

or this:

Dim deptID As Integer = deptIDs.First()

or this:

Dim deptID As Integer = deptIDs.FirstOrDefault()

[simply_misunderstood]

Thanks that worked a treat.