EF core paging. Select total count in same query

Question

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 ?

score 2 · Accepted Answer · answered Apr 07 '16 at 22:28

2

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

answered Apr 07 '16 at 22:28

tmg

19,895
5
72
76

3

This will fail when the result set is empty though (at least in EF core). Page is not null but has an internal NullReferenceException. – Jeff Bailey Oct 18 '16 at 14:28
1

It does fail when the result set is empty. Any solution for that? – Hemanshu Bhojak Dec 10 '16 at 11:43
5

This solution is not working. It was before 3.0? In 3.0 they disable silent evaluation. Right now you get error when two queries will be trigger. Why it is tagged entity-framework-core if question was asked 4 years ago? ef core didnt exist yet. – CrazyBaran Apr 30 '20 at 18:17

EF core paging. Select total count in same query

1 Answers1

Linked