CODEWITHSUNDEEP
entity-framework-core
Paulius K.
Paulius K.

Reputation: 151

EF core paging. Select total count in same query

I have paging and i want to select count in the same query using simple sql (EF7):

var selectSql = " SELECT TotalCount = COUNT(*) OVER(), E.* FROM [table] E ...";
var rows = context.Set<EventTable>().FromSql<EventTable>(selectSql, parameters.Select(p => p.Value).ToArray()).ToArray();

This select works, but i don't have TotalCount property in my EventTable class, because i don't want that property in database.

I try get TotalCount property from entity tracker:

var row = rows.First();
var entity = context.Entry(row);
var totalCount = entity.Property<int>("TotalCount").CurrentValue;

But then i get error: The property 'TotalCount' on entity type 'EventTable' could not be found. Ensure that the property exists and has been included in the model.

Then i try to add property in model like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     modelBuilder.Entity<EventTable>(b => b.Property<int>("TotalCount"));
}

It works fine when i want to select, but it throws an exception on insert, because column in database not exist. And EF will add that column on migration. But i notice, that if before migration generation i add line b.Property("TotalCount"); into ModelSnapshot class it will avoid to add property on migration. But problem on insert still exist.

I try to create another class:

[NotMapped]
public class EventSearchTable : EventTable
{
    [Column("total_count")]
    [Required]
    public int TotalCount { get; set; }
}

and then do this:

var rows = context.Set<EventSearchTable>().FromSql<EventSearchTable>(..);

It works on EF6, but not on EF7, i got error: Value cannot be null. Parameter name: entityType Because no entity in my DbContext. If i will add EventSearchTable class on my DbContext then it will expect columns like discriminator and etc and will create table in migrations.

Any ideas how to get property TotalCount ?

Upvotes: 2

Views: 6509

Answers (1)

tmg
tmg

Reputation: 20393

The following query will get the count and page results in one trip to the database

 var query = context.Set<EventTable>();
 var page = query.OrderBy(e => e.Id)
                 .Select(e => e)
                 .Skip(100).Take(100)
                 .GroupBy(e => new { Total = query.Count() })
                 .FirstOrDefault();

 if (page != null)
 {
      int total = page.Key.Total;
      List<EventTable> events = page.Select(e => e).ToList();
 }

Upvotes: 1

Related Questions