I am trying to maintain a pre-existing piece of software that uses Telerik Data Access for data access and also Telerik Kendo on the frontend. Several Kendo grids in the software represent tables in the database, except in order to be more user-friendly, instead of displaying foreign keys, they display the Display Names of the objects those foreign keys represent.
In order to fill the grid, then, the data call made by the UI might look something like this:
[root]/DataServices/[tenant]/odata/HouseModels?%24format=json&%24top=100&%24count=true&%24expand=DoorStyle(%24select%3DID%2CDisplayName)%2CWindowStyle(%24select%3DID%2CDisplayName)%2CDeckStyle(%24select%3DID%2CDisplayName)%2CFinishStyle(%24select%3DID%2CDisplayName)
This works fine in all ways except one: if a foreign key column is nullable and the user sorts or filters on that column, all rows that have a null value in that column will disappear. (Ironically, this even happens if the user filters specifically to show only null values! They will always see no results!)
I personally don't care whether null values are sorted before or after non-null values, but entire rows should never disappear entirely just from sorting (or in a way that makes zero sense for a particular filter).
[Note: The 'HouseModels' thing above is just an example. My data has nothing to do with houses, so yes, it does make sense to have nullable FKs.]
Using SQL Profiler, I believe I have determined the Data Access behaviour that is causing this problem. When the user tries to apply a sort or filter, the first call received by the database will look something like this if the user sorts the 'Door Style' column:
SELECT TOP(100)
a.[ID] AS COL1,a.[FinishStyleID] AS COL2,a.[DoorStyleID] AS COL3,
a.[WindowStyleID] AS COL4,a.[DeckStyleID] AS COL5,
(CASE WHEN a.[DoorStyleID] IS NULL THEN NULL
ELSE b.[DisplayName] END)
AS xj1
FROM [HouseModels] a
JOIN [User DoorStyles] AS b
ON (a.[DoorStyleID] = b.[ID])
/*permissions-related SQL omitted*/
ORDER BY xj1 DESCFrom what I can tell, the results of this first call determine what rows will be visible in the grid, because the subsequent data calls are to fulfill all of the 'expand' requirements for all the IDs in the result of that first call.
As you can see, this means the problem is in the "JOIN" in the first call. Whether the user is filtering or sorting, that JOIN will always be on the column being sorted or filtered. Due to that being a "JOIN" instead of a "LEFT JOIN", all rows with a null value in that column are completely omitted from the results.
Is there any way that I can change this to be a LEFT JOIN? I don't know how to configure or override that part of what Data Access is doing.
[Note: At this time, we have to keep using Data Access. Completely replacing that is way beyond the scope of this small bug I am trying to fix.]
1 Answer, 1 is accepted
Rank 1
Iron
Iron
Here's a suggestion:
You're right, a LEFT JOIN is likely the solution here. Telerik Data Access might offer configuration options to control join types, but another approach could be to modify the query itself within your code.
Here's a suggestion:
- Capture the original query generated by Data Access.
- Modify the query to replace the JOIN with a LEFT JOIN on the FK column.
- Execute the modified query directly.
Rank 1
I cannot find anywhere in my code that would allow me to modify the query directly or through some kind of Telerik Data Access configuration.
How can I capture the generated query and modify it? (I'm not sure what information you need to answer that, so for what it's worth, I am using C#, .NET 4.6.1, ASP.NET web API, and OData.)
Rank 1
I cannot find anywhere in my code where this query exists or where I can modify what query Data Access creates through some kind of configuration option.
How can I capture the query before it goes out?
(I'm not sure what information you need to answer that, so for what it's worth, I am using C#, .NET 4.6.1, ASP.NET Web API, and OData.)