how to add all db function (TVF) to unit of work on dbcontext same as DbSet 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;

    }

score 0 · Accepted Answer · answered Jan 27 '22 at 06:26

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.

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

1 Answers1