0

I want to compare two values in Entity Framework. One of them is varchar in Database but the content is integer. I've tried some methods( as in Entity Framework/Linq EXpression converting from string to int Convert.ToInt32 not supported in EF6)

.Where(r => Convert.ToInt32(r.foo.xxx)>=yy)

.Where(r => int.Parse(r.foo.xxx)>=yy)

these parsing operations not supported in EF, 

.Where(r => String.Compare(r.foo.xxx,yy)>=0

.Where(r => r.foo.xxx>= SqlFunctions.StringConvert(yy))

and these operation compare two string values(7 >= 58 in string comparison)

Is there anyway to compare these values in EF like Cast in MSSQL.

Thx.

Community
  • 1
  • 1
eozcan
  • 115
  • 2
  • 9
  • 3
    Do you have a reason the database column type to be `varchar` and not some number type? – Vladislav Karamfilov Sep 17 '15 at 08:17
  • we don't have permission to change database and other units are responsible for that database.( Some political issue:)) – eozcan Sep 17 '15 at 08:24
  • 1
    Why then don't map your model property to the one corresponding the DB column type? – kamil-mrzyglod Sep 17 '15 at 08:27
  • Which version of EF do you use? – Vladislav Karamfilov Sep 17 '15 at 08:30
  • @eozcan Are you using code first for mapping or do you have an edmx file ? – Giorgi Sep 17 '15 at 08:32
  • I'm using Code first. – eozcan Sep 17 '15 at 08:35
  • 2
    Fix the database bug. There is no other realistic solution. Using the wrong data type doesn't just make querying difficult, it will seriously hurt performance. How *would* you write a SQL statement to do what you want? If you used `CAST` on the field, you would prevent the database from using any indexes, forcing a full table scan – Panagiotis Kanavos Sep 17 '15 at 09:00
  • The duplicate question provides the answer: you can use `Convert.Int32` on the text field. This will be converted to `CAST ...`, *will* result in awful performance as the dire warnings state. In your original attempt there was no point using `SqlFunctions.StringConvert` - it doesn't matter where the int is converted to a string, lexicographical ordering is used in both cases – Panagiotis Kanavos Sep 17 '15 at 09:10
  • @PanagiotisKanavos have you even tried `Convert.ToInt32` yourself? At least in EF6 with SQL Server here it has not still been supported `Convert.ToInt32`. I've just downvoted 2 answers there involving suggesting to use `Convert.ToInt32`. I believe this will be supported in future versions of EF, we now can just use `Expression.Convert` to build the Expression tree manually. – Hopeless Sep 17 '15 at 10:31
  • @Hopeless no, I always make sure to use the proper types, so I don't have to fix such bugs. If I have to make extensive manipulations or complex queries, I create views and functions, I don't try to pull SQL code into the application. LINQ isn't a substitute for SQL, it's an abstraction over an ORM. You can't use it to fix class, table design or mapping problems – Panagiotis Kanavos Sep 17 '15 at 10:36
  • @PanagiotisKanavos so you should have not said as if `Convert.ToInt32` could be converted to `CAST` (the above comment right above my previous comment). I agree that we should make everything right at database level. But the OP has already said he cannot do so himself (due to some limited access). He should however try proposing this problem to some others having right to fix the database. – Hopeless Sep 17 '15 at 10:39
  • 3
    The answers in duplicated question (as marked) in fact cannot help you (I'm not sure about the non-accepted answer of ***menace*** but that answer is also fairly difficult). I would like to share with you this answer I've written http://pasted.co/8f82ffd5 (I believe it's at least better than pull all data to your computer and use L2O). – Hopeless Sep 17 '15 at 10:58
  • use stored procedure :) – Ahmed Mandour Sep 17 '15 at 11:05
  • @Hopeless Brilliant Solution (Y) – Ahmed Mandour Sep 17 '15 at 11:29
  • @PanagiotisKanavos you are right in case of you have full control over the db design but sometimes you couldn't change database:) – eozcan Sep 17 '15 at 11:54
  • @hopeless that's the solution:D Can you add this comment as a answer. – eozcan Sep 17 '15 at 11:55

2 Answers2

-1

It turns out that EF doesn't support this operation on database level... A sample workaround could be: filter by other properties if you need and make a projection of the DB data with only the columns you need and materialize it with .ToList() method and then you can use int.Parse() or int.TryParse() to convert the string values to integers and compare with another numeric value. Example:

db.Entities
  .Where(x => x.DbFilterableProperty == sampleValue) // This is optional
  .Select(x => new { x.NumberAsString, x.OtherProperty })
  .ToList()
  .Where(x => int.Parse(x.NumberAsString) >= someNumericValue);

The important thing here is the projection (new { x.NumberAsString, x.OtherProperty }) that will give you only the properties you really need, so you don't have more data from the other entity properties in memory than you need.

Vladislav Karamfilov
  • 471
  • 1
  • 9
  • 17
  • That's incorrect. You *can* force a conversion on the field and the OP already posted a (wrong) attempt, it's simply the wrong thing to do. Loading everything in memory to filter is a bad idea, – Panagiotis Kanavos Sep 17 '15 at 09:02
  • And how can we convert the string into integer **on database level**? PS As I said, the solution is not the perfect one but will reduce the amount of data being stored in memory to some level... – Vladislav Karamfilov Sep 17 '15 at 09:10
  • 1
    As the duplicate question shows, `Convert.ToInt32` translates to a `CAST`. The OP could have written `Convert.ToInt32(foo.Property) >= yy`, harming performance just as `CAST` on an indexed field would do – Panagiotis Kanavos Sep 17 '15 at 09:13
  • 1
    `Convert.ToInt32()` doesn't work for me... I'm using EntityFramework 6.1.3 and it throws `System.NotSupportedException` exception trying to execute the method on database level. The best answer to the question that you've given a link to shows a method that will make the query in memory, not on DB level... So basically we end up with the optimization in my answer... – Vladislav Karamfilov Sep 17 '15 at 09:23
  • This won't work for even moderate amounts of data. The linked question contains many options. Another one is to change the property type and mapping to int. Finally, instead of mapping to the table, map to a view that *does* perform a cast. – Panagiotis Kanavos Sep 17 '15 at 09:43
  • And one that *isn't* mentioned - use a stored procedure to perform this specific query and map to it. This will hard-code the returned fields though. Another option is to map to a TVF. In Code-First that will require [an extension package](https://codefirstfunctions.codeplex.com/) but it will allow queries without the restrictions of a stored procedure, ie any number of fields, use in joins etc – Panagiotis Kanavos Sep 17 '15 at 09:47
  • Completely agree with the solution of changing the column type to a numeric type or using a view (if you are allowed). :) As I said my answer shows an improvement in some cases where you don't have really large amount of data... – Vladislav Karamfilov Sep 17 '15 at 09:47
  • Thanks for that library! Seems to be really helpful. :) – Vladislav Karamfilov Sep 17 '15 at 09:50
  • 1
    Thanks @VladislavKaramfilov but as i said before, the data is too large and there is a partiton on that field so TOLIST method is useless before the condition. – eozcan Sep 17 '15 at 12:00
-2

You can use Extension .ToList() before Extension .Where() then try to parse the string column to int like this

  int temp;
  .ToList().Where(r => (int.TryParse(r.foo.xxx, out temp) ? temp : 0) >= yy);

The key is the .ToList() in those two ways. It gets all the string data from the database, so when you call int.TryParse on the results, the database query has already been run, so it is using pure CLR code, and not trying to convert int.TryParse into a SQL query. I made an EF context in one of my Sandbox projects and verified this works.

Ahmed Mandour
  • 330
  • 1
  • 8
  • The data to be listed is too large. So I want to filter the data to be listed in thequery. And there is partition depends on that field. – eozcan Sep 17 '15 at 08:28
  • 5
    `.ToList()` will materialize all entities in the memory and will then work with the collection in memory. For small number of entities (and small number of properties (DB table columns)) this is not a big problem but in many cases you'll end up with a big performance problem. – Vladislav Karamfilov Sep 17 '15 at 08:29