search object list by computed property

Question

Here is my class:

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

Here is my list:

var persons = new List<Person>();
persons.Add(...);
persons.Add(...);
etc.

If I try and search the list via the "calculated" property FullName as follows:

return persons.Where(p => p.FullName.Contains("blah"))

I get the following error:

System.NotSupportedException: The specified type member 'FullName' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

How can I get around this?

If that's an EF mapping, then you should write the FullName as a function instead of a property so EF knows it can't use it. — Michael Dunlap, Nov 30 '12 at 19:47
I don't think that this is possible to LINQ 2 Entities to figured out and translated this predicated to appropriate SQL. Try to use non-computed property instead. — Sergey Teplyakov, Nov 30 '12 at 19:56
possible duplicate of [Only initializers, entity members, and entity navigation properties are supported](http://stackoverflow.com/questions/6919709/only-initializers-entity-members-and-entity-navigation-properties-are-supporte) — Jon B, Nov 30 '12 at 19:58

score 2 · Answer 1 · answered Nov 30 '12 at 20:02

2

You can't use computed properties in the IQueriable predicates. There is two ways to achieve this.

You can create a view in the database and create calculated FullName column there and map this column back to your data entity.

Second, you can retrieve all data back to the client and filter them in memory:

persons
  .ToList() // Execute this query and get results
  .Where(p => p.FullName.Contains("blah");

answered Nov 30 '12 at 20:02

Sergey Teplyakov

11,477
34
49

Yes, you can do both, however 1) It's not exactly ideal to have to resort to creating views, especially if you are using EF and 2) Returning the entire table and then filtering is extremely inefficient. I was hoping for a silver bullet here... – user1733939 Nov 30 '12 at 20:15
1

Unfortunately there is no any way to achieve this even in theory: LINQ provider uses Expression Tree to transform your predicated into appropriate SQL queries, but there is no way to transform property getter body into the SQL statements. – Sergey Teplyakov Nov 30 '12 at 20:28
Yeah, coming to that unfortunate realization. Pity. Thanks Sergey. – user1733939 Nov 30 '12 at 20:32

Jon B · Accepted Answer · 2012-11-30T20:36:34.493

1

In your case, you can simply change your Where to use both parts of your computed property:

return persons.Where(p => (p.FirstName + " " + p.LastName).Contains("blah"))

edited Nov 30 '12 at 20:36

answered Nov 30 '12 at 20:01

Jon B

51,025
31
133
161

Unfortunately this will not work if you are searching for the whole name in this instance. For example, searching for "Bob Jones" will not get what you are looking for even though the FirstName and LastName properties are "Bob" and "Jones" respectively. – user1733939 Nov 30 '12 at 20:11
@user1733939 Can you do `p => (p.FirstName + " " + p.LastName).Contains("blah")`? (I'm not sure if that works, since EF is pretty fussy) – Jon B Nov 30 '12 at 20:12
That did it. Nice one Jon B! – user1733939 Nov 30 '12 at 20:34

score 0 · Answer 3 · answered Jan 21 '14 at 17:18

Try this nuget package I created: https://www.nuget.org/packages/NinjaNye.SearchExtensions/

Once installed, you will be able to do the following

people.Search("blah", p => p.FirstName, p => p.LastName);

The above returns records where "blah" exists in either FirstName OR LastName. All of this is done using Expression Trees so searching is done in SQL not in memory

Checkout the project page for other example usages: https://github.com/ninjanye/SearchExtensions

search object list by computed property

3 Answers3