DbContext.Query is not accepting generic type T, How to make a generic DbContext query in dot net core api?

Question

I want make generic class to make query in sql database. My class is like that

public class GenericQuery<T>{
   private SomeContext context;
   GenericQuery(SomeContext _context){
      this.context = _context;
   }
   public T GetItemById(int id){
        Task<T> result = Task.Run(() => GetItemByIdAsync(id));
        result.Wait();
        return result.Result;
   }
   private T GetItemByIdAsync(int id){
       var typeName = x // x will extracted from type T using reflection.
       var procName = $"Get{x}"
       T result = this.context.Query<T>().FromSql("{0}, {1}",procName, id).FirstOrDefault();
       return result;
   }
}

But here in method GetItemByIdAsync, this.context.Query<T>().FromSql(---) is giving compilation Error as following -

**Error CS0452  The type 'T' must be a reference type in order to use it as parameter 'TQuery' in the generic type or method 'methodName'**

how solve that?

Answer 1

• If you're using Entity Framework (including Entity Framework Core) then it is an anti-pattern to define a "Repository" or "Generic Repository" type in your project.

  • This also applies to most other ORMs like NHibernate and even the older Linq-to-Sql system: the ORM provides the Repository for you already.
  • Specifically, your DbContext subclass is the Unit-of-Work object.
  • ...and the Entity Framework-owned DbSet<T> class is the actual Generic Repository type.

• Looking at your question, it's clear you just want to reduce repetitiveness in your codebase (DRY is important, after all) - but be careful about being too aggressive in implementing DRY because you may have different queries that look the same or similar but the differences might matter - so think carefully about what you're eliminating and that you won't be creating extra work for yourself or others in future.

• In my experience and in my professional opinion: the "best" way to centrally define common queries is by defining Extension Methods for your DbContext and/or DbSet<T> types that build (but do not materialize!) an IQueryable<T>.

  • Define generic extension-methods on DbSet<T> constrained with an interface if they apply to different entity types.
  • Define non-generic extension-methods on DbSet<EntityTypeName> if they're specific to a single entity type.
  • Define non-generic extension-methods on DbContext if they're queries for multiple entity types (e.g. a JOIN query).
    • These still could be constrained generic extension-methods too, but it's non-trivial to get a DbSet<T> object reference for any T.

• Another advantage of only defining methods that return IQueryable<T> is that you can compose them - which is a huge benefit if you have (for example) a complicated .Where() condition that you want to re-use in another query without repeating yourself.

Here's an example of a set of constrained generic extension-methods that return IQueryable<T>:

interface IHasId
{
    Int32 Id { get; }
}

interface IHasName
{
    String Name { get; }
}

public static class QueryExtensions
{
    public static IQueryable<T> QueryById( this DbSet<T> dbSet, Int32 entityId )
        where T : IHasId
    {
        return dbSet.Where( e => e.Id == entityId  );
    }

    public static IQueryable<T> QueryByName( this DbSet<T> dbSet, String name )
        where T : IHasName
    {
        return dbSet.Where( e => e.Name == name );
    }
}

// You need to add the interfaces to your entity types, use partial classes for this if your entity types are auto-generated:

public partial class Person : IHasId, IHasName
{
}

Used like so:

MyDbContext db = ...

Person p = await db.People.QueryById( entityId: 123 ).SingleOrDefaultAsync();

List<Person> people = await db.People.QueryByName( name: "John Smith" ).ToListAsync();

---

And if you want to add materialized queries, then that's okay too (but you won't be able to compose them - which is why it's best to stick to only adding extensions that return IQueryable<T>:

public static class QueryExtensions
{
    public static Task<T> GetSingleAsync( this DbSet<T> dbSet, Int32 entityId )
        where T : IHasId
    {
        return dbSet.SingleOrDefaultAsync( e => e.Id == entityId );
    }

     public static Task<List<T>> GetAllByNameAsync( this DbSet<T> dbSet, String name )
        where T : IHasName
    {
        return dbSet.Where( e => e.Name == name ).ToListAsync();
    }
}

Used like so:

MyDbContext db = ...

Person p = await db.People.GetSingleAsync( entityId: 123 );

List<Person> people = await db.People.GetAllByNameAsync( name: "John Smith" );