Queries to the database return empty or null but saving data does work

Question

I'm creating a web API using ASP.net MVC core with Entity Framework Core. i'm using NSwag to use swagger documentation and Swagger UI, there I'm testing the Post methods and they work, but the get returns empty or null.

here you can see the data in the database

the code to get all the data of a table is this:

 // GET: api/UserRoles
        [HttpGet]
        public IEnumerable<UserRole> GetUserRoles()
        {
            return _context.UserRoles.ToList();
        }

but it returns null even when there is data on the database, and all other queries return null or empty (or throw an exception in the case of .First()

this is my OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        base.OnModelCreating(modelBuilder);
        //Debugger.Launch();

        modelBuilder.Entity<Category>().HasOne(x => x.ParentCategory).WithMany(x => x.SubCategories).HasForeignKey(x => x.ParentCategoryId);

        foreach (var entity in modelBuilder.Model.GetEntityTypes())
        {
            var type = entity.ClrType.GetInterface(nameof(Interfaces.IDto));
            if (type == null) continue;
            modelBuilder.Entity(entity.ClrType).Property<DateTime?>("DeletedAt");
            modelBuilder.Entity(entity.ClrType)
                .Property<DateTime>("LastUpdated")
                .HasComputedColumnSql("SYSUTCDATETIME()");
            modelBuilder.Entity(entity.ClrType)
                .Property<DateTime>("CreatedAt").HasDefaultValueSql("SYSUTCDATETIME()"); ;
            modelBuilder.Entity(entity.ClrType)
                .HasKey(nameof(Interfaces.IDto.Id)).ForSqlServerIsClustered(false);
            modelBuilder.Entity(entity.ClrType)
                .HasIndex("CreatedAt").ForSqlServerIsClustered();


            var parameter = Expression.Parameter(entity.ClrType, "e");
            var body = Expression.NotEqual(
                Expression.Call(typeof(EF), nameof(EF.Property), new[] { typeof(DateTime?) }, parameter, Expression.Constant("DeletedAt")),
                Expression.Constant(null));
            modelBuilder.Entity(entity.ClrType).HasQueryFilter(Expression.Lambda(body, parameter));

        }

        modelBuilder.Entity<Customer>().HasIndex(x => x.Identification).IsUnique();
    }

this is my UserRole model

public class UserRole:Dbo
{
    public string Name { set; get; }
    public string Description { get; set; }
}

this is my Dbo class

public abstract class Dto : IDto, INotifyPropertyChanged
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public event PropertyChangedEventHandler PropertyChanged;

    [NotifyPropertyChangedInvocator]
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

all the queries return null or empty for all models

Update:

here you can see a Where() method being called with 0 results (i have only one record with name being equal to "admin"), also you can see that querying the StockMovementFlow table being queried and return 0 records too.

Answer 1

The problem is Expression.NotEqual here:

var parameter = Expression.Parameter(entity.ClrType, "e");
var body = Expression.NotEqual(
    Expression.Call(typeof(EF), nameof(EF.Property), new[] { typeof(DateTime?) }, parameter, Expression.Constant("DeletedAt")),
    Expression.Constant(null));
modelBuilder.Entity(entity.ClrType).HasQueryFilter(Expression.Lambda(body, parameter));

What is does currently is to set a global query filter (i.e. additional condition applied to all queries) similar to this (pseudo code):

e => e.DeletedAt != null

which would return all soft deleted records (which in your case are none), while I guess the idea was to return non soft deleted records, i.e.

e => e.DeletedAt == null

So simply change Expression.NotEqual to Expression.Equal and the issue will be solved.