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 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 DESC

From 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.]