Entity Framework GroupBy an object or ComplexType

Question

Is it possible to perform a group by on an object?

from item in context.Items
group item by item.MyObject
select ... 

Where Item.MyObject is a simple object such as:

public class MyObject {
    public int SomeValue { get; set; }
    public string SomeName { get; set; }
    public string SomeOtherProperty { get; set; }
}

Obviously I could do the following:

from item in context.Items
group item by new { item.SomeValue, item.SomeName, item.SomeOtherProperty }
select ... 

However when grouping by objects with a lot of properties, this approach is tedious and error prone.

The above code results in a NotSupportedException with the following message: "The key selector type for the call to the 'GroupBy' method is not comparable in the underlying store provider". Overriding the Equals and GetHashcode does not have any effect. I'm guessing the real problem is that the entity framework doesn't know how to express the SQL...?

Answer 1

Pitifully, no, you cannot do that. The ComplexType looks promising, and seems to be a nice "group of columns" -- but the EF team simply designed it for completely other use and tons of features related to using it as "group of columns" is simply missing.

As far as my knowledge spans, the "Obviously I could do the following:" is the only way possible. You just have to either define the grouping on a per sub-column basis, or by a projection to some type that the database will know how to compare.

The core problem is that the EF team sees the ComplexType not as a group of columns that allow you to tidy up your entities with many fields, but, for them, it is another type of entity, kind of a transient one. The ComplexType is is meant to represent objects that are returned for example from a stored procedure (or table-returning functions) that returns a result set of 5 columns that cannot be mapped to any normal existing table/class. For example, imagine you have a table/class Customer that contains 50 columns/fields, most of them not null and representing some sensitive data. You create a stored procedure that takes an ID of the customer and returns its {name, address, contact phone, shoe size}. Obviously, having such trimmed results set, you cannot decode/map it to the Customer class that has tons of fields that cannot be null. So how do you represent th result set in EF?

First option, quite lame one, is to simply ignore EF and talk to the database directly and read/translate/unpack the rows/columns by hand. Well, we know SqlClient, we can do that.

Second option, ugly one, is to define in EF a stub table/view, for example CustomerView, that would contain just that {name, address, contact phone, shoe size} columns, and map the stored procedure result set to it. It would work nicely, but if you by accident 'generate database' form the EF model, you will get that extra unused table also..

Third option to the rescue are the ComplexType. Instead of trying to make a shadow table or view, you just tell the EF that "there is a some extra data type" that will never be mapped to its own table, and will never have a PrimaryKey defined, and the EF will for your convenience map it to a class like any other data object. Now, having such type, you may return it from procedures and retrieve it as a nicely typed object, you may pass it as procedure's parameter, etc. But: you cannot persist it on its own. It has no own table mapping, and no own primary keys. It has no identity.

It is great, but well, why did you want to have such data type returned from a procedure? Probably, the procedure processed or generated some data, and you would like to store them in some table. You have not defined a whole normal entity, so that thing is just a 'data pack', so you will probably be writing that result value to that table along with other data. And this is why the EF allows you to map the ComplexType to some columns of a table: if you got that temporary result object from a procedure and now you want to write it somewhere, you dont have to do it manually column-by-column, you just map it to columns "name, address, contact phone, shoe size" of the Customer.

The most important point is, that the database server never knows such ComplexType existed. As I said, it had no identity. When you try to do a LINQ query like

aset.Where(item => item.complexProperty == complexValue)

then, the complexValue is a CLR object that is NOT mapped to any table, has NO identity, thus NO PK, thus EF has completely NO IDEA how to check whether the "left hand object" is the same as "right hand object". Had the objects some identity defined, it could check PK on the server side, or do a object-reference comparison at the client side, but here - it simply fails.

I totally agree that this is a yet another damn useful feature in EF that is missing. I believe it would be hyper easy for the EF team to compare the ComplexTypes on column-by-column basis, but they did not. They simply did not thought that ComplexTypes can be used for tidying up the tables. They have meant it to be a transient data pack to be passed to- and from- stored procedures and functions.. Pity.