Get Row Count of Dbset in DbContext dynamicly

Question

I have a form that pass a instance of my DbContext and a Entity of Table to it as below :

public frm_foo(DbContext DatabaseContext, DbSet TableEntity)
        {
           InitializeComponent();
           var res =GetRowCount(DatabaseContext,TableEntity);
        }

And call form contractor by:

new frm_foo(dbContext, dbContext.Set<IpRoom>()).ShowDialog();

The Context contain a IpRoom table.

I want to get Count of row from in a function that send the DbContext and Dbset instance to it as below :

public int GetRowCount(DbContext DatabaseContext, DbSet TableEntity)
   {
       var countVal = (from a in DatabaseContext.Set(TableEntity.GetType())
                        select a).Count();
       return countVal;
   }

But Got error:

Could not find an implementation of the query pattern for source type 'DbSet'. 'Select' not found.

While DatabaseContext.Set(TableEntity.GetType()) return a Dbset! Anybody know whats my wrong? Thanks in advance

Ps: I dont want use SqlQuery, Something like:

var sql = "SELECT COUNT(*) FROM dbo.IpRoom";
var total = context.Database.SqlQuery<int>(sql).Single();

Possible duplicate of [Could not find an implementation of the query pattern](https://stackoverflow.com/questions/8215773/could-not-find-an-implementation-of-the-query-pattern) — mjwills, Jul 06 '17 at 14:15
It will be interesting to see the intended usage of this method, i.e. `GetRowCount(context, ???)` — Ivan Stoev, Jul 06 '17 at 14:38
@IvanStoev : Whats `???` Ivan? do you mean pass one parameter to function? — Ali, Jul 06 '17 at 15:45
No :) The question was how do you get the `DbSet` in order to pass it to the method. Just wondering why you need that method at all, because for instance `context.Logs.Count()` will easily do the same. — Ivan Stoev, Jul 06 '17 at 15:58
Aha :) you meas maybe could pass the name of `Dbset` instead of instance of that yes? — Ali, Jul 06 '17 at 16:01
Well, I wasn't sure. Now I see your update, and somehow can solve the `Count` issue, but note that the non generic `DbSet` is almost unusable. You can't write normal LINQ against it, neither `ToList` etc. If you are absolutely sure the problem is just the `Count()`, I guess we could help. — Ivan Stoev, Jul 06 '17 at 16:07
Then probably you should try [DynamicLINQ](https://www.nuget.org/packages/System.Linq.Dynamic) package. It contains custom extension method `Count` defined for `IQueryable`, hence can be used with `DbSet` as well as other `IQueryable` custom methods which can be useful for your scenario. — Ivan Stoev, Jul 06 '17 at 16:20
@IvanStoev Thanks Ivan..your hint about `GetRowCount(context, ???)` help me to find answer my question — Ali, Jul 07 '17 at 06:29

score 1 · Answer 1 · answered Jul 06 '17 at 14:26

1

DbSet does not implement IQueryable, DbSet<T> does. eg

public static int GetRowCount<T>(DbContext DatabaseContext, DbSet<T> TableEntity)
{
    var countVal = TableEntity.Count();
    return countVal;
}

answered Jul 06 '17 at 14:26

David Browne - Microsoft

80,331
6
39
67

it generate this error: **The type 'T' must be a reference type in order to use it as parameter 'TEntity' in the generic type or method 'DbSet'** – Ali Jul 06 '17 at 15:42
Right. T should be an Entity type defined in your DbContext subclass. Perhaps you should explain what you are trying to accomplish in more detail. – David Browne - Microsoft Jul 06 '17 at 15:43
I explain more in question David. Could you please see that? – Ali Jul 06 '17 at 15:58
Read the question again. Still have no idea what you're trying to do. Perhaps a short, complete repro would help. – David Browne - Microsoft Jul 06 '17 at 17:20
in fact i was do some action on a table in `Dbcontex` in a form...For example count of table row...or filter some row and show it to end user...because of that i send a instance of `dbcontext` and `dbset` that witch must give count or filter from that – Ali Jul 06 '17 at 17:23
Post a short, complete repro where you try to call the function in my answer. It should allow you to get the count of any entity in your DbContext. – David Browne - Microsoft Jul 06 '17 at 17:29
I just copy and past your code to my project...Compile and see above error – Ali Jul 06 '17 at 17:32
What is the type parameter T you are using? – David Browne - Microsoft Jul 06 '17 at 19:36
A Entity in DbContex. In my case the `IpRoom` table – Ali Jul 06 '17 at 22:26
Then you'll need to produce a repro. That error shouldn't occur when T is an Entity. – David Browne - Microsoft Jul 06 '17 at 22:29
I thing when send a entity to other form as some thing like `dbContext.Set()` , the new form recive that entity as a instanse variable of DbSet class only. And its normal that dont work as orginal entity...i think we must find a way to acess the entity in Dbcontext dynamily or by name to solve this problem – Ali Jul 06 '17 at 22:37

Losbaltica · Answer 2 · 2021-02-15T10:48:31.930

1

To get generic row count I use the following method.

public static int GetRowCount<T>(DbContext context)
    {
        // Get the generic type definition
        var method = typeof(DbContext).GetMethod(
            nameof(DbContext.Set), BindingFlags.Public | BindingFlags.Instance);

        // Build a method with the specific type argument you're interested in
        method = method.MakeGenericMethod(typeof(T));

        var iEnumerable = method.Invoke(context, null) as IQueryable<T>;

        return (iEnumerable ?? throw new InvalidOperationException()).Count();
    }

edited Feb 15 '21 at 10:48

answered Feb 15 '21 at 10:14

Losbaltica

609
1
10
24

I am pretty sure if you have T you can write it shorter: ```public static int GetRowCount(DbContext context) => context.Set().AsQueryable().Count();``` – Dmitriy Feb 04 '23 at 12:28

score 0 · Answer 3 · answered Jan 13 '21 at 00:15

I use a function like this one to know if the table have no elements and needs to be seeded with data to have something to work with:

using System;
using System.Linq;

namespace EFCoreTutorial
{
    class Program
    {
        static void Main(string[] args)
        {    
            using (var context = new Data.SchoolContext())
            {
                var studentsCount = context.Students.Count();

                if (studentsCount == 0)
                {
                    // Seed all your starting data now
                    var student = new Model.Student()
                    {
                        Name = "Ali",
                    };

                    context.Students.Add(student);
                    context.SaveChanges();
                }
            }
        }
    }
}

SchoolContext is derived from DbContext.

Ali · Answer 4 · 2020-02-01T17:05:32.007

-2

Add a class to your project as blow:

namespace System.Data.Entity
{
  public static class EntityFrameworkExtensions
  {
    public static IEnumerable<object> AsEnumerable(this DbSet set)
    {
      foreach (var entity in set)
        yield return entity;
    }
  }
}

This class add a Generic method AsEnumerable to DbSet instance and when you want to use it for example for Count:

 var count = (from a in TableEntity.AsEnumerable() select a).Count();

edited Feb 01 '20 at 17:05

answered Jul 07 '17 at 06:29

Ali

3,373
5
42
54

This is a bad idea, as it will cause all the entities in the DbSet to be loaded into memory instead of running the count on in the DB. – tocsoft May 24 '18 at 14:56
This is notably slow. – JTinkers Aug 06 '19 at 09:23

Get Row Count of Dbset in DbContext dynamicly

4 Answers4