Dapper Binding failing with Dynamic Parameters
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
This issue I have also posted here: https://stackoverflow.com/questions/77902278/why-does-oracle-sql-dapper-not-recognize-these-numbers
Writing a custom Oracle SQL query in .NET using Dapper for dynamic parameters. The field that is giving me an issue is a number(6, 0) field that represents a date field in JD Edwards (JDE Julian Date). In my C# application I have a utility function that converts a date time to JDE Julian Date. Here is the Where clause of the query:
WHERE WR.WLOPST IN :OpStatus AND
WO.WASRST IN :WoStatus AND
TRIM(WL.MCRP01) IN :Plants AND WC.IWMCUW IN :Depts AND WR.WLMCU IN :WorkCenters
AND WR.WLDRQJ >= :DateStart AND WR.WLDRQJ <= :DateEnd AND WT.MEY55OHOLD IN :Holds
AND LENGTH(REPLACE(WR.WLAPID, ' ', ' ')) >= :AssetAssigned AND WR.WLDOCO >= :WorkOrderLow AND WR.WLDOCO <= :WorkOrderHigh
AND WO.WAVR01 >= :PESNumberLow AND WO.WAVR01 <= :PESNumberHigh AND RT.P1Y55OP IN :OpCodes
ORDER BY WR.WLDOCO
I've narrowed down the issue in the where clause to WR.WLDRQJ <= :DateEnd. What is weird is when the date is 02/15/2024 (124046) this query runs fine. When I choose any time past that like 02/16/2024 (124047) I get ORA-01722: invalid number. I validate every datetime value to its Julian Value to make sure the conversion is correct. Furthermore, I can run this in SQL Developer just fine which leads me to believe this may be a dapper issue with dynamic parameters. When debugging I can see what is being assigned to the parameter:
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No repository file or test is named. Start by reproducing the Oracle query with Dapper dynamic parameters, focusing on WR.WLDRQJ <= :DateEnd and the 124046/124047 values, then compare the bound parameter details with the SQL Developer execution. Done means identifying whether Dapper parameter binding causes ORA-01722 and documenting a minimal reproducible case or confirmed non-Dapper cause.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100