CODEWITHSUNDEEP
c#entity-framework
albicelestial
albicelestial

Reputation: 145

Ordering by custom entity properties in Entity Framework

Just another question about custom extended properties in Entity Framework entities.

Entity model is quite simple in general. Of course in reality it is more complicated, but just for simplyfing I am not pasting all the generated code, just classes and needed properties. So, I have the entity classes:

partial class Calibration
{
   public string Identifier {get;set;}
   public Device CalibratedDevice {get;set;}
}

partial class Device
{
   public string Name {get;set;}
   public ModelGroup ModelGroup {get;set;}
}

partial class ModelGroup
{
   public Model[] Models {get;set;}
}

partial class Model 
{
   public Name {get;set;}
}

And I need to extend Calibration class with additional calculated property in another file.

partial class Calibration
{
   public string ModelGroupName {get;set;}
}

This property is calculated like this:

string.Join(" / ", CalibratedDevice.ModelGroup.Models.Select(m => m.Name));

And finally I need to sort ObjectSet of Calibration entities by this calculated property. Of course, code like

Calibrations.OrderBy(c => c.ModelGroupName)

will not work with throwing an exception, because EF cannot translate ModelGroupName property to database. Of course, I know the easiest way to do it: 

Calibrations.AsEnumerable().OrderBy(c => c.ModelGroupName)

And of course, it doesn't works for me, I don't want to load all the objects from data storage and sort them in linq because I need only a small piece of the whole ordered object set.

I know the approach with storing calculation lambdas instead of properties and passing it to OrderBy method, but it doesn't works either because I have more complex calculation logic than simple a + b. For example 

partial class Calibration
    {
        public static Expression<Func<Calibration, string>> ModelGroupName
        {
            get
            {
                return c => string.Join(" / ", c.CalibratedDevice.ModelGroup.Models.Select(m => m.Name));
            }
        }
}

Calibrations.OrderBy(Calibration.ModelGroupName)

will throw an Exception because EF cannot thanslate string.Join method to database. I worked with the first version of EF and this annoying method-translation mechanism was a disaster. And now after few years of EF evolution this problem exists and I can found any suitable solution.

So, please, suggest the ways to organize IQueryable EF sorting by custom properties with calculation logic witch is not directly translated to SQL. Thanks for replies.

Upvotes: 2

Views: 2125

Answers (2)

Nick Butler
Nick Butler

Reputation: 24383

EF converts IQueryable objects into SQL statements that run on a db. You're asking if EF can translate arbitrary C# code into SQL - no, it can't.

It might be possible to construct a query that returns the right result set, which your custom properties can use - it depends on the logic.

Upvotes: 1

Svarog
Svarog

Reputation: 2208

You can map SQL functions to CLR functions in Entity Framework.
Here's a tutorial on how it is done:
http://blogs.microsoft.co.il/blogs/gilf/archive/2009/05/28/entity-sql-user-defined-functions-in-entity-framework-4.aspx

Please let me know if you need further help.

Upvotes: 1

Related Questions