This post is a follow-up to an article I wrote a few weeks ago, ADO.NET Entity Framework Comparison Frustration. As a quick re-cap, I was simply trying to filter a list of users with a LINQ query expression by comparing custom classes, not primitive types.

TorvusEntities entities = new TorvusEntities();



 
// Pull my Account Entity from the database
Account matt = entities.Accounts.First(a => a.AccountId == 10);

// Attempt to get all Teams by a Team Owner
var teams = from t in entities.Teams
                 where t.Owner == matt
                 select t;

However, an exception was thrown.

Unable to create a constant value of type 'Closure type'. Only primitive types (for instance Int32, String and Guid) are supported in this context.

So I left it at that. The Entity Framework could only handle comparison on primitive types, which makes sense of course, since the expression needs to be converted into the underlying data storage language (T-SQL in this case). I was simply hoping that they would provide a more object-oriented way of translating the comparison I hoped to express by some other means. E.g., overriding Object.Equals() or the == operator, implementing a certain interface on my entities, something that felt OO to me.

Today however, I found my answer. Embarrassingly enough, it was in the MSDN docs all along.

A comparison expression checks whether a constant value, property value, or method result is equal, not equal, greater than, or less than another value. If a particular comparison is not valid for LINQ to Entities, an exception will be thrown. [Specifically, the exception I wrote above.] All comparisons, both implicit and explicit, require that all components are comparable in the data store. Comparison expressions are often used in Where clauses for restricting the query results.

And here it is, the confirmation I wanted (albeit not the answer I was hoping for):

LINQ to Entities does not support using a user class as a constant. However, a property reference on a user class is considered a constant, and will be converted to a command tree constant expression and executed on the data store.

Oh, another thing worth mentioning, method returns don't count as so-called constant expressions, and will thrown an exception when attempted.

public class MyBusinessObject
    {
        public int GetId()
        {
            return 5;
        }
    }

    public class Test
    {
        public Test()
        {
            using (TorvusEntities context = new TorvusEntities())
            {
                MyBusinessObject myBo = new MyBusinessObject();
                var accounts = from a in context.Accounts
                               where a.AccountId == myBo.GetId()
                               select a;

                // Exception will be thrown at run-time,
                // methods do not count as Constant expressions
            }
        }
    }

Today I began playing with the ADO.NET Entity Framework. This framework looks very promising and I plan on experimenting with it heavily this weekend, so expect some more ADOe articles over the next few weeks.

[Update: I discovered more about this subject while reading in-depth the MSDN articles on the ADO.NET Entity Framework. I have updated my conclusions at Entity Framework Comparison Frustration: Explained]

Without going into too much detail, ADOe is very similar to LINQ to SQL. It is essentially a very advanced OR/M that is built into the .NET framework and integrated heavily with Visual Studio designer support, complete with a few snap-in panes for visually mapping your database schema to your CLR objects. I found this post on MSDN explaining the major differences between LINQ to SQL and the ADO.NET Entity Framework.

Later I will go into much more detail about ADOe in general, but for now I wanted to bring up a problem I ran into tonight. My database schema is simple, I have a table called Teams, and a table called Accounts. Each Team has an OwnerId which relates to the AccountId of the Account table -- every Team must have an owner. Lets try and get all Teams for a specific Account...

TorvusEntities entities = new TorvusEntities();

// Pull my Account Entity from the database
Account matt = entities.Accounts.First(a => a.AccountId == 10);

// Attempt to get all Teams by a Team Owner
var teams = from t in entities.Teams
            where t.Owner == matt
            select t;

Looks pretty simple right? The driving force behind ADOe is of course to abstract away data access, so that any .NET developer can read and understand the query intention even if he had never seen T-SQL before in his life. Well, apparently my query isn't acceptable. I am getting an exception:

Unable to create a constant value of type 'Closure type'. Only primitive types (for instance Int32, String and Guid) are supported in this context.

Hmm, ok that's unfortunate. Google is turning up nothing interesting. Apparently the Entity framework can only query primitive types. So naturally I tried to compare the AccountId property instead of a direct instance-to-instance comparison -- something that, IMHO, looks more like T-SQL than C#.

// This works, by comparing the AccountId (int) column
var teams = from t in entities.Teams
             where t.Owner.AccountId == matt.AccountId
             select t;

So I started thinking, maybe ADOe just needs to know how to compare two instances, let's override some operators

public partial class Account
 {
     public static bool operator ==(Account a, Account b)
     {
         return a.AccountId == b.AccountId;
     }
 }

Damn, same exception. Well I'm going to admit that this could very possibly be an error on my part, after all it's 2 AM and I only installed ADOe Beta 3 a few hours ago... I'll update if I discover anything.