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