1

In a recent EF Code First project we were attempting to optimize some Linq queries using different techniques (don't worry its not premature). One common way to optimize a linq query is to convert more of the expression from Linq-to-Objects to almost all Linq-to-Entities side, which is generally faster than mixing Linq-to-Objects and Linq-to-Entities with lazy loading.

I've read how to create linq expressions for most queries that are translatable to Linq-To-Entities, but I'm not sure how to do this with the object initializer syntax.

Take this example:

return results.Select(x => new { Name = x.FullName });

From the falling example Person class: 

public class Person
{
        public string FullName
        {
            get { return FirstName + " " + LastName; }
        }

        public string FirstName { get; set; }

        public string LastName { get; set; }

}

Now I can make the first expression into a Linq-to-Entity friendly expression by converting it to: 

return results.Select(x => new { Name = x.FirstName + " " + x.LastName});

But this kind of sucks bad because I'm duplicating the logic for the FullName. Now you can say this doesn't matter for such a trivial example, but its not hard to imagine a case with a much more complex read-only property.

Anyway I'm trying to figure out if there is a way to do something like this:

return results.Select(x => new { Name = Person.FullNameExpression(x) });

Can anyone tell me if something like this is possible in Linq-to-entities, without using Linq-to-objects?

If this isn't possible, what's the closest I can get to preventing from repeating the logic for readonly properties on my entites?

jennyfofenny
  • 4,359
  • 2
  • 17
  • 15
Mark Rogers
  • 96,497
  • 18
  • 85
  • 138

2 Answers2

1

Anyway I'm trying to figure out if there is a way to do something like this:

Easy way out:

You simply can't. If you want the logic to only exist in the single location, then it can only run in a single location. As a read-only property on a .Net class means it can only run as a local object. If you don't want that logic there, then you have to send it to the (sql) server.

The Road less traveled:

I believe you could technically create an expression that could run on either a local Person or a server side anonymous type, but I personally think that maybe overkill and not exactly easily maintainable code unless you're familiar with expression trees.

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
  • Thanks for the interest, yes, I'm looking to see if there is some form of expression solution, not a local object solution. – Mark Rogers Apr 14 '15 at 05:19
  • Probably want to start around [Reading properties of an object with expression trees](http://stackoverflow.com/questions/16436323/reading-properties-of-an-object-with-expression-trees). – Erik Philips Apr 14 '15 at 05:20
  • 1
    I'm not sure if the linked to solution is necessarily linq-to-entties friendly with the object initializer syntax, but I'll give it a try later today. – Mark Rogers Apr 14 '15 at 12:28
  • The idea would be have an Expression (encapsulated) that can be applied to any object and return FullName. So in your EF if you returned an anonymous object with `FirstName` and `LastName` you then apply to the end of that lambda expression your `FullName` expression. I'm actually learning Expressions over the next few weeks, I'll try this first. Might be a while before I have any concrete conclusion. – Erik Philips Apr 14 '15 at 17:34
0

While using the 'database first' approach, I usually place the calculated/readonly properties in a partial class. These calculated fields/properties are simply not mapped, since they do not exist in my database table. So when I fetch persons, which populates first- and lastname, a call to the fullname property on the person class would construct the value on the fly. Exactly like in your example.

I am not too comfortable with code first, but you could perfectly setup readonly properties on your person class too. The only thing you need to do is to tell EF that this property is unmapped, either via the unmapped attribute or via DbModelBuilder.Ignore.

Then there is no need duplicate the fullname logic. After fetching persons from the database, fullname would be available via accessing the property.

results.Select(person => person.Fullname)

souplex
  • 981
  • 6
  • 16
  • Thanks for the interest, but if I do as you suggest then I the property won't be evaluated in linq-to-entities, but will have to be pulled into linq-to-objects which has worse performance than linq-to-entities, I believe. – Mark Rogers Apr 14 '15 at 05:21
  • I guess it depends. Linq to objects are 'in memory', so I think the performance degradation would be negligible. When you want to filter 'expressions' on the (SQL) Server side, you probably end up with field1 + ' ' + field2 compare statements. Effectively making many indexes unusable by the SQL server. This might hurt performance a lot more. So when filtering server side, you would want to use persisted fields for that reason alone. – souplex Apr 14 '15 at 06:48
  • 1
    I've been measuring performance as I go by using different measures, and its rare that something is faster in linq-to-objects over linq-to-entities. But beyond that I need to convert this particular field to linq to entities because there are other properties I need to retrieve so that I can optimize the rest of the query beyond the full name part. This is simply an example propery. So no, I cant use linq-to-objects. – Mark Rogers Apr 14 '15 at 12:08
  • I understand this being a contrived example. And I am not advocating the use of Linq-to-Objects either. The point was to warn about expression statements might cause your database server to fail to optimize your queries appropriately. – souplex Apr 14 '15 at 12:52