LINQ to SQL Sequence contains more than one element

Question

Hi all

I am getting an error when trying to delete a record from the database.

Here is my current deletion code:

Dim BAToRemove = From i In uDC.BookAuthors _
                       Where i.authorID = intAuthorID _
                       And i.bookID = intBookID _
                       And i.main = "N" _
                       Select i Take (1) Distinct

        If BAToRemove.Any Then
            Dim ba As BookAuthor = BAToRemove.First
            uDC.BookAuthors.DeleteOnSubmit(ba)

            Dim cs As ChangeSet = uDC.GetChangeSet

            uDC.SubmitChanges(ConflictMode.ContinueOnConflict)

            If cs.Deletes.Count = 1 Then
                Return True
            Else
                Return False
            End If
        End If

This is the code after fiddling to try and get it to work. Even without the .First and Take(1) distinct, the query definitely only returns a single result. I have used BAToRemove.Count and also manually checked the table and there is only one matching row.

Both BookID and AuthorID are primary keys.

I have also tried just using BAToRemove.First straight in the DeleteOnSubmit.

I have also tried copying all of the elements of the returned object to a new object, then attaching and deleting but I get the error that the element already exists (which it does).

All of the other posts I have founded regarding this problem always seems to turn out to be the fault of using SingleOrDefault, which I am not using, so I'm stumped on this one.

Any ideas?

Answer 1

Can you try calling DeleteAllOnSubmit(ba) and check what gets executed in SQL server profiler, this should give you a heads up on whats going wrong.