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

asp.net-core c# entity-framework-core sql-server

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

enter image description here

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.

enter image description here

1
2
3/31/2019 1:21:14 AM

Accepted Answer

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.

4
3/31/2019 1:25:40 PM

Popular Answer

You need to create a query to get specific info from your context.

create the query to your table using the context.

Eg.

var query = context.Students
                   .where(s => s.StudentName == "Bill")
                   .FirstOrDefault<Student>();

query.tolist()



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow