0

The Math.Round function isn't supported by LINQ to entities (I assume that means using LINQ with the entity framework dbset) but I really need to round the double in query to filter lots of rows according to the user input.

User input is 4 digits after point and doubles in db can be any size.

Is there a way to round double in the query?

UPDATE:

Basically user enters some number in filter of the table column. Lets say it's a weight of fruits.

So for example user enters weight as 4.2152 and the column must filter all the fruits whose weight is 4.21515 or 4.215212 etc.

And in the database there are many fruits which weight is like 4.21514543543643

RESULT

So after a day long struggle I decided to use the range condition. Although it's not quite a solution. If user enters 4.2152 then range filters with condition bigger then 4.21515. But it will filter out the 4.215149 value which would otherwise be rounded to 4.2152.

The problem is solved but not exactly as needed :(

ProgBlogger
  • 175
  • 1
  • 6
  • 23
  • 3
    It's unclear to me what you want rounding - could you give more details please? It sounds like you might be able to just specify a range in the query, based on rounded versions of user input, but without more details it's hard to say. – Jon Skeet Dec 18 '19 at 08:19
  • 2
    There is no "Linq to entities", Maybe you talk about Linq to objects. There it is supported. In Linq to SQL it is not. The Math functions are partially included here:https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.sqlserver.sqlfunctions?view=entity-framework-6.2.0 But Round is not included. But Round(x)=9 is easy to replace with (x>=8.5&&x<9.5) – Holger Dec 18 '19 at 08:29
  • @Holger There_is_ linq to entities, and it's the set of Linq extensions methods that act on Entity Framework specific classes (like on `IQueryable` or `DbSet`). If your EF Context is a SQL server, then this will mean that a SQL query will be generated, indeed. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/linq-to-entities, when the Linq query is enumerated. (which matches what you and OP said, of course). – Pac0 Dec 18 '19 at 08:31
  • @Pac0. You are right, It's an abstract intermediate layer. It's just not often used with something different to SQL, that this name is hardly used. – Holger Dec 18 '19 at 09:03

2 Answers2

3

Instead of trying to round the data on your server, try instead to use some boundaries.

For your example, you basically want all the fruits which weight between 4.2152 and 4.2153 .

Now, it will depend on your specific case, (do you always want a precision of 4 decimals ? What is the exact datatype do you use, etc...), so the exact algorithm is up to you.

But it will looks like this :

double lowerBound = userInput; // 4.2152
double precision = 0.0001; 
double upperBound = userInput + precision;

var query = DbSet<Fruit>.Where(f => lowerBound >= f.Weight && f.Weight < upperBound);

Also keep in mind that floating-point arithmetic can surprise you, sometimes. Depending on your usecase, this + 0.0001 might not be exactly what you want.

Pac0
  • 21,465
  • 8
  • 65
  • 74
1

Update answers:

You can try to use Canonical Functions. It's have a lot of functions help you in Math.

Maybe it can help. Document

Old answers:

Try use .AsEnumerable() before Where clause.

It's should works.

mikenlanggio
  • 1,122
  • 1
  • 7
  • 27
  • 1
    AsEnumerable will make the next query executed locally, which means all the database will be loaded, and then some filtering will occur in memory on the Enumerable. I don't think this is advisable. – Pac0 Dec 18 '19 at 08:37
  • Yes. This is the problem. I don't want to load all the data to memory but to filter it in the db – ProgBlogger Dec 18 '19 at 08:38
  • Yup. But you can filter some clause before use it. Like: `where(x=>x.username == "username").AsEnumerable().where(x=>Math.Round(x.number) == myNumber)` If not. I don't think you can do something else – mikenlanggio Dec 18 '19 at 08:41
  • 2
    Update my answers – mikenlanggio Dec 18 '19 at 08:57