X.ToString () is not supported by the entity infrastructure!

Question

Entity framework does not recognize my ToString method, which converts a route object to a specification string:

public IEnumerable<Route> GetRoutes(string prefix, string searchPattern, string code) { return Routes. Where(x => code == null || x.Code == code). Where(x => searchPattern == null || x.ToString().Contains(searchPattern)). Where(x => prefix == null || x.ToString().StartsWith(prefix)); } 

Here is my Route object:

  public class Route : IDataEntity, ISoftDeletable { public virtual long Id { get; set; } public virtual string Code { get; set; } public virtual bool IsDeleted { get; set; } public virtual Guid CompanyId { get; set; } public virtual IList<LocationInRoute> Locations { get; set; } public override string ToString() { StringBuilder str = new StringBuilder(); foreach (LocationInRoute loc in Locations) { if (str.Length > 0) { str.Append(" > "); } str.Append(loc.ToString()); } return str.ToString(); } } 

All x.ToString() throws an exception that is not supported in linq for entities. Any workaround?

Answer 1

You cannot do this on the client side ( ToString ).

You must create an SQL function that will perform your evaluation — it can either only concatenate the strings of your locations (I believe this is a related entity), or you can run the entire test in a user-defined function. Then you can import this function into your entity model (EDMX) and create an EdmFunctionAttribute to call it - MSDN .

Answer 2

The Entity framework does not recognize my ToString method, which converts a route object to a specification string.

It is right. Entity Framework cannot convert the methods you write in C # to sql.

Entity Framework converts expressions to sql. Sometimes these expressions represent method calls (e.g. Queryable.Where ), and the Entity Framework knows about these specific framework.net methods and how to translate them into sql.

How do you expect a new StringBuilder to appear in the database?

---

PS: is this or there are criteria in the request - this is a terrible way to go. You do not have to create one request to manage them all. Instead, conditionally create a query:

 IQueryable<Route> query = Routes if (code != null) { query = query.Where(x => x.Code == code) } if (searchPattern != null) { query = query.Where(x => x.Locations.Any(loc => loc.Name.Contains(searchPattern))) } if (prefix != null) { query = query.Where(x => x.Locations.First().Name.StartsWith(prefix)); } 

Answer 3

You need to specify which property of the Route class you need to compare with searchPattern or prefix . You cannot implicitly do .ToString() in your script.

Answer 4

I think you should have a preperty called ID or Name , which you want to compare with searchPattern , and then use:

  .Where(x => searchPattern == null || x.Name.Contains(searchPattern)); 

Because I'm trying to understand that x represents an entity, therefore, how do you want to compare the name entiy with the search pattern?

Edit:

After looking at the changes in the question, he still cannot use the ToString() method in this query because he cannot convert to an SQL expression.

You now have two options:

First: (I'm not sure if it depends on the expected size of the data), try loading all records from the database using the ToList() extension before calling the Where extension. This should work well, but it can cause a problem with huge tables.

Second: you need to create a stored procedure and move the logic to the database.

Answer 5

ToString() supported by newer versions of the Entity Framework (since version 6.1)

See release notes in EF 6.1:

What's in EF6.1

EF6.1 adds the following new features:
[...]

• Support .ToString, String.Concat and HasFlags enumeration in LINQ queries.

---

(Well, the question has been around for several years, but perhaps this information may help others ... See also a related question. How to use ToString SelectListItem with Entity Framework? )

Answer 6

This post and this post may be of some help. Please note that the proposed approach in the second link will not work for LINQ for Entities, because it uses ToString. To make it work, replace the CreateLike method with:

 private static Expression<Func<T, bool>> CreateLike<T>( PropertyInfo prop, string value ) { var parameter = Expression.Parameter( typeof( T ) ); Expression instanceExpression = Expression.MakeMemberAccess( parameter, prop ); if( prop.PropertyType != typeof( System.String ) ) { var cast = Expression.Convert( instanceExpression, typeof( double? ) ); MethodInfo toString = typeof( SqlFunctions ).GetMethods().First( m => m.Name == "StringConvert" && m.GetParameters().Length == 1 && m.GetParameters()[0].ParameterType == typeof( double? ) ); instanceExpression = Expression.Call( toString, cast ); } var like = Expression.Call( instanceExpression, "Contains", null, Expression.Constant( value, typeof( string ) ) ); return Expression.Lambda<Func<T, bool>>( like, parameter ); }