CODEWITHSUNDEEP
c#.netentity-frameworkentity-framework-5
Naor
Naor

Reputation: 24053

x.ToString() is not supported by the entity framework!

Entity framework doesn't recognize my ToString method that converts the route object to specifix 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 entity:

 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 the x.ToString() throws an exception of not supported in linq to entities.. Any workaround?

Upvotes: 4

Views: 5774

Answers (7)

Javier Parra
Javier Parra

Reputation: 3

This is tricky solution you can concat like this: key its an string property and key its an int?, its not the best performance solution but it works

    .Select(x => new Balance {
 Amount = x.Gem_Amount ?? 0,
 Balance = balance,
 Key = "" +(int)x.Key
 }).ToList();

Upvotes: 0

Stephan Bauer
Stephan Bauer

Reputation: 9249

ToString() is supported by newer versions of Entity Framework (from version 6.1 onwards)

See Release Notes of EF 6.1:

What’s in EF6.1

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

  • Support for .ToString, String.Concat and enum HasFlags in LINQ Queries.

(Well, the question is already a few years old, but maybe this information might help others... See also related question How to use ToString SelectListItem with Entity Framework?)

Upvotes: 2

acarlon
acarlon

Reputation: 17264

This post and this post might provide some help. Note that the suggested approach in the second link will not work for LINQ to 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 );
}

Upvotes: 0

Amy B
Amy B

Reputation: 110071

Entity framework doesn't recognize my ToString method that converts the route object to specifix string.

That's right. Entity Framework can't convert the methods you write in c# into sql.

Entity Framework converts expressions to sql. Sometimes those expressions represent calls to methods (such as Queryable.Where) and Entity Framework knows about those specific .net framework methods and how to translate them into sql.

How do you expect the database to new up a StringBuilder?

PS: this or'ing of criteria in the query is a terrible way to go. You shouldn't construct one query to rule them all. Instead, conditionally construct the 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));
}

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

You cannot do this on client side (ToString).

You must create SQL function which will do your evaluation - it can either do only string concatenation of you locations (I believe it is related entity) or you can do the whole test in custom function. Then you can import that function into your entity model (EDMX) and create EdmFunctionAttribute to call it - MSDN.

Upvotes: 2

Homam
Homam

Reputation: 23841

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

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

Because I'm asuuming that x demonstrate an entity, so, how do you want to compare the entiy's name itself with a search patternn ?

Edit:

After seeing the changes in the question, it's still unable to use ToString() method in this query because it's not able to convert to a SQL Statement.

Now, you have two options:

The first one: (I'm not sure if applicable depend on the suspected data size), try to load all the records from the database using ToList() extension before calling Where extension. This should work well but it might cause a problem with huge tables.

The second one: You have to create a stored procedure and move the logic to the database.

Upvotes: 1

Alex R.
Alex R.

Reputation: 4754

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

Upvotes: 1

Related Questions