Entity Framework Does not support object comparison in Queries
As I continue to migrate my linq to SQL code over to entity framework, I am discovering more constraints that I never faced working with linq to SQL. For instance in one of my linq query, I was able to do object comparison to apply filter to queries. For example, If you wanted to retrieve customers who are in the city of London and also customer that you currently hold in your hand and find out the count of customers that meet this criteria or probably find out how much total sales those customers have given us, you probably want to perform this query on the database. In the past I could apply object comparison in my query rather than applying comparisons based on column values. You could actually use column values to dictate your filters, but sometimes being explicit on how you are applying a filter puts noise in your code as compared to just saying make sure this customer is also part of the filter.
Below is an example of code, that demonstrates running a query to get the count of customers who are in the city of London or customer filtered using object comparison.
In the above example, I am first retrieving a customer based on primary key column. In the next query, I am use the object that I obtained from my previous query to filter the customers to only customers that are in City of London or matches the customer that has the same object reference as the reference passed in the query. Although it may seem that you are doing object comparison but in reality linq to SQL transforms the object comparison to filter being applied on database based on primary key. You can confirm this behavior by looking at the SQL query that was generated. Interesting point to consider is, the entire query got execute on the database including the count operation and filter based on primary key column.
The concept of object comparison does not work in entity framework. In fact applying this concept will give you a runtime error because entity framework cannot transform object comparison to filter being applied on the database. Below code shows the same example being written in entity framework which causes runtime exception.
When you run the above piece of code, you will get a runtime error saying Unable to create a constant value but if you get rid of object comparison in the above entity query, you will not get this error.