dotnetcore / FreeSql

🦄 .NET aot orm, C# orm, VB.NET orm, Mysql orm, Postgresql orm, SqlServer orm, Oracle orm, Sqlite orm, Firebird orm, 达梦 orm, 人大金仓 orm, 神通 orm, 翰高 orm, 南大通用 orm, 虚谷 orm, 国产 orm, Clickhouse orm, DuckDB orm, TDengine orm, QuestDB orm, MsAccess orm.
https://freesql.net
MIT License
4.13k stars 859 forks source link

json字段查询 #1881

Open mansai opened 2 months ago

mansai commented 2 months ago

表达式树没有对应的json字段查询 https://freesql.net/guide/expression-function.html

例如下图ExtraProperties字段为json存储,json里对应的字段为Cpu,Ram,Price三个动态字段 image

declare @jsontable table(userName varchar(50),ExtraProperties varchar(max)) insert into @jsontable values('name1','{"Cpu":"Intel I7","Ram":"32GB","Price":"1999"}') insert into @jsontable values('name2','{"Cpu":"Intel I3","Ram":"16GB","Price":"1699"}') select * from @jsontable

例如要模糊搜索内存为32的数据行对应sql句语如下 select * from @jsontable where JSON_VALUE(ExtraProperties, N'$.Ram') LIKE '%32%' image

能否加入到SqlExt里面

2881099 commented 2 months ago

https://freesql.net/guide/type-mapping.html#json

v3.5.100

mansai commented 2 months ago

不支持动态传参,jsonMap类型为Dictionary<string, object?>

[FreeSql.DataAnnotations.JsonMap] public Dictionary<string, object?> ExtraProperties { get => base.ExtraProperties; protected set => base.ExtraProperties = value; }

var sql=_repository.FreeSql.Queryable().Where(t => t.ExtraProperties["Cpu"]== "32GB").ToSql();

报错:

2024-09-10 14:59:14.091 +08:00 [ERR] 未实现函数表达式 t.ExtraProperties.get_Item("Cpu") 解析 System.Exception: 未实现函数表达式 t.ExtraProperties.get_Item("("Cpu") ") 解析 at FreeSql.Internal.CommonExpression.ExpressionLambdaToSql(Expression exp, ExpTSC tsc) at FreeSql.Internal.CommonExpression.ExpressionBinary(String oper, Expression leftExp, Expression rightExp, ExpTSC tsc) at FreeSql.Internal.CommonExpression.ExpressionLambdaToSql(Expression exp, ExpTSC tsc) at FreeSql.Internal.CommonExpression.ExpressionWhereLambda(List1 _tables, Func3 _tableRule, Expression exp, BaseDiyMemberExpression diymemexp, List1 whereGlobalFilter, List1 dbParams) at FreeSql.Internal.CommonProvider.Select0Provider2.InternalWhere(Expression exp) at FreeSql.Internal.CommonProvider.Select1Provider1.WhereIf(Boolean condition, Expression1 exp) at FreeSql.Internal.CommonProvider.Select1Provider1.Where(Expression1 exp) at JC.AI.MES.SYS.EnumAppService.GetAsync(Guid id) in E:\abp\JC.AI.MES\aspnet-core\src\JC.AI.MES.Application\SYS\EnumAppService.cs:line 88 at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() at Volo.Abp.GlobalFeatures.GlobalFeatureInterceptor.InterceptAsync(IAbpMethodInvocation invocation) at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed) at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() at Volo.Abp.Auditing.AuditingInterceptor.InterceptAsync(IAbpMethodInvocation invocation) at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed) at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() at Volo.Abp.Authorization.AuthorizationInterceptor.InterceptAsync(IAbpMethodInvocation invocation) at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed) at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() at Volo.Abp.Validation.ValidationInterceptor.InterceptAsync(IAbpMethodInvocation invocation) at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed) at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo) at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync() at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation) at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed) at lambda_method4095(Closure, Object) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location ---

2881099 commented 2 months ago

动态这种情况,需要定义类似 SqlExt.xxx 解析