ASP.NET LINQ to Delete Rows

Question

Dim db As New SQLDataContext
Try
    Dim deleteBoatPics = (From boat In db.Photos
                          Where boat.boatid = id)
    db.Photos.DeleteOnSubmit(deleteBoatPics)
    db.SubmitChanges()
Catch ex As Exception
End Try

I'm getting an error that says: Unable to cast object of type 'System.Data.Linq.DataQuery`1[WhiteWaterPhotos.Photo]' to type 'WhiteWaterPhotos.Photo'.

I have two separate db.SubmitChanges() because when the button is pressed, I have it delete the records from 1 table, and then the next.

I'm lost, can someone help me out?

Answer 1

try this:

Dim db As New SQLDataContext
Try
    Dim deleteBoatPics = (From boat In db.Photos
                          Where boat.boatid = id).take(1).singleordefault
    if not deleteBoatPics is Nothing Then
       db.Photos.DeleteOnSubmit(deleteBoatPics)
       db.SubmitChanges()
    End If
Catch ex As Exception
End Try

Or for a list of elements:

Dim db As New SQLDataContext
Try
    Dim deleteBoatPics = (From boat In db.Photos
                          Where boat.boatid = id).ToList()
    if not deleteBoatPics is Nothing Then
       db.Photos.DeleteAllOnSubmit(deleteBoatPics)
       db.SubmitChanges()
    End If
Catch ex As Exception
End Try

Answer 2

try

Dim db As New SQLDataContext
Try
    Dim deleteBoatPics = (From boat In db.Photos
                          Where boat.boatid = id
                          select boat)
    db.Photos.RemoveAll(deleteBoatPics)
    db.SubmitChanges()
Catch ex As Exception
End Try

I don't know vb.net that much but in c# is would have a "select boat" at the end of that query and use RemoveAll(...)