CODEWITHSUNDEEP
c#neo4jcypherneo4jclient
Matthew Layton
Matthew Layton

Reputation: 42229

Neo4jClient ICypherFluentQuery doesn't seem to work with nullable values

I am writing a cypher query where I want to find all users who were in a particular role between the given date ranges. It seems that null queries are a bit tricky.

Consider the following code:

public async Task<Result<IEnumerable<UserNode>>> FindUsersByRoleNameAsync(
    string roleName, 
    DateOnly? from = default, 
    DateOnly? until = default, 
    CancellationToken token = default)
{
    from ??= DateOnly.MinValue;
    until ??= DateOnly.MaxValue;

    return await client.Cypher
        .Match("(user:User)-[edge:has_role]-(role:Role)")
        .Where((RoleNode role) => role.Name == roleName)
        .AndWhere((HasRole edge) => edge.From >= from)
        .AndWhere((HasRole edge) => edge.Until <= until) // this line is problematic!
        .ToNodesAsync<UserNode>("user", UnitOfWork, token) // Custom deserialization...ignore.
        .ConfigureAwait(false);
}

A breakdown explanation...

Some context might help here as to why null seems to be the issue;

If I omit this line from the query...

.AndWhere((HasRole edge) => edge.Until <= until) // this line is problematic!

...then it works for queries where from and until are omitted, and queries where from is not omitted.

The bit that doesn't seem to work is when trying to compare edge.Until (which might be null in the database) with until (which will either be a specified value, or DateOnly.MaxValue if it's omitted).

Removing the line above, 2/3 query tests pass. Adding that line back in causes 0/3 query tests to pass.

What am I doing wrong?

Upvotes: 0

Views: 37

Answers (1)

Charlotte Skardon
Charlotte Skardon

Reputation: 6270

It's worth looking at the actual query that is generated from your code, which will be something like:

MATCH (user: User)-[edge:has_role]-(role:Role)
WHERE (role.Name = "Blah")
AND (edge.From >= "")
AND (edge.Until <= "9999-12-31")

So it is always expecting Until to exist. You would need to change the query to something like:

MATCH (user:User)-[edge:has_role]-(role:Role)
WHERE (role.Name = "Blah")
AND (edge. From >= "")
AND (edge. Until IS NULL) 
OR (edge. Until <= "9999-12-31")

The client is doing exactly what you ask of it, to find any HasRole edge with an Until property less than DateOnly.MaxValue, and writes the corresponding Cypher.

Probably something like this is what you want:

public async Task<Result<IEnumerable<UserNode>>> FindUsersByRoleNameAsync(
    string roleName,
    DateOnly? from = default,
    DateOnly? until = default,
    CancellationToken token = default)
{
    from ??= DateOnly.MinValue;
    until ??= DateOnly.MaxValue;

    return await client.Cypher
        .Match("(user:User)-[edge:has_role]-(role:Role)")
        .Where((RoleNode role) => role.Name == roleName)
        .AndWhere((HasRole edge) => edge.From >= from)
        .AndWhere((HasRole edge) => edge.Until <= until) // this line is problematic!
        .OrWhere($"edge.{nameof(HasRole.Until)} IS NULL") //Add here
        .ToNodesAsync<UserNode>("user", UnitOfWork, token) // Custom deserialization...ignore.
        .ConfigureAwait(false);
}

Upvotes: 1

Related Questions