linq to entities can not convert int to string

Question

I try to filtering the data that return to kendo combo box, the filtering is based on the ID, I need to return all records that contains the filtration text not only the equals one, so what I did is to cast the ID to string as the following snip

Items = Mapper.Map<List<PurchaseOrder>, List<PurchaseOrderViewModel>>(
                purchaseOrderRepository.GetMany(x => 
                                                x.PurchaseOrderID
                                                 .ToString()
                                                 .Contains(text))
                                                 .ToList());

but it's always return linq to entities does not recognize the method 'system.string tostring()'

so I tried to convert the dbset to list before the where statement as I found in another post LINQ to Entities does not recognize the method 'System.String ToString()' method in MVC 4 but I got another error says that the list does not contains a definition for Where (dbSet is an instance of IDbSet)

public virtual IList<T> GetMany(Expression<Func<T, bool>> where)
{

    return dbset.ToList().Where(where).ToList();
}

here is My original(current) get method

sing System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data;
using System.Linq.Expressions;
using Spine.ERP.DataAccess.DbFirstDataAccess;
using Spine.ERP.DataModel.Helpers;
namespace Spine.ERP.DataAccess.Infrastructure
{
    public abstract class RepositoryBase<T> where T : class
    {

        private SSSDBEntities dataContext;

        private readonly IDbSet<T> dbset;

        protected RepositoryBase(IDatabaseFactory databaseFactory)
        {
            DatabaseFactory = databaseFactory;
            dbset = DataContext.Set<T>();
        }

       protected IDatabaseFactory DatabaseFactory
        {
            get;
            private set;
        }


        protected SSSDBEntities DataContext
        {
            get { return dataContext ?? (dataContext = DatabaseFactory.Get()); }
        }
    public virtual IQueryable<T> GetMany(Expression<Func<T, bool>> where)
        {
            return dbset.Where(where);
        }
  }
}

Any other thing can I do to solve this problem?

Michael Mairegger · Accepted Answer · 2013-11-12T09:21:41.980

10

There is a SqlFunctions class available that contains many SQL-functions that may be used for LINQ. Try a look at SqlFunctions.StringConvert

Items = Mapper.Map<List<PurchaseOrder>, List<PurchaseOrderViewModel>>(
                purchaseOrderRepository
                   .GetMany(x => SqlFunctions
                                  .StringConvert((double?) x.PurchaseOrderID)
                                  .Contains(text))
                                  .ToList());

Unfortunately there is only a SqlFunctions.StringConvert(double? number) and no SqlFunctions.StringConvert(int? number). But I always convert the integer number to a double and it works as expected.

Edit: You are calling ToList prior Where. Therefore that SqlFunctions can only be called in a LINQ to Entity query any SqlFunctions after a ToList() is illegal.

Try without your GetMany Method:

purchaseOrderRepository.Set<PurchaseOrder>()
   .Where(x => SqlFunctions
       .StringConvert((double?) x.PurchaseOrderID)
       .Contains(text))

edited Nov 12 '13 at 09:21

answered Nov 11 '13 at 09:27

Michael Mairegger

6,833
28
41

Thanks for your reply but I try this solution before but the same error return – Wael Joulani Nov 11 '13 at 12:11
Stange, it should work. I have never had a problem with that. I cannot see the reason why with this code the same error should arise. Are you somewhere calling `ToString()` – Michael Mairegger Nov 11 '13 at 13:47
No I didn't call ToString() anywhere – Wael Joulani Nov 12 '13 at 04:58
I cannot understand why you get exactly the same error. At least it should be different. (I have edited the above code: not the cast ist to double? instead of double; maybe thats the problem) – Michael Mairegger Nov 12 '13 at 07:25
Thank for your help, but it's still raise the same error maybe it should convert to List first then filtering, but I can not do that because it's always return that the where not found – Wael Joulani Nov 12 '13 at 08:02
Ok, now I have seen that you call `ToList` prior `Where`. Therefore you cannot call any SqlFunction. Try executing it directly purchaseOrderRepository.Where(x => SqlFunctions.StringConvert((double?) x.PurchaseOrderID).Contains(text)).ToList()); – Michael Mairegger Nov 12 '13 at 08:24
Thanks again, get many is the method which will get the data from the data base and the repository is just and interface to allow me using this method, so i can't call Where from the repository direct. – Wael Joulani Nov 12 '13 at 08:41
I add the GetMany method which I currently used – Wael Joulani Nov 12 '13 at 08:42
Is there any possibility that you can call Where to a repository.Set(), just to see if my proposed solution would work in your case? – Michael Mairegger Nov 12 '13 at 08:43
I't not work, it's return a syntax error says that the class does not contain a definition for Where – Wael Joulani Nov 12 '13 at 09:03
Of what type is purchaseOrderRepository? Is it a DbContext? – Michael Mairegger Nov 12 '13 at 09:11
Then you should be able to call: purchaseOrderRepository.Set().Where(x => SqlFunctions.StringConvert((double?) x.PurchaseOrderID).Contains(text) – Michael Mairegger Nov 12 '13 at 09:21
I tired it as I say before but its not recognized the Where statement it's raise syntax error says that its contain a definition for Where – Wael Joulani Nov 12 '13 at 09:26
Have you imported System.Linq? – Michael Mairegger Nov 12 '13 at 09:31
Yes for sure, I add the base class to my original post – Wael Joulani Nov 12 '13 at 09:37
Sorry my bad, it's recognized it but it's raise syntax error when use it – Wael Joulani Nov 12 '13 at 09:39
Now I am a little bit confused. Intellisense suggests the method where, but by using it you get a syntax error? – Michael Mairegger Nov 12 '13 at 09:53
That what confusing me also, When I user DBContext.Where() it's work, but when I user DBContext.Where() it's return an error also same error returns when I use DBContext.ToList().Where() – Wael Joulani Nov 12 '13 at 09:57
No, you should use DataContext.Set().Where(...) – Michael Mairegger Nov 12 '13 at 10:11
Sorry that what I mean DbContext.set.Where not DBContext – Wael Joulani Nov 12 '13 at 10:17
System.Data.Entity.DBSet does not contain a definition for Where and the best extension method overload System.Liqe.Enumerable.Where(System.Collection.Generic.IEnumerable) has some invalid arguments – Wael Joulani Nov 12 '13 at 10:25
That is very, very strange. Than lets at least try DataContext.PurchaseOrder.Where(...) – Michael Mairegger Nov 12 '13 at 11:25
That seems impossible for me. I do the same all the time. Normally this error arises if System.Linq is not imported. You have importe this reference also in the sub-class right? – Michael Mairegger Nov 12 '13 at 11:31
Strange, I will try this tonight and let you know whether I have any strange problems. – Michael Mairegger Nov 12 '13 at 11:57
I have tried it, and the above code did not raise any syntax Errors (surely I have modified the type and property to my Needs ;) ) – Michael Mairegger Nov 12 '13 at 16:58
Thank you, I'll double check my code again and check what is wrong with it – Wael Joulani Nov 13 '13 at 05:37

linq to entities can not convert int to string

1 Answers1

Linked