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.

show how is USerRoles mapped (even if it is a dbml generated EF) — Gonzalo.-
– Gonzalo.-, Commented Mar 30, 2019 at 23:40
Are you sure you are querying the right database? Check _context.Database.Connection.ConnectionString in the running code — PaulBBO
– PaulBBO, Commented Mar 31, 2019 at 0:33
Im pretty sure because the writes on this database context are written to the database beign shown also i have created a test that reads the db and then writes on it, the read is null or empty but the write succeed — Bloodday
– Bloodday, Commented Mar 31, 2019 at 0:37

Ivan Stoev · Accepted Answer · 2019-03-31 13:25:40Z

4

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.

answered Mar 31, 2019 at 13:25

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bloodday Over a year ago

I love you dude! you are right i was implementing the filer in a bad way...

Hawkzey · Accepted Answer · 2019-03-31 00:58:50Z

0

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()

edited Mar 31, 2019 at 0:58

answered Mar 31, 2019 at 0:45

Hawkzey

1,2232 gold badges15 silver badges24 bronze badges

6 Comments

Bloodday Over a year ago

I have tried other methods, find(), Where(), first(), all the same

Bloodday Over a year ago

also that .AnyAsync() inside the if returns false

Hawkzey Over a year ago

have you tried getting data from your context outside of the seed function? Where are you calling this function?

Bloodday Over a year ago

yes, I have tried getting data directly on the controllers (UserRolesController, StockMovementFlowController, etc) and same result

Hawkzey Over a year ago

In your table the name is not admin it is administrador, change this in your where statement?

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related