Skip to content

Query: simplify IndexOf translation #18773

@maumar

Description

@maumar

Currently query like:

es.Where(e => e.NullableStringA.IndexOf("oo") == e.NullableIntA).Select(e => e.Id)

gets translated to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE (CASE
    WHEN N'oo' = N'' THEN 0
    ELSE CHARINDEX(N'oo', [e].[NullableStringA]) - 1
END = [e].[NullableIntA]) OR (CASE
    WHEN N'oo' = N'' THEN 0
    ELSE CHARINDEX(N'oo', [e].[NullableStringA]) - 1
END IS NULL AND [e].[NullableIntA] IS NULL)

This seems too complicated. While it's true that if argument is and empty string we can short circuit and not compute the result of CHARINDEX function it doesn't seem too common (usually queries are on indexof different than emtpy).
Another reason might have been that for cases when target is null, and argument is empty string we return 0, rather than null (i.e. any string starts with empty string - which is consistent with out mental model of null meaning unknown value). However this scenario is invalid on c# already (null ref) and seems like sacrificing 99% scenario for the sake of 1%.

Instead, we could simply translate to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE CHARINDEX(N'oo', [e].[NullableStringA]) - 1 = [e].[NullableIntA]
OR (CHARINDEX(N'oo', [e].[NullableStringA]) IS NULL AND [e].[NullableIntA] IS NULL)

which, once #18555 is done, can be further optimized to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE CHARINDEX(N'oo', [e].[NullableStringA]) - 1 = [e].[NullableIntA]
OR ([e].[NullableStringA] IS NULL AND [e].[NullableIntA] IS NULL)

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions