[CSHARP-2472] Casting to double not supported in LINQ Select expression Created: 08/Jan/19  Updated: 08/Feb/23  Resolved: 03/Nov/22

Status: Closed
Project: C# Driver
Component/s: Linq
Affects Version/s: None
Fix Version/s: 2.19.0

Type: New Feature Priority: Major - P3
Reporter: David Craveiro Assignee: Robert Stam
Resolution: Done Votes: 0
Labels: rfw
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File query.txt    
Issue Links:
Depends
depends on CSHARP-4397 Translate conversions to short form l... Closed

 Description   

I'm using decimals to store my data on mongoDB, because I need to have as much precision as I can.

To use $sqrt on mongoDB you need to pass a double as argument, so I need to cast my decimal to double and then pass it to $sqrt.
Using mongoDB shell it's an easy job, but when you try to code this using mongoDB c# driver with IQueryable interface things getting complicated.

My code:

var data = _myRepository.AsQueryable()
   .GetValues(equipmentId, startDate, endDate)
   .GroupBy(g => g.timestamp)
   .Select(p => new MyDTO {
      timestamp = p.Key,
      sqrt_calc = (decimal)Math.Sqrt(
         (double)p.Sum(x => x.my_decimal_value)
      )
   .OrderBy(q => q.timestamp);

I'm getting the following exception: OverflowException: Arithmetic operation resulted in an overflow.

IQueryable is producing the query into query.txt attached.query.txt

As you can see there is a missing $toDouble after $sqrt, so $sqrt is receiving a decimal and produces a NaN which I believe that is causing the exception.

Is there a way to tell IQueryable to add the $toDouble cast?



 Comments   
Comment by Githook User [ 07/Nov/22 ]

Author:

{'name': 'rstam', 'email': 'robert@robertstam.org', 'username': 'rstam'}

Message: CSHARP-2472: Skip integration test on older servers.
Branch: master
https://github.com/mongodb/mongo-csharp-driver/commit/b3cf5d8a234c2b9625459624452e66c3d13255af

Comment by Githook User [ 03/Nov/22 ]

Author:

{'name': 'rstam', 'email': 'robert@robertstam.org', 'username': 'rstam'}

Message: CSHARP-2472: Get sample from ticket to pass.
Branch: master
https://github.com/mongodb/mongo-csharp-driver/commit/502a4273371980b1b350ce3260111a2b11c2a56c

Comment by Robert Stam [ 03/Nov/22 ]

Thanks for the excellent repro, that  made it much easier to work on this!

I got this working, but I had to make a very small change to your repro code:

var queryable = collection
    .AsQueryable()
    .Where(
        q => q.equipment_id == equipmentId
        && q.timestamp >= startDate
        && q.timestamp <= endDate
    )
    .GroupBy(g => g.timestamp)
    .Select(p => new MyDTO
    {
        timestamp = p.Key,
        sqrt_calc = (decimal)Math.Sqrt(
            p.Sum(x => (double)x.my_decimal_value)
        )
    })
    .OrderBy(q => q.timestamp);

 The change is that:

sqrt_calc = (decimal)Math.Sqrt(
    (double)p.Sum(x => x.my_decimal_value)
)

becomes:

sqrt_calc = (decimal)Math.Sqrt(
    p.Sum(x => (double)x.my_decimal_value)
)

The cast to `double` had been moved to inside the call to `Sum`.

This change is necessary because `decimal` is represented by default as a string, and we need to convert those strings to doubles before we can sum them.

The generated pipeline is:

"{ $match : { equipment_id : 1, timestamp : { $gte : ISODate('2022-01-01T00:00:00Z'), $lte : ISODate('2022-01-02T00:00:00Z') } } }",
"{ $group : { _id : '$timestamp', __agg0 : { $sum : { $toDouble : '$my_decimal_value' } } } }",
"{ $project : { timestamp : '$_id', sqrt_calc : { $toDecimal : { $sqrt : '$__agg0' } }, _id : 0 } }",
"{ $sort : { timestamp : 1 } }");

Comment by David Craveiro [ 12/Feb/19 ]

GetValues is an Expression to match documents. After replacing it this is the code I have:

var data = _myRepository.AsQueryable()
     .Where(
          q => q.equipment_id == equipmentId
          && q.timestamp >= startDate
          && q.timestamp <= endDate
     )
     .GroupBy( g => g.timestamp )
     .Select( p => new MyDTO 
     {
          timestamp = p.Key,
          sqrt_calc = (decimal)Math.Sqrt(
               (double)p.Sum(x => x.my_decimal_value)
          )
     })
     .OrderBy( q => q.timestamp ); 

 

Comment by Robert Stam [ 11/Feb/19 ]

I've changed the Summary of the ticket to reflect that it is the cast to double that is not supported.

To help in reproducing this in the future, can you please explain what this line is:

.GetValues(equipmentId, startDate, endDate)

it doesn't look like standard LINQ.

 

Comment by Wan Bachtiar [ 11/Feb/19 ]

Is there a way to tell IQueryable to add the $toDouble cast?

Hi,

Unfortunately $toDouble aggregation operator as LINQ is not currently supported.
As an alternative, you could construct the projection manually. For example:

var docs = collection.Aggregate()
                    .Match(x => x.timestamp >= startDate && 
                                x.timestamp <= endDate && 
                                x.equipment_id == 168)
                    .Group(y=>y.timestamp,
                        z => new { 
                            timestamp = z.Key, 
                            sqrt_calc = z.Sum(a => a.my_decimal_value)
                            }
                    )
                    .Project(new BsonDocument{
                        {"_id", 0},
                        {"timestamp", "$_id"},
                        {"sqrt_calc", new BsonDocument{
                                {"$sqrt", new BsonDocument("$toDouble", "$sqrt_calc")}
                            }
                        }
                    })
                    .Sort(Builders<BsonDocument>.Sort.Ascending("timestamp"))

Regards,
Wan.

Generated at Wed Feb 07 21:42:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.