Base solution for your next web application
Open Closed

Microsoft.Data.SqlClient.SqlException After Upgrade to .Net Core v3.1 #8295

User avatar
0
cangunaydin created

Hello after i have upgraded my project to asp.net zero v8.1, (my old version was v7.1 with .Net Framework v4.6.1, now i have switched to .net core) I am having issues with ef core. When i try to get data from db ef core is throwing an exception

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 Message=An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'. Source=Core Microsoft SqlClient Data Provider

here is my code. This code is running on the hangfire background thread.

public class WrikeTaskManager : DomainService, IWrikeTaskManager
    {
        private readonly IUnitOfWorkManager _unitOfWorkManager;
        private readonly IRepository<TaskManager> _taskManagerRepository;
        public WrikeTaskManager(IUnitOfWorkManager unitOfWorkManager, IRepository<TaskManager> taskManagerRepository)
        {
            _unitOfWorkManager = unitOfWorkManager;
            _taskManagerRepository = taskManagerRepository;
        }
        [UnitOfWork]
        public void RefreshWebHookForAllTenants()
        {
            using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.MayHaveTenant, AbpDataFilters.MustHaveTenant))
            {
                var allTaskManagers = _taskManagerRepository.GetAll().ToList();

                foreach (var taskManager in allTaskManagers)
                {
                    var wrikeTaskManagerClient = new WrikeTaskManagerClient(taskManager);
                    wrikeTaskManagerClient.ActivateWebhookIfSuspended().Wait();
                }
            }
        }
    }

the code is throwing exception here
var allTaskManagers = _taskManagerRepository.GetAll().ToList();

and when i run sql profiler it runs the query in the background like this.

exec sp_executesql N'SELECT [b].[Id], [b].[AccountId], [b].[AccountName], [b].[AssignedToContentManagerStatusId], [b].[BearerToken], [b].[BookAndAdDownloadedStatusId], [b].[CallCountCustomFieldId], [b].[ClientApprovedContentCustomFieldId], [b].[ClientSentContentCustomFieldId], [b].[CollectionCompletedStatusId], [b].[CompanyNameCustomFieldId], [b].[CompletedStatusId], [b].[ContentConnectedToScreenCustomFieldId], [b].[ContentCreationInProgressStatusId], [b].[ContentSentToClientCustomFieldId], [b].[CreationTime], [b].[CreatorUserId], [b].[DeleterUserId], [b].[DeletionTime], [b].[DescriptionCustomFieldId], [b].[FolderId], [b].[IsDeleted], [b].[IsDeliveredCustomFieldId], [b].[LastCalledCustomFieldId], [b].[LastModificationTime], [b].[LastModifierUserId], [b].[LastTaskAssignedContentManagerId], [b].[MediaLengthCustomFieldId], [b].[MediaTypeCustomFieldId], [b].[NewStatusId], [b].[OfferEndDateCustomFieldId], [b].[OfferIdCustomFieldId], [b].[OfferItemDateIdCustomFieldId], [b].[OfferNameCustomFieldId], [b].[OfferStartDateCustomFieldId], [b].[OfferStatus], [b].[ProductionIdCustomFieldId], [b].[ProductionNameCustomFieldId], [b].[RootFolderId], [b].[TenantId], [b].[Type], [b].[WebHookId]
FROM [bbTaskManagers] AS [b]
WHERE (CASE
    WHEN CAST(0 AS bit) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END = @__ef_filter__IsMustHaveTenantFilterEnabled_1) AND (([b].[IsDeleted] <> CAST(1 AS bit)) OR ([b].[IsDeleted] <> @__ef_filter__IsSoftDeleteFilterEnabled_2))',N'@__ef_filter__IsMustHaveTenantFilterEnabled_1 bit,@__ef_filter__IsSoftDeleteFilterEnabled_2 bit',@__ef_filter__IsMustHaveTenantFilterEnabled_1=0,@__ef_filter__IsSoftDeleteFilterEnabled_2=1

which does not run and throwing the same error. So i wonder why the ef core is translating it wrong to sql. Did i do sth wrong while i am upgrading? Is that some kind of reference issue? Any help would be appreciated.Thank you.

Go to accepted answer
6 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team

    hi @cangunaydin May be related to this https://github.com/aspnet/EntityFrameworkCore/issues/19444

    Can you share the code of your TaskManager class?

  • User Avatar
    0
    cangunaydin created

    Hello @maliming here is the code for TaskManager class.

    i looked at the issue that you have mentioned i have tried removing all data annotations but it still gives the same error. I don't think the problem is related with that. It can be related with DisableFilter maybe. I will try couple of more things. Do you have any suggestion that can be useful except the issue you have posted.

    
using ByteBrick.BookAndAd.Offers;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Abp.Domain.Entities.Auditing;
using Abp.Domain.Entities;
using System.Collections.Generic;
using System.Linq;
using Abp.UI;

namespace ByteBrick.BookAndAd.TaskManagers
{
    [Table("bbTaskManagers")]
    public class TaskManager : FullAuditedEntity, IMustHaveTenant
    {
        public int TenantId { get; set; }


        [Required]
        [StringLength(TaskManagerConsts.MaxBearerTokenLength, MinimumLength = TaskManagerConsts.MinBearerTokenLength)]
        public virtual string BearerToken { get; set; }

        public virtual TaskManagerType Type { get; set; }

        /// <summary>
        /// Which offer status will create task
        /// </summary>
        public virtual OfferStatus OfferStatus { get; set; }

        public virtual string WebHookId { get; set; }

        public virtual string AccountName { get; set; }

        /// <summary>
        /// Wrike accountId
        /// </summary>
        [Required]
        [StringLength(TaskManagerConsts.MaxAccountIdLength, MinimumLength = TaskManagerConsts.MinAccountIdLength)]
        public virtual string AccountId { get; set; }

        /// <summary>
        /// Wrike rootFolderId
        /// </summary>
        [Required]
        [StringLength(TaskManagerConsts.MaxFolderIdLength, MinimumLength = TaskManagerConsts.MinFolderIdLength)]
        public virtual string RootFolderId { get; set; }

        /// <summary>
        /// Wrike folderId
        /// </summary>
        [Required]
        [StringLength(TaskManagerConsts.MaxFolderIdLength, MinimumLength = TaskManagerConsts.MinFolderIdLength)]
        public virtual string FolderId { get; set; }

        #region CustomFieldProperties
        /// <summary>
        /// Wrike custom field ID for offerIds of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string OfferIdCustomFieldId { get; set; }
        /// <summary>
        /// Wrike custom field ID for company names of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string CompanyNameCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for offer names of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string OfferNameCustomFieldId { get; set; }
        /// <summary>
        /// Wrike custom field ID for startdate of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string OfferStartDateCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for endDate of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string OfferEndDateCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for LastCalledCustomField of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string LastCalledCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for CallCountCustomField of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string CallCountCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for DescriptionCustomField of tasks
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string DescriptionCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for production names
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ProductionNameCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for ClientSentContentCustom names
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ClientSentContentCustomFieldId { get; set; }


        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ContentSentToClientCustomFieldId { get; set; }

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ClientApprovedContentCustomFieldId { get; set; }

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ContentConnectedToScreenCustomFieldId { get; set; }

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string MediaTypeCustomFieldId { get; set; }

        /// <summary>
        /// Wrike custom field ID for media length names
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string MediaLengthCustomFieldId { get; set; }
        /// <summary>
        /// Wrike is delivered ID to understand if the whole task is completed or partially completed.
        /// </summary>

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string IsDeliveredCustomFieldId { get; set; }

        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]
        public virtual string ProductionIdCustomFieldId { get; set; }
        [StringLength(TaskManagerConsts.MaxCustomFieldIdLength, MinimumLength = TaskManagerConsts.MinCustomFieldIdLength)]

        public virtual string OfferItemDateIdCustomFieldId { get; set; }
        #endregion







        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string NewStatusId { get; set; }

        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string CollectionCompletedStatusId { get; set; }

        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string AssignedToContentManagerStatusId { get; set; }

        /// <summary>
        /// Content creation in progress
        /// </summary>
        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string ContentCreationInProgressStatusId { get; set; }

        /// <summary>
        /// Content creation completed
        /// </summary>
        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string CompletedStatusId { get; set; }

        [Required]
        [StringLength(TaskManagerConsts.MaxStatusIdLength, MinimumLength = TaskManagerConsts.MinStatusIdLength)]
        public virtual string BookAndAdDownloadedStatusId { get; set; }


        public virtual ICollection<TaskManagerUser> TaskManagerUsers { get; set; }

        /// <summary>
        /// En son hangi ContentManager'a task assign edildi
        /// </summary>
        public string LastTaskAssignedContentManagerId { get; set; }

        public TaskManagerUser GetNextContentManagerToAssignTask()
        {
            TaskManagerUser retVal = null;

            var contentManagers = TaskManagerUsers.Where(tmu => tmu.Type == TaskManagerUserType.ContentManager).OrderBy(tmu => tmu.Name).ToList();

            if (contentManagers == null || contentManagers.Count < 1)
            {
                throw new UserFriendlyException("Could not find any content manager.");
            }

            //eðer en son iþ atanamýþ adam Id si bulamýyorsan eline geçen ilk adamý dön
            if (string.IsNullOrWhiteSpace(LastTaskAssignedContentManagerId))
            {
                retVal = contentManagers.First();
            }

            //en son task atanmýþ olan content manager'ý getir
            var lastTaskAssignedContentManager = contentManagers.FirstOrDefault(cm => cm.ExternalUserId == LastTaskAssignedContentManagerId);

            //eðer en sonra task atanmýþ kullanýcý yok ise (silinmiþ olabilir vs.)
            if (lastTaskAssignedContentManager == null)
            {
                //ilk kullanýcýyý dön
                retVal = contentManagers.First();
            }

            IEnumerator<TaskManagerUser> enumerator = contentManagers.GetEnumerator();
            //enumerator ilk itema bakýyor, son assign edilen adama kadar getir
            while (enumerator.MoveNext())
            {
                //eðer son assign edilen adama geldiysen bir sonraki adamýn id yi return et
                if (enumerator.Current.ExternalUserId == LastTaskAssignedContentManagerId)
                {
                    if (enumerator.MoveNext())
                    {
                        retVal = enumerator.Current;
                        break;
                    }
                    else
                    {
                        //eðer son assign ettiðin adamdan sonra baþkasý yoksa baþa dön, baþtaki adamýn id yi return et
                        enumerator.Reset();
                        enumerator.MoveNext();
                        retVal = enumerator.Current;
                        break;
                    }
                }
            }

            return retVal;
        }


        [NotMapped]
        public string MaterialCollectorId
        {
            get
            {
                return TaskManagerUsers
                .First(tmu => tmu.Type == TaskManagerUserType.MaterialCollector).ExternalUserId;
            }
        }

        public List<string> ContentManagerIds
        {
            get
            {
                return TaskManagerUsers
                .Where(tmu => tmu.Type == TaskManagerUserType.ContentManager)
                .Select(tmu => tmu.ExternalUserId).ToList();
            }
        }

        public void SetCustomFieldProperty(string propertyName, string value)
        {
            GetType().GetProperty(propertyName).SetValue(this, value);
        }

        public string GetCustomFieldPropertyValue(string propertyName)
        {
            return (string)GetType().GetProperty(propertyName).GetValue(this);
        }

        /// <summary>
        /// Workflow içerisindeki idleri ve CustomFieldId leri kopyalar
        /// </summary>
        /// <param name="existingTaskManager"></param>
        public void GetValuesFromExistingTaskManager(TaskManager existingTaskManager)
        {
            var myProperties = GetType().GetProperties();
            foreach (var myProperty in myProperties)
            {
                if (myProperty.Name.EndsWith("CustomFieldId"))
                {
                    myProperty.SetValue(this,
                    existingTaskManager.GetType().GetProperty(myProperty.Name).GetValue(existingTaskManager));
                }
                else if (myProperty.Name.EndsWith("StatusId"))
                {
                    myProperty.SetValue(this,
                    existingTaskManager.GetType().GetProperty(myProperty.Name).GetValue(existingTaskManager));
                }
            }
        }
    }
}
  • User Avatar
    0
    cangunaydin created

    Hello, and another bit of information if you change the class TaskManager from IMustHaveTenant to IMayHaveTenant you don't get any errors. And what is weird is you don't get any errors when you call it from an appservice but if you call it from background thread. Now i think it is working differently depending on if you have a session or not.

  • User Avatar
    0
    cangunaydin created

    and it works when you set tenant id inside the background thread like this.

            [UnitOfWork]
        public void RefreshWebHookForAllTenants()
        {
            using (CurrentUnitOfWork.SetTenantId(2))
            {
                using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.MayHaveTenant, AbpDataFilters.MustHaveTenant))
                {
                    var allTaskManagers = _taskManagerRepository.GetAll().Where(o => !o.IsDeleted).ToList();

                    foreach (var taskManager in allTaskManagers)
                    {
                        var wrikeTaskManagerClient = new WrikeTaskManagerClient(taskManager);
                        wrikeTaskManagerClient.ActivateWebhookIfSuspended().Wait();
                    }
                }

            }

        }
  • User Avatar
    0
    maliming created
    Support Team

    hi cangunaydin I will try to reproduce your problem and then find a solution, Thanks for the explanation.

    -- update

    Did not reproduce the problem you encountered, can you use the zero demo project to reproduce the problem and share it to me? [email protected]

  • User Avatar
    0
    cangunaydin created

    Hello @maliming sorry this was my bad. i forgot that i have override the filters in dbcontext. you can close this issue. Thank you so much for the help.

Assignee
No one
Labels
Non yet