Linq methods, like Where(), do not modify the provided IQueryable object, they return a new IQueryable object, that you must re-assign back into your query variable.
public class PoiRepository : AbpZeroSampleRepositoryBase<Poi,long>, IPoiRepository
{
public PoiRepository(IDbContextProvider<AbpZeroSampleDbContext> dbContextProvider)
: base(dbContextProvider)
{
}
public List<Poi> GetAllPois(PoiState poistate, PoiType poitype) //PoiType is not used yet
{
var query = GetAll();
if (poistate == PoiState.NotVerified)
query = query.Where(x => x.ValidationRate == 0); //<--this seems not applied
if (poistate == PoiState.Verified)
query = query.Where(x => x.ValidationRate > 0); //<--this seems not applied
return query.OrderByDescending(x => x.Name).ToList();
}
}
Since the Caching feature is now implemented, I did retry to call permissionChecker.IsGranted() inside a loop. There is still 5 SQL query issued for EACH iterations. So the caching does not seems to work.
I've isolated the problem in AbpMemoryCache.cs, in the method Set():
public override void Set(string key, object value, TimeSpan? slidingExpireTime = null)
{
//TODO: Optimize by using a default CacheItemPolicy?
_memoryCache.Set(
key,
value,
new CacheItemPolicy
{
SlidingExpiration = slidingExpireTime ?? DefaultSlidingExpireTime
});
var test = _memoryCache.Get(key);
}
Here I added a Get() right after the Set(), and the value returned is ALWAYS null. In my case:
I can't understand why the memoryCache.Get() returns null right after the Set().
Any ideas ?
A solution explained in this article, is telling EF about the single Database connection. This is done simply by opening the connection yourself after creating the context.
I will try that first since it is involving less work.
using (var ctx = new MyEntities())
{
((IObjectContextAdapter)ctx).ObjectContext.Connection.Open();
....
...all my neat stuff
}
<a class="postlink" href="http://fabzter.com/blog/avoid-distributed-transactions-using-entity-framework">http://fabzter.com/blog/avoid-distribut ... -framework</a>
After some tests, NLog connection does NOT seems to be the culprit.
My second supposition would be the usage of TransactionScope made by ABP. Is there any plan to replace the TransactionScope usage with the new EF6 BeginTransaction ? <a class="postlink" href="https://msdn.microsoft.com/en-us/data/dn456843.aspx">https://msdn.microsoft.com/en-us/data/dn456843.aspx</a> Microsoft now recommend BeginTransaction instead of TransactionScope. Moreover, it is mentioned that TransactionScope cannot be used in cloud scenarios unless you are sure you have one and only one connection. My assumption, I may be wrong, is that no matter how hard we try to use a single connection, we have no control on when the DBContext decide to close and open a new connection, which would cause an escalation to DTC because of the TransactionScope usage. What I suspect that cause this is when SaveChanges is called multiple times in the same ApplicationService method ex: calling Repository.InsertAndGetId() and the SaveChanges() called and the end of UoW.
I will do some tests by replacing TransactionScope with EF6 BeginTransaction to see if it helps.
Thanks for the insight.
For now, I did optimize using this 'hacky' approach. I inject an IRepository<RolePermissionSetting, long>, and get the list of roles which have the permission. Total resulting queries = 2. ** I'm using Permissions only at role level. No user specific permissions.
// get list of roleid that have the permission
var roleIds = _rolePermissionSettingRepository.GetAll()
.Where(x => x.Name == PermissionNames.CanBeAssigneeOnTask)
.Select(x => x.RoleId)
.ToList();
var query = _userRepository.GetAll()
.Where(x => x.IsActive && !x.IsDeleted)
.Where(x => x.Roles.Any(y => roleIds.Contains(y.RoleId)));
I need to make an Application Service method that returns a list of users which have a specific Permission granted.
I'm injecting an IPermissionChecker in my app service.
When I try to call IPermissionChecker.IsGranted inside my LINQ query (before the SQL query is actually executed), I get an exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsGranted(Abp.Authorization.IPermissionChecker, Int64, System.String)' method, and this method cannot be translated into a store expression.
ex:
var list = _userRepository.GetAll()
.Where(x => x.IsActive && !x.IsDeleted)
.Where(x => _permissionChecker.IsGranted(x.Id, PermissionNames.CanBeAssigneeOnTask))
.ToList();
So instead, I tried to first load all users into memory, then call IPermissionChecker.IsGranted() for each users.
foreach (AppUser appUser in list)
{
if (_permissionChecker.IsGranted(appUser.Id, PermissionNames.CanBeAssigneeOnTask))
{
...
}
}
That works but each calls to IPermissionChecker.IsGranted() issues SEVEN different sql queries (I'm monitoring with SQL Profiler). So if I have 100+ users * 7 queries = 700+ queries... it is getting very slow to execute.
Is there any way to optimize this ?
We have an ABP based application running on Azure Web App, with an Azure SQL Database.
Once every 2-3 days, I can see in my logs an exception related to distributed transaction. It is expected that DTC does not work on Azure, but I don't understand why a transaction is trying to be promoted to a distributed one.
All my database interactions are done through ABP UnitOFWork, using auto-implemented IRepository. I have a single DBContext.
I'm using NLog, configured using Castle LoggingFacility.UseNLog().
So I was wondering if someone else have experienced the same issue ?
I suspect that it may be related to NLog and DBContext connections. I will try to add "ENLIST=false;" to my NLog connection string to see if it helps.
Other suggestions where to look at ?
Here is the details of the exception. It occurs during the DBContext.SaveChanges().
System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: The Promote method returned an invalid value for the distributed transaction. ---> System.ArgumentException: Value does not fall within the expected range.
at System.Transactions.Oletx.IDtcProxyShimFactory.ConnectToProxy(String nodeName, Guid resourceManagerIdentifier, IntPtr managedIdentifier, Boolean& nodeNameMatches, UInt32& whereaboutsSize, CoTaskMemHandle& whereaboutsBuffer, IResourceManagerShim& resourceManagerShim)
at System.Transactions.Oletx.DtcTransactionManager.Initialize()
at System.Transactions.Oletx.DtcTransactionManager.get_ProxyShimFactory()
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
--- End of inner exception stack trace ---
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<Open>b__36(DbConnection t, DbConnectionInterceptionContext c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.EntityConnection.<Open>b__2()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
--- End of inner exception stack trace ---
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at PMP.EntityFramework.PMPDbContext.SaveChanges() in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\PMP.EntityFramework\EntityFramework\PMPDbContext.cs:line 146
at Abp.EntityFramework.Repositories.EfRepositoryBase`3.InsertAndGetId(TEntity entity) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp.EntityFramework\EntityFramework\Repositories\EfRepositoryBaseOfTEntityAndTPrimaryKey.cs:line 89
at Castle.Proxies.EfRepositoryBase`2Proxy_14.InsertAndGetId_callback(Project entity)
at Castle.Proxies.Invocations.EfRepositoryBase`3_InsertAndGetId_21.InvokeMethodOnTarget()
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Domain.Uow.UnitOfWorkInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 28
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.EfRepositoryBase`2Proxy_14.InsertAndGetId(Project entity)
at PMP.Project.Command.ImportProjectFromSap.Filters.CreateProjectIfNotExist.Execute(ImportProjectFromSapMessage msg) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\PMP.Application\Project\Command.ImportProjectFromSap\Filters\CreateProjectIfNotExist.cs:line 32
at Abp.FilterPipeline.Pipeline`1.<>c__DisplayClass1.<Execute>b__0(IFilter`1 f) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\Abp.Genisys\FilterPipeline\Pipeline.cs:line 14
at System.Collections.Generic.List`1.ForEach(Action`1 action)
at Abp.FilterPipeline.Pipeline`1.Execute(T input) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\Abp.Genisys\FilterPipeline\Pipeline.cs:line 14
at Castle.Proxies.ImportProjectFromSapServiceProxy.Execute_callback(ImportProjectFromSapInput input)
at Castle.Proxies.Invocations.IImportProjectFromSapService_Execute.InvokeMethodOnTarget()
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Logging.LoggingInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\Abp.Genisys\Logging\LoggingInterceptor.cs:line 32
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Auditing.AuditingInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Auditing\AuditingInterceptor.cs:line 40
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Authorization.Interceptors.AuthorizationInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Authorization\Interceptors\AuthorizationInterceptor.cs:line 32
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Domain.Uow.UnitOfWorkInterceptor.PerformSyncUow(IInvocation invocation, UnitOfWorkOptions options) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 60
at Abp.Domain.Uow.UnitOfWorkInterceptor.PerformUow(IInvocation invocation, UnitOfWorkOptions options) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 52
at Abp.Domain.Uow.UnitOfWorkInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 42
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Abp.Runtime.Validation.Interception.ValidationInterceptor.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp\Runtime\Validation\Interception\ValidationInterceptor.cs:line 21
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.ImportProjectFromSapServiceProxy.Execute(ImportProjectFromSapInput input)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Abp.WebApi.Controllers.Dynamic.Interceptors.AbpDynamicApiControllerInterceptor`1.Intercept(IInvocation invocation) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp.Web.Api\WebApi\Controllers\Dynamic\Interceptors\AbpDynamicApiControllerInterceptor.cs:line 44
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.DynamicApiController`1Proxy_41.Execute(ImportProjectFromSapInput input)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClassc.<GetExecutor>b__6(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Abp.WebApi.Controllers.Dynamic.Selectors.DyanamicHttpActionDescriptor.<ExecuteAsync>b__0(Task`1 task) in c:\InformatiqueIndustrielleSG\BCH\src\PMP\src\trunk\libs\aspnetboilerplate\src\Abp.Web.Api\WebApi\Controllers\Dynamic\Selectors\DyanamicHttpActionDescriptor.cs:line 54
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext()
Is there a built-in ABP class that I can inject into my ApplicationService, to retrieve an absolute url of a page in my app ? I need to send an email containing an url to my app.
Thanks!