Postgrest - Where filter with nullable field doesn't work

[rhuanbarros]

Bug report

Describe the bug

A call to the database using Where or Filter method with a nullable field throws the following exception:

**System.ArgumentException: Unable to parse the supplied predicate, did you return a predicate where each left hand of the condition is a Model property?**
   at Postgrest.Table`1[[MaterialeShop.Admin.Src.Dtos.ListaItem, MaterialeShop.Admin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].Where(Expression`1 predicate)

If the field is not nullable it works fine.

To Reproduce

Example:

await client
            .From<ListaItem>()
            // .Filter(nameof(ListaItem.ListaId), Postgrest.Constants.Operator.Equals, id)
            // .Where(x => x.ListaId == id)
            // .Order(nameof(ListaItem.CreatedAt), Postgrest.Constants.Ordering.Ascending)
            // .Where(x => x.SoftDeleted == false)
            .Where(x => x.CreatedAt == DateTime.Now)
            // .Order(x => x.CreatedAt, Postgrest.Constants.Ordering.Ascending)
            .Get();

CreatedAt is defined as:

[Column("CreatedAt")]
public DateTime? CreatedAt { get; set; } = DateTime.Now;

Expected behavior

Filter as it already works with a not nullable field.

Project information

<Project Sdk="Microsoft.NET.Sdk.BlazorWebAssembly">

  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <ServiceWorkerAssetsManifest>service-worker-assets.js</ServiceWorkerAssetsManifest>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Blazored.LocalStorage" Version="4.3.0" />
    <PackageReference Include="gotrue-csharp" Version="3.0.4" />
    <PackageReference Include="Microsoft.AspNetCore.Components.Authorization" Version="7.0.0" />
    <PackageReference Include="Microsoft.AspNetCore.Components.WebAssembly" Version="7.0.0" />
    <PackageReference Include="Microsoft.AspNetCore.Components.WebAssembly.DevServer" Version="7.0.2" PrivateAssets="all" />
    <PackageReference Include="MudBlazor" Version="6.1.2" />
    <PackageReference Include="supabase-csharp" Version="0.7.1" />
    <!-- <PackageReference Include="Microsoft.Extensions.Http" Version="7.0.0" /> -->
  </ItemGroup>

  <ItemGroup>
    <ServiceWorker Include="wwwroot\service-worker.js" PublishedContent="wwwroot\service-worker.published.js" />
  </ItemGroup>

</Project>

[acupofjose]

Great report as always. Fixed in 3.1.2!

[rhuanbarros]

thank you!

[rhuanbarros]

Hi, just another catch. This time it isn't breaking my code, but I take the liberty of informing. 🥲

If the right side of the predicate is a variable defined as nullable, it throws the following exception.

System.ArgumentException: Left side of expression: '(Convert(x.ListaId, Nullable`1) == value(MaterialeShop.Admin.Src.Services.CarrinhoService+<>c__DisplayClass9_0).ListaId)' is expected to be property with a ColumnAttribute or PrimaryKeyAttribute
   at Postgrest.Linq.WhereExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[Func`2](Expression`1 node)
   at System.Linq.Expressions.Expression`1[[System.Func`2[[MaterialeShop.Admin.Src.Dtos.Carrinho, MaterialeShop.Admin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[System.Boolean, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]].Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Postgrest.Table`1[[MaterialeShop.Admin.Src.Dtos.Carrinho, MaterialeShop.Admin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].Where(Expression`1 predicate)
   at MaterialeShop.Admin.Src.Services.CarrinhoService.FindCarrinho(Nullable`1 ListaId, Int32 PerfilId, Int32 OrcamentoId) in C:\Temporario\MaterialeShop projects\MaterialeShop.Admin\Src\Services\CarrinhoService.cs:line 81

[acupofjose]

@rhuanbarros Could you show me the model you're working with on that?

[rhuanbarros]

Sorry for the delay.

[Table("Carrinho")]
public class Carrinho : BaseModelApp
{
    [Column("PerfilId")]
    public int PerfilId { get; set; }

    [Column("ListaId")]
    public int ListaId { get; set; }

    [Column("OrcamentoId")]
    public int OrcamentoId { get; set; }

    [Column("Status")]
    public string? Status { get; set; } = StatusConstCarrinho.EmCriacao;

    public static class StatusConstCarrinho
    {
        public static string EmCriacao = "Em criação";
        public static string Concluido = "Concluído";
        public static string Cancelado = "Cancelado";
    }

    public Carrinho(Carrinho other)
    {
        Id = other.Id;
        CreatedAt = other.CreatedAt;
        SoftDeleted = other.SoftDeleted;
        SoftDeletedAt = other.SoftDeletedAt;

        PerfilId = other.PerfilId;
        ListaId = other.ListaId;
        OrcamentoId = other.OrcamentoId;
        Status = other.Status;
    }

    public Carrinho()
    {
    }

    public Carrinho GetCopy()
    {
        return new Carrinho(this);
    }   

}

public async Task<IReadOnlyList<Carrinho>> FindCarrinho(int ListaId, int PerfilId, int OrcamentoId)
    {
        logger.LogInformation("------------------- CarrinhoService FindCarrinho -------------------");

        Postgrest.Responses.ModeledResponse<Carrinho> modeledResponse = await client
            .From<Carrinho>()
            // .Filter(nameof(Carrinho.PerfilId), Postgrest.Constants.Operator.Equals, PerfilId)
            // .Filter(nameof(Carrinho.OrcamentoId), Postgrest.Constants.Operator.Equals, OrcamentoId)
            // .Filter(nameof(Carrinho.ListaId), Postgrest.Constants.Operator.Equals, ListaId)
            // .Filter(x => x.PerfilId == PerfilId)
            // .Filter(x => x.OrcamentoId == OrcamentoId)
            // .Filter(x => x.ListaId == ListaId)
            .Where(x => x.PerfilId == PerfilId)
            .Where(x => x.OrcamentoId == OrcamentoId)
            .Where(x => x.ListaId == ListaId)
            .Get();
        return modeledResponse.Models;
    }

If int ListaId is defined as int? ListaId, it throws that exception.

[acupofjose]

@rhuanbarros Can you try this on the latest version?

[rhuanbarros]

Hi, I was on a little vacation last week. Soon I will take a look in it.

[acupofjose]

No rush friend - Hope it was a good vacation!

[rhuanbarros]

I tried with the latest version, it threw the same exception, but I figured out that I messed up my code. It throws this exception only when the field definition in the model is not set to nullable and the variable passed to the expression is nullable.... So I think we can close this issue. Thank you.

[oalpar]

This issue is happening again in postgres 4.0.3

model:

[Table("scenes")]
public class Scene : BaseModel
{
    [PrimaryKey("id")]
    public long Id
    {
        get; set;
    }

    [Column("created_at")]
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    [Column("name")]
    public string Name
    {
        get; set;
    }

    [Column("cluster")]
    public long? Cluster
    {
        get; set;
    }

    [Column("ip")]
    public string Ip
    {
        get; set;
    }

    [Column("port")]
    public int Port
    {
        get; set;
    }

    [Column("is_in_use")]
    public bool IsInUse
    {
        get; set;
    }
    [Column("is_dynamic")]
    public bool IsDynamic
    {
        get; set;
    }

    [Column("tick_rate")]
    public float TickRate
    {
        get; set;
    }
}

 long? cluster = null; //var `reference`
var scene= await _client.Postgrest.Table<Scene>().Where(x => x.Name == SceneName && x.IsDynamic == false && x.cluser==cluster).Single();