Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Json Filter on DateTimeOffset field is returning 0 rows #477

Closed
vsimonaitis opened this issue Apr 16, 2021 · 3 comments · Fixed by #478
Closed

Json Filter on DateTimeOffset field is returning 0 rows #477

vsimonaitis opened this issue Apr 16, 2021 · 3 comments · Fixed by #478
Assignees

Comments

@vsimonaitis
Copy link

When filtering is applied on DateTimeOffset column and target value is passed as a string, filtering works fine.

But when filter is deserialized from Json object (JSON used for WebApi request) NewtonSoft automatically Deserializes dates into DateTime. When DateTime filter is applied on DateTimeOffset, it produces a SQL where condition "@p2=1" where p2 equals 0. In other words it returns 0 rows no matter what.

var proj = new TestData();
                proj.date = DateTime.UtcNow;
                proj.timestamp = DateTimeOffset.UtcNow;
                proj.categoryTitle = "P1";
                session.Save(proj);
                {
                    var loadOptions = new SampleLoadOptions
                    {
                        Filter = new[] { "timestamp", ">", "2020-01-01T18:04:57.610Z" },
                        RequireTotalCount = true
                    };
                    var loadResult = DataSourceLoader.Load(session.Query<TestData>(), loadOptions);

                    Assert.GreaterOrEqual(loadResult.totalCount, 1); // Success
                }
                {
                    var source = new[] { new TestData() { timestamp = DateTimeOffset.UtcNow } };

                    var filter = JsonConvert.DeserializeObject<IList>(@"[ ""timestamp"", "">="", ""2020-03-03T18:04:57.610Z"" ]");

                    var loadOptions = new SampleLoadOptions
                    {
                        Filter = filter,
                        RequireTotalCount = true
                    };

                    var loadResult = DataSourceLoader.Load(source, loadOptions);
                    Assert.GreaterOrEqual(loadResult.totalCount, 1); // Fail
                }

PS: I would prefer to get an exception instead of 0 results.

@AlekseyMartynov
Copy link
Contributor

Thanks for reporting. We will fix this case of DateTime to DateTimeOffset conversion. As a workaround, you can useDateParseHandling = None, refer to JamesNK/Newtonsoft.Json#862

PS: I would prefer to get an exception instead of 0 results.

I agree with you, that this complicates debugging. However, we prefer to mute errors when, for instance, an end user enters text into a numeric column filter.

@vsimonaitis
Copy link
Author

Just a suggestion: Maybe the library could output error in the resultObject in a separate error property? It would not affect DataGrid behavior but would be very useful for developers.

@AlekseyMartynov
Copy link
Contributor

Thanks for the idea. I opened an individual ticket #479 to track it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants