linq to entities can not convert int to string
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))
Wael Joulani
Updated on June 05, 2022Comments
-
Wael Joulani almost 2 years
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?
-
Wael Joulani over 10 yearsThanks for your reply but I try this solution before but the same error return
-
Michael Mairegger over 10 yearsStange, 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()
-
Wael Joulani over 10 yearsNo I didn't call ToString() anywhere
-
Michael Mairegger over 10 yearsI 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)
-
Wael Joulani over 10 yearsThank 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
-
Michael Mairegger over 10 yearsOk, now I have seen that you call
ToList
priorWhere
. Therefore you cannot call any SqlFunction. Try executing it directly purchaseOrderRepository.Where(x => SqlFunctions.StringConvert((double?) x.PurchaseOrderID).Contains(text)).ToList()); -
Wael Joulani over 10 yearsThanks 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 over 10 yearsI add the GetMany method which I currently used
-
Michael Mairegger over 10 yearsIs there any possibility that you can call Where to a repository.Set<PurchaseOrder>(), just to see if my proposed solution would work in your case?
-
Wael Joulani over 10 yearsI't not work, it's return a syntax error says that the class does not contain a definition for Where
-
Michael Mairegger over 10 yearsOf what type is purchaseOrderRepository? Is it a DbContext?
-
Michael Mairegger over 10 yearsThen you should be able to call: purchaseOrderRepository.Set<PurchaseOrder>().Where(x => SqlFunctions.StringConvert((double?) x.PurchaseOrderID).Contains(text)
-
Wael Joulani over 10 yearsI 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
-
Michael Mairegger over 10 yearsHave you imported System.Linq?
-
Wael Joulani over 10 yearsYes for sure, I add the base class to my original post
-
Wael Joulani over 10 yearsSorry my bad, it's recognized it but it's raise syntax error when use it
-
Michael Mairegger over 10 yearsNow I am a little bit confused. Intellisense suggests the method where, but by using it you get a syntax error?
-
Wael Joulani over 10 yearsThat what confusing me also, When I user DBContext<T>.Where() it's work, but when I user DBContext<PurchaseOrder>.Where() it's return an error also same error returns when I use DBContext<T>.ToList().Where()
-
Michael Mairegger over 10 yearsNo, you should use DataContext.Set<PurchaseOrder>().Where(...)
-
Wael Joulani over 10 yearsSorry that what I mean DbContext.set<Class>.Where not DBContext<class>
-
Wael Joulani over 10 yearsSystem.Data.Entity.DBSet<PurchaseOrder> does not contain a definition for Where and the best extension method overload System.Liqe.Enumerable.Where<TSource>(System.Collection.Generic.IEnumerable<TSource,int,bool>) has some invalid arguments
-
Michael Mairegger over 10 yearsThat is very, very strange. Than lets at least try DataContext.PurchaseOrder.Where(...)
-
Michael Mairegger over 10 yearsThat 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 over 10 yearsStrange, I will try this tonight and let you know whether I have any strange problems.
-
Wael Joulani over 10 yearsThank you, I appreciate your help :)
-
Michael Mairegger over 10 yearsI have tried it, and the above code did not raise any syntax Errors (surely I have modified the type and property to my Needs ;) )
-
Wael Joulani over 10 yearsThank you, I'll double check my code again and check what is wrong with it