Posts Tagged ‘Linq’

Linq to SQL Query gives an InvalidOperationException with message “Cannot compare entities associated with different tables.”

Wednesday, September 15th, 2010

If you try to do a Linq to SQL query and it runs fine, but gives you an exception of type InvalidOperationException and the message is “Cannot compare entities associated with different tables.” then it probably means that you are trying to pass in an object of one type and compare it to another type.  I suspect that you will probably only get this error if Linq attempts to translate the error to SQL – if you run it in memory, you might not get an error at all, as it may just do a straight object comparison and conclude that they are different objects.

An example would be something like


Dim p = DBContext.People.First
Dim q = From a in DBContext.Addresses Where a Is p
Dim addr = q.First

The final line will result in this exception as Linq to SQL is attempting to translate the query into SQL and it (correctly) can’t find any way of writing a query that compares a person to an address!

Linq to SQL Quirks Part 4: Circular References

Friday, July 10th, 2009
This entry is part of a series, Linq to SQL Quirks»

In a project that I am working on at the moment, I have a situation where I have a Person table and a Family table.  The Person table has a FamilyID field, which is a foreign key to the Families ID (primary key) field, and the Family table has a DefaultPersonID, which is another foreign key back to Person to store the default contact for a family.

The problem is that if I create a new Person and Family with the Person being the DefaultContact, I get an InvalidOperationException thrown with a message “A cycle was detected in the set of changes”.  The problem is that Linq to SQL doesn’t know what to store first.  The only way that I have come up with to get around this is to do the following…

            If Person.ID = 0 AndAlso Not Person.Family Is Nothing Then
               
                Dim fam = Person.Family
                Person.Family = Nothing

                DBContext.SubmitChanges()
                Person.Family = fam

            End If

            DBContext.SubmitChanges()

This works by breaking the circular reference, so that there is only a 1 way reference, then submitting to the database, and once submitted, restoring the removed reference and submitting changes again

Linq to SQL Quirks Part 3 – Extension to Get Around the Delete Problem

Tuesday, June 30th, 2009
This entry is part of a series, Linq to SQL Quirks»

My previous post about problems deleting can be partly handled with an extension method to the Linq to SQL Table(Of T) class with something like this…

         Public Sub DeleteOnSubmitOrDetach(Of T As Class)(ByVal table As System.Data.Linq.Table(Of T), ByVal o As T)
            Dim IDProperty = o.GetType.GetProperty("ID")
            If IDProperty Is Nothing Then
                Throw New System.MissingFieldException("Trying to delete object of type " & GetType(T).Name & " - does not have an ID field")
            End If
            If IDProperty.GetValue(o, New Object() {}) = 0 Then
                table.InsertOnSubmit(o)
            End If
            table.DeleteOnSubmit(o)
        End Sub

Note that you’ll need to import System.ComponentModel and System.Reflection and possibly some others, and it assumes that you have an IDENTITY ID field by that name on all of your classes that you use this with.

Linq Part 3 – Query Format

Monday, June 29th, 2009
This entry is part of a series, Linq»

It is very easy when you are familiar with SQL to constrain yourself to treating Linq queries in a similar fashion and not take advantage of some of the new features that Linq allows. Of course, Linq does impose its own constraints, as does each Linq provider, so it isn’t always easier.

It is definitely worth getting your head around the fact that in Linq, you can query a query.  With some providers, this might mean that you are querying the results of a query, but with others, it can mean that you are simply building another query, which will only actually be executed when you try to retrieve the results.

For example,

Dim q = From p in DataContext.People Where p.Surname=”Smith”

q = From p in q Where p.FirstName=”John”

In Linq to SQL, this is perfectly efficient as it will only run SELECT * FROM Person WHERE Surname=’Smith’ AND FirstName=’John’ or something similar.

Equally, you can also combine multiple queries into a single statement, such as…

Dim q = From p in DataContext.People Select p.ID, p.Surname Order By p.Surname Select ID

…and of course you don’t need to worry about the order of the statements – notice a Select can come after an Order By!  The thing to remember is that each Select/Group effectively creates a different context (as in a variable context), so you have different variables available at each point in the query.

Linq Part 2 – From

Wednesday, June 24th, 2009
This entry is part of a series, Linq»

A Linq query will always start with From item in datasource

When I initially started using Linq, I thought that was a little odd, and why didn’t Microsoft just use SQL style Select columns from datasource.  I thought about this for a minute, and realised that the problem with that is that you can’t use Intellisense to get the column names because you don’t actually know what they are until you’ve specified the data source.

Another reason for this is that the Select part of a Linq query is not compulsory, so it makes more sense to start with the part that is.

You might find it a little confusing having to specify item if you are thinking in SQL terms, as you would usually just include a table name and possibly an alias in SQL.  The point in item is that a Linq query is a bit like a For loop in a single statement.  You are effectively looping around the items in the data source.  An individual provider might not actually implement it that way (Linq to SQL doesn’t – if you use a Where clause, it is translated to SQL instead of retrieving all the data and checking what matches), but that is the abstraction that we are using.  We don’t reference the item by using the datasource name because we might still want to access something else in the datasource separately in the query.

Linq to SQL Quirks Part 2: DeleteOnSubmit and Entity not Attached

Tuesday, June 23rd, 2009
This entry is part of a series, Linq to SQL Quirks»

If you use an implicit insert on a new object and then decide that you don’t actually want it, you have a bit of a problem.  First of all, you need to remove the relationships that you created so that it doesn’t show up in any places that it shouldn’t.  The problem with this is that Linq will still kind of (see below) have it marked for insertion, so if you call SubmitChanges without doing anything else, it will either still insert it, or if you’ve removed a relationship which relies on a non-nullable foreign key, you’ll get an error like

“An attempt was made to remove a relationship between a A and a B. However, one of the relationship’s foreign keys (A.BID) cannot be set to null.”

Where A and B are the 2 appropriate Linq to SQL classes.

The next thing we need to do then is to make sure that Linq to SQL removes the item from the insert list.  The only way that I have found to do this is to call DeleteOnSubmit on the appropriate table.  The problem is that if we do that at this point, we get an InvalidOperationException with message “Cannot remove an entity that has not been attached.”

Apparently, Linq to SQL knows it is supposed to insert it, but it isn’t properly attached.  To get around this, we need to call InsertOnSubmit on the appropriate table before calling DeleteOnSubmit.

That’s all fine and dandy and works, as long as you are trying to delete something that hasn’t been inserted yet.  Normally, however, I use the same code whether it has been inserted or not, and of course if you call InsertOnSubmit on an entity that has already been inserted, you get an InvalidOperationException saying “Cannot add an entity that already exists.”.  Therefore, before you call InsertOnSubmit, you need to check if it actually has already been inserted.  Since I always have identity fields with the name ID on all my tables and Linq to SQL updates these when it does an insert, I normally just check that the ID is 0 before inserting.

The resulting steps that you need to solve the problem are therefore: –

  1. Remove the relationships from item
  2. If the ID=0 then DataContext.Table.InsertOnSubmit(item)
  3. DataContext.Table.DeleteOnSubmit(item)

You shouldn’t actually have to call DataContext.SubmitChanges at this point – the whole point in the exercise is to mean it will be safe when you do later.

Alternatively, you can just throw away the datacontext without calling SubmitChanges, but I find that is rarely an option because there may be other data that needs submitting.

By the way, you need to do the first step of removing the relationships in order to make sure that none of your code accidentally tries to access the removed object as it will still be in memory as a related object until SubmitChanges is called otherwise and you could end up processing it when accessing entities related to an object that it is related to.

Linq to SQL Quirks Part 1: Implicit Inserts

Tuesday, June 23rd, 2009
This entry is part of a series, Linq to SQL Quirks»

There are a few things that Linq to SQL can be a bit funny about that aren’t so obvious.

First of all, there are what I call implicit inserts.  You might not have realised that you don’t actually need to explicitly call InsertOnSubmit on a table for Linq to SQL to do the insert as long as you have created a relationship to an object which already exists in the database.  Linq to SQL will automatically insert the new object on the next SubmitChanges call.

Linq Part 1 – What is Linq?

Tuesday, March 24th, 2009
This entry is part of a series, Linq»

Linq is an abbreviation for Language Integrated Query.  The idea of it is that you can write a query on a source of data inline as part of your code.  It is particularly clever because Linq allows different providers to be used so that different types of data can be queried.  There are Linq providers that are part of the .NET Framework for querying anything based on IEnumerable (including Lists, Collections, Arrays, etc.), XML, SQL Server databases (using Linq to SQL). 

There are also providers available for querying Oracle, MySQL, various webservices (one of the earliest ones I came across was Linq to Amazon).  It is a very powerful system with lots of flexibility.

Another point about Linq is that you can build queries on top of queries and depending on the provider, it only compiles and executes the final query when you try to access the data.  For example, you could do a query like

Dim people = From p in DB.People Where p.FirstName=”John”

people = From p in people Where p.Surname=”Smith”

Once you try to access people, (eg. by looping through it), Linq will execute an SQL query something like

SELECT [ID], [FirstName], [Surname] FROM [Person] WHERE [Person].[FirstName]=’John’ AND [Person].[Surname]=’Smith’

Linq and Reflection

Wednesday, April 2nd, 2008

I’ve been using Linq to do queries on Reflection objects recently and I’ve found that it works quite well.  An example: –

From pi In Me.GetType.GetProperties Select pi Where pi.CanRead And pi.CanWrite And Not pi.Name Like “*_Display” And pi.GetGetMethod.GetParameters.Length = 0 And (pi.PropertyType Is GetType(String) Or pi.PropertyType Is GetType(Boolean) Or pi.PropertyType Is GetType(Date) Or pi.PropertyType Is GetType(Integer) Or pi.PropertyType Is GetType(Boolean?) Or pi.PropertyType Is GetType(Date?) Or pi.PropertyType Is GetType(Integer?) Or pi.PropertyType.IsEnum)

String Processing With Linq

Wednesday, March 26th, 2008

It just occurred to me that since a String can be treated as an array of characters, you should be able to do a Linq query on it, so I tried it, and it seems to work quite well…

Dim Test = “The cat sat on the mat”
Dim Test2 = (From c In Test Select c Where c <> ” “).ToArray()
Console.Write(Test2)

Personally, I much prefer string processing this way than with Regular Expressions.  Granted, it isn’t so versatile and is undoubtedly more processor intensive, but if you aren’t bothered about the clock cycles and it will do what you want, it is much easier to code and read!