how to add all db function (TVF) to unit of work on dbcontext same as DbSet<TEntity> in asp.net core

Question

I use one IDbcontext for all mycontext in project .

 public interface IComBaseDbContext<TContext> where TContext : DbContext
{
    DbSet<TEntity> Set<TEntity>() where TEntity : class;
            
    void AddRange<TEntity>(IEnumerable<TEntity> entities) where TEntity : class;
   
    void RemoveRange<TEntity>(IEnumerable<TEntity> entities) where TEntity : class;

    EntityEntry<TEntity> Entry<TEntity>(TEntity entity) where TEntity : class;
    void MarkAsChanged<TEntity>(TEntity entity) where TEntity : class;
   
   // IQueryable<TEntity> Query<TEntity>() where TEntity : class;
    int SaveChanges(bool acceptAllChangesOnSuccess);
    int SaveChanges();
    Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = new CancellationToken());
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken());
    Task<IEnumerable<TEntity>> BulkInsertAsyncz<TEntity>(IEnumerable<TEntity> entities) where TEntity : class;

            
    Task<DbDataReader> ExecuteReaderAsyncE(string rawSql,
        CancellationToken cancellationToken = default(CancellationToken), params object[] parameters);
    Task<int> ExecuteNonQueryAsyncE(string rawSql,
        CancellationToken cancellationToken = default(CancellationToken), params object[] parameters);
    Task<object> ExecuteScalarAsyncE<T>(string rawSql, CancellationToken cancellationToken = default(CancellationToken), params object[] parameters);

    DatabaseFacade Database { get; }
}

how to add all dbfunction tvf to interface same DbSet Set() where TEntity : class;

I use mycontext :

  private readonly IComBaseDbContext<EngDbContext> _db;
    public EngPartService(IComBaseDbContext<EngDbContext> db, IHttpContextAccessor contextAccessor, IMapper mapper) 
    {
        _mapper = mapper;
        _db = db;
    }

With unit of work not access to tvf db function as lambda expression . i need to get all dbfunction from dbcontext and convert to lambda expression and add to dbcontext or add function to dbcontext for use tvf ;

same this :

 public IQueryable<TResult> DbFunc<TResult>(string funName, params object[] parameters) where TResult : class
    {
        var dbf = _context.Model.GetDbFunctions().FirstOrDefault();
       Expression body = Expression.Constant(false);
        var bb= Expression.Lambda<Func<IQueryable<TResult>>>(
            Expression.Call(tt.GetType(),
                nameof(tt.Name),
                null,
                Expression.Constant(parameters),
                body,null, tt.Parameters);
       
          
        return null;

    }

Answer 1

I solve problem by use this code from caldis

        private List<ParameterExpression> getParamExpr(MethodInfo method)
    {
        var list = new List<ParameterExpression>();
        list.Add(Expression.Parameter(typeof(object), "obj"));
        list.AddRange(Array.ConvertAll(method.GetParameters(), input => Expression.Parameter(typeof(object))));
        return list;
    }
    private List<Expression> getParamTypes(MethodInfo method, List<ParameterExpression> inList)
    {
        var list = new List<Expression>();
        var methParams = method.GetParameters();
        list.AddRange(
            // Skip the first item as this is the object on which the method is called.
            inList.Skip(1).Select(
                input => Expression.Convert(
                    input,
                    Type.GetType(
                        methParams[inList.IndexOf(input) - 1].ParameterType.FullName.Replace("&", string.Empty)))));
        return list;
    }
    public IQueryable<TResult> DbFunc<TResult>(string functionName,  params object[] parameters) where TResult : class
    {

      
        var method = _context.GetType().GetMethod(functionName);

        var voidMethod  = method.ReturnType == typeof(void);

        var paramExprs = getParamExpr(method);
        var paramTypes = getParamTypes(method, paramExprs);

        var instanceExp = Expression.Convert(paramExprs[0], method.DeclaringType);
        Expression call = null;

        if (voidMethod)
        {
            call = Expression.Call(instanceExp, method, paramTypes);
        }
        else
        {
            call = Expression.Convert(Expression.Call(instanceExp, method, paramTypes), typeof(object));
        }

        var exp = Expression.Lambda(call, paramExprs).Compile();

       


        //if (voidMethod)
        //{
        //    switch (method.GetParameters().Length)
        //    {
        //        case 0:
        //            ((Action<object>)exp)(_context);
        //            break;
        //        case 1:
        //            ((Action<object, object>)exp)(_context, parameters[0]);
        //            break;
        //            // Continue here with more case statements.
        //    }
        //}
        //else
        //{
            switch (method.GetParameters().Length)
            {
                case 0:
                    return (IQueryable<TResult>)((Func<object, object>)exp)(_context);
                   
                case 1:
                    return (IQueryable<TResult>)((Func<object?, object?, object?>)exp)(_context, parameters[0]);
                case 2:
                    return (IQueryable<TResult>)((Func<object?, object?, object ?, object?>)exp)(_context, parameters[0], parameters[1]);
                case 3:
                    return (IQueryable<TResult>)((Func<object?, object?, object?, object?, object?>)exp)(_context, parameters[0], parameters[1], parameters[2]);
                case 4:
                    return (IQueryable<TResult>)((Func<object?, object?, object?, object?, object?, object?>)exp)(_context, parameters[0], parameters[1], parameters[3],parameters[3]);
               
                // Continue here with more case statements
        }
            //}
            // Error handling omited
            
            return null;


        }

and use func in service :

 public async Task<CpaDataSourceResult> ReadEngPartListsByProductIdAsync(DataSourceRequest request, int productId)
    {
        var pp=await _db.DbFunc<GetEngPartListForParentResult>("GetEngPartListForParent",new object[] { productId })
            .ToCpaDataSourceResultAsync(request);
        
        return pp;
        
    }

But I needed to have a Enum list of names of all the functions and their parameters.same

_context.Model.GetDbFunctions().Select(x => x.Name).ToList();//as Enum
        _context.Model.GetDbFunctions().Select(x =>new DbFunctionParameter() {Name = x.Name,Parameters  = x.Parameters}).ToList();//as Enum

I can not solve this problem; I decided to stop using the unit of work on the dbcontext because it makes it too complicated. I'm glad to know your comments.