Open Closed

must directly specify foreign keys on model #2051


0
jonas452 created

Hi,

I'm running against a bug// error in my code i can't figure out.

I have two classes related to each other. Person and User(aspnetmodulezero class user) User is named PUserAccount in the class Person

public class Person : FullAuditedEntity,IMustHaveTenant
    {
        public const int MaxNameLength = 32;
        public const int MaxFirstnameLength = 32;
        public const int MaxEmailAddressLength = 255;

        public virtual int TenantId { get; set; }
        [Required]
        [MaxLength(MaxNameLength)]
        public virtual string Name { get; set; }
        [Required]
        [MaxLength(MaxFirstnameLength)]
        public virtual string FirstName { get; set; }
        public virtual string PhoneNr { get; set; }
        public virtual User **PUserAccount**{ get; set; }
        [Required]  
        [MaxLength(MaxEmailAddressLength)]      
        public virtual string Email { get; set; }

        public Person()
        {

        }       
    }

In a certain point in my application i let the admin user link a person to a User. A person can exist without a user. To achieve this i have created a domain service.

public class PersonManager : EmployeePlannerSPADomainServiceBase, IPersonManager
    {
        private readonly IRepository<Person> _personRepository;
        private readonly UserManager _userManager;
        private readonly RoleManager _roleManager;

        public PersonManager(IRepository<Person> personRepository, 
                             UserManager userManager,
                             RoleManager roleManager)
        {
            _personRepository = personRepository;
            _userManager = userManager;
            _roleManager = roleManager;
        }

        public Person GetById(int id)
        {
            return _personRepository.GetAll().Where(p => p.Id == id).FirstOrDefault();
        }

        public async Task CreateAsync(Person pers)
        {
            await _personRepository.InsertAsync(pers);
        }

        public async Task<User> CreateNewUserFromPersonAsync(Person p)
        {
            //Get standard role - Tenant id should get injected in this manager
            List<Role> roles = _roleManager.Roles.Where(r =>r.Name == StaticRoleNames.Tenants.User).ToList();
            if (roles.Count == 1)
            {
                User newU = new User()
                {
                    UserName = p.Email,
                    Name = p.Name,
                    Surname = p.FirstName,
                    EmailAddress = p.Email,                    
                    IsEmailConfirmed = false,
                    ShouldChangePasswordOnNextLogin = true,
                    IsActive = false
                };
                await _userManager.CreateAsync(newU, "123qwe");                

                await UpdateAsync(p);

                return p.PUserAccount = newU;
            }
            else
            {
                throw new ApplicationException("No suitable standard role found for new user ");
            }            
        }

        public async Task DeleteByIdAsync(int Id)
        {            
            await _personRepository.DeleteAsync(Id);
        }

        public async Task LinkPersonToUserAsync(Person p, User u)
        {
            //Check if user has been linked before
            var lPers = _personRepository.GetAll().Where(
                    per => per.PUserAccount != null &&
                           per.PUserAccount.Id == u.Id                    
                ).ToList();
            
            if (lPers.IsNullOrEmpty())
            {
                p.PUserAccount = u;
                await UpdateAsync(p);
            }
            else
            {
                throw new ApplicationException("That user account is already linked to an other person");
            }            
        }

        public async Task UpdateAsync(Person pers)
        {
            await _personRepository.UpdateAsync(pers);
        }
    }

In my domain service you see a method LinkPersonToUserAsync. When executed i receive a exception on following line

per => per.PUserAccount != null &&
                           per.PUserAccount.Id == u.Id

The error : ERROR 2016-12-01 21:34:07,433 [6 ] nHandling.AbpApiExceptionFilterAttribute - An error occurred while preparing the command definition. See the inner exception for details. System.Data.Entity.Core.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.ApplicationException: FK Constriant not found for association 'EmployeePlannerSPA.EntityFramework.Person_PUserAccount' - must directly specify foreign keys on model to be able to apply this filter at EntityFramework.DynamicFilters.DynamicFilterQueryVisitorCSpace.Visit(DbPropertyExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbPropertyExpression.Accept[TResultType](DbExpressionVisitor1 visitor) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpression(DbExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.Visit(DbPropertyExpression expression) at EntityFramework.DynamicFilters.DynamicFilterQueryVisitorCSpace.Visit(DbPropertyExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbPropertyExpression.Accept[TResultType](DbExpressionVisitor1 visitor) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpression(DbExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitBinary(DbBinaryExpression expression, Func3 callback) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.Visit(DbComparisonExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbComparisonExpression.Accept[TResultType](DbExpressionVisitor1 visitor) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpression(DbExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.Visit(DbFilterExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbFilterExpression.Accept[TResultType](DbExpressionVisitor1 visitor) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpression(DbExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpressionBinding(DbExpressionBinding binding) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.VisitExpressionBindingEnterScope(DbExpressionBinding binding) at System.Data.Entity.Core.Common.CommandTrees.DefaultExpressionVisitor.Visit(DbProjectExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor1 visitor) at EntityFramework.DynamicFilters.DynamicFilterInterceptor.TreeCreated(DbCommandTreeInterceptionContext interceptionContext)

I'm not an expert on Entity framework but any help to filter on a foreign key for independent association would be appreciated. I really don't want to create a mapping with fluent mapi and define my own constraint(or create navigation properties in my poco). I have checked the database and the constraint has been autmaticaly created by EF

Currently i'm on version aspnetzero : v1.13.0.0

Thanks, Jonas


6 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi,

    Your issue seems to be related to EntityFramework.DynamicFilters library we use. You can check this issue <a class="postlink" href="https://github.com/jcachat/EntityFramework.DynamicFilters/issues/78">https://github.com/jcachat/EntityFramew ... /issues/78</a> It is still open but I think they offer a solution.

  • 0
    jonas452 created

    I spent a significant amount of time trying to solve that problem and could not find a solution. So the only way to make that work is to define the ID properties in your models.

    I don't want to place a foreignkey on my model. breaks the seperate layer design. So besides that solution is there a work around ? a certain mapping i can do in fluent api?

  • 0
    jonas452 created

    As one must continue it's development i have defined a navigation property on my model. Something i really didn't want to do.

    modelBuilder.Entity<Person>().Property(p => p.PUserAccount_Id).IsOptional().HasColumnName("PUserAccount_Id");
    modelBuilder.Entity<Person>().HasOptional(p => p.PUserAccount).WithMany().HasForeignKey(pers => pers.PUserAccount_Id);
    

    But hey, what can you do... If someone finds a better solution for this in the future, i'd love to hear it.

    @ ismcagdas - Thx for the link ;)

  • 0
    exlnt created

    <cite>Jonas452: </cite> As one must continue it's development i have defined a navigation property on my model. Something i really didn't want to do.

    modelBuilder.Entity<Person>().Property(p => p.PUserAccount_Id).IsOptional().HasColumnName("PUserAccount_Id");
    modelBuilder.Entity<Person>().HasOptional(p => p.PUserAccount).WithMany().HasForeignKey(pers => pers.PUserAccount_Id);
    

    But hey, what can you do... If someone finds a better solution for this in the future, i'd love to hear it.

    @ ismcagdas - Thx for the link ;)

    @Jonas452 - Would you mind sharing the specifics on how you resolved this error? I am getting the exact same error message on several of my entities. I do not use the "ForeignKey" annotation in my models. Also, where you do place the modelBuilder.Entity code you are showing above, I cannot find it anywhere in the solution?

  • 0
    jonas452 created

    Yeah No Prob.

    regular class : src / SixByte.JobByte.Core / Scheduler /

    namespace SixByte.JobByte.Scheduler
    {
        public abstract class Day : FullAuditedEntity,IMustHaveTenant
        {        
            public virtual int TenantId { get; set; }
            [Required]
            public virtual DateTime OnDate { get; set; }
            public virtual string Description { get; set; }
            [Required]
            public virtual bool Locked { get; set; }
            //public virtual WorkSchedule WorkSchedule { get; set; }
    
            //Foreign key
            public virtual int WorkScheduleId { get; set; }
        }
    }
    

    And then in your dbcontext : src / SixByte.JobByte.EntityFramework / EntityFramework / JobByteDbContext.cs Do note: I made an extra addition for switching to mariaDB/mysql. Was needed because mysql doesn't support schemas and i always will group my tables. As you can see i changed the schema's of aspnetzero and aspnetboilerplate. = no dbo schema

    namespace SixByte.JobByte.EntityFramework
    {
        /* Constructors of this DbContext is important and each one has it's own use case.
         * - Default constructor is used by EF tooling on development time.
         * - constructor(nameOrConnectionString) is used by ABP on runtime.
         * - constructor(existingConnection) is used by unit tests.
         * - constructor(existingConnection,contextOwnsConnection) can be used by ABP if DbContextEfTransactionStrategy is used.
         * See http://www.aspnetboilerplate.com/Pages/Documents/EntityFramework-Integration for more.
         */
    
        [DbConfigurationType(typeof(JobByteDbConfiguration))]
        public class JobByteDbContext : AbpZeroDbContext<Tenant, Role, User>
        {
            private const DbSysType _DBTYPE = DbSysType.MSSQL;
    
            /* Define an IDbSet for each entity of the application */
            private const string SCHEMA_ABP = "ABP";
            private const string SCHEMA_ASPZERO = "AZF";
            private const string DBSCHEMA_SCHEDULAR = "SCHED";
            private const string DBSCHEMA_COMPANY = "COMP";
    
    
            public virtual IDbSet<BinaryObject> BinaryObjects { get; set; }
    
            public virtual IDbSet<Friendship> Friendships { get; set; }
    
            public virtual IDbSet<ChatMessage> ChatMessages { get; set; }
    
            public virtual IDbSet<TenantCompany> TenantCompanies { get; set; }
            public virtual IDbSet<Person> Persons { get; set; }
            public virtual IDbSet<Employee> Employees { get; set; }
            public virtual IDbSet<WorkActivity> WorkActivities { get; set; }
            public virtual IDbSet<WorkSchedule> WorkSchedules { get; set; }
            public virtual IDbSet<Day> Days { get; set; }
            public virtual IDbSet<SickDay> SickDays { get; set; }
            public virtual IDbSet<WorkDay> WorkDay { get; set; }
            public virtual IDbSet<VacationDay> VacationDay { get; set; }
            public virtual IDbSet<WorkTimePeriod> WorkTimePeriod { get; set; }
            public virtual IDbSet<VacationTimePeriod> VacationTimePeriod { get; set; }
            public virtual IDbSet<TimePeriod> TimePeriods { get; set; }
            public virtual IDbSet<Holiday> Holidays { get; set; }
            public virtual IDbSet<EmployeeWorkActivity> EmployeeWorkActivities { get; set; }
            public virtual IDbSet<WorkEvent> WorkEvents { get; set; }
            public virtual IDbSet<EventType> EventTypes { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
    
                //Basic Application Settings
                CreateBaseTables(modelBuilder);
    
                //Application Settings    
                CreateApplicationTables(modelBuilder);
    
            }
    
            protected void CreateBaseTables(DbModelBuilder modelBuilder)
            {
                TableCreationBasedOnDbType<AuditLog>(_DBTYPE, modelBuilder, SCHEMA_ABP, "AuditLogs");
                TableCreationBasedOnDbType<BackgroundJobInfo>(_DBTYPE, modelBuilder, SCHEMA_ABP, "BackgroundJobs");
                TableCreationBasedOnDbType<Edition>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Editions");
                TableCreationBasedOnDbType<ApplicationLanguage>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Languages");
                TableCreationBasedOnDbType<ApplicationLanguageText>(_DBTYPE, modelBuilder, SCHEMA_ABP, "LanguageTexts");
                TableCreationBasedOnDbType<NotificationInfo>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Notifications");
                TableCreationBasedOnDbType<NotificationSubscriptionInfo>(_DBTYPE, modelBuilder, SCHEMA_ABP, "NotificationSubscriptions");
                TableCreationBasedOnDbType<OrganizationUnit>(_DBTYPE, modelBuilder, SCHEMA_ABP, "OrganizationUnits");
                TableCreationBasedOnDbType<Role>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Roles");
                TableCreationBasedOnDbType<Setting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Settings");
                TableCreationBasedOnDbType<TenantNotificationInfo>(_DBTYPE, modelBuilder, SCHEMA_ABP, "TenantNotifications");
                TableCreationBasedOnDbType<Tenant>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Tenants");
                TableCreationBasedOnDbType<UserAccount>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserAccounts");
                TableCreationBasedOnDbType<UserClaim>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserClaims");
                TableCreationBasedOnDbType<UserLoginAttempt>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserLoginAttempts");
                TableCreationBasedOnDbType<UserNotificationInfo>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserNotifications");
                TableCreationBasedOnDbType<UserOrganizationUnit>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserOrganizationUnits");
                TableCreationBasedOnDbType<UserRole>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserRoles");
                TableCreationBasedOnDbType<User>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Users");
                TableCreationBasedOnDbType<UserLogin>(_DBTYPE, modelBuilder, SCHEMA_ABP, "UserLogins");
                TableCreationBasedOnDbType<EditionFeatureSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Features");
                TableCreationBasedOnDbType<FeatureSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Features");
                TableCreationBasedOnDbType<TenantFeatureSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Features");
                TableCreationBasedOnDbType<UserPermissionSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Permissions");
                TableCreationBasedOnDbType<PermissionSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Permissions");
                TableCreationBasedOnDbType<RolePermissionSetting>(_DBTYPE, modelBuilder, SCHEMA_ABP, "Permissions");
    
                TableCreationBasedOnDbType<BinaryObject>(_DBTYPE, modelBuilder, SCHEMA_ASPZERO, "BinaryObjects");
                TableCreationBasedOnDbType<ChatMessage>(_DBTYPE, modelBuilder, SCHEMA_ASPZERO, "ChatMessages");
                TableCreationBasedOnDbType<Friendship>(_DBTYPE, modelBuilder, SCHEMA_ASPZERO, "Friendships");
            }
    
            protected void CreateApplicationTables(DbModelBuilder modelBuilder)
            {
                TableCreationBasedOnDbType<TenantCompany>(_DBTYPE, modelBuilder, DBSCHEMA_COMPANY, "TenantCompanies");
                TableCreationBasedOnDbType<Person>(_DBTYPE, modelBuilder, DBSCHEMA_COMPANY, "Persons");
                TableCreationBasedOnDbType<Employee>(_DBTYPE, modelBuilder, DBSCHEMA_COMPANY, "Employees");
                TableCreationBasedOnDbType<EmployeeWorkActivity>(_DBTYPE, modelBuilder, DBSCHEMA_COMPANY, "EmployeeActivities");
    
                TableCreationBasedOnDbType<Day>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "Days");
                TableCreationBasedOnDbType<VacationTimePeriod>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "VacationTimePeriods");
                TableCreationBasedOnDbType<WorkTimePeriod>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkTimePeriods");
                TableCreationBasedOnDbType<VacationDay>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "VacationDays");
                TableCreationBasedOnDbType<WorkDay>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkDays");
                TableCreationBasedOnDbType<Holiday>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "Holidays");
                TableCreationBasedOnDbType<SickDay>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "SickDays");
                TableCreationBasedOnDbType<WorkEmployeeEvent>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkEmployeeEvents");
                TableCreationBasedOnDbType<WorkOwnCarTransportionEvent>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkOwnCarTransportionEvents");
                TableCreationBasedOnDbType<WorkSchedule>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkSchedules");
                TableCreationBasedOnDbType<WorkEvent>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkEvents");
                TableCreationBasedOnDbType<TimePeriod>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "TimePeriods");
                TableCreationBasedOnDbType<ExpenseType>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "ExpenseTypes");
                TableCreationBasedOnDbType<ExpenseNota>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "ExpenseNotas");
                TableCreationBasedOnDbType<ExpenseImage>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "ExpenseImages");
                TableCreationBasedOnDbType<EventType>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "EventTypes");
                TableCreationBasedOnDbType<WorkActivity>(_DBTYPE, modelBuilder, DBSCHEMA_SCHEDULAR, "WorkActivities");
            }
    
            public JobByteDbContext()
                : base(GetConnectionString())
            {
    
            }
    
            private static string GetConnectionString()
            {
                //Notice that; this logic only works on development time.
                //It is used to get connection string from appsettings.json in the Web project.
    
                var configuration = AppConfigurations.Get(
                    WebContentDirectoryFinder.CalculateContentRootFolder()
                    );
    
                return configuration.GetConnectionString(
                    JobByteConsts.ConnectionStringName
                    );
            }
    
            public JobByteDbContext(string nameOrConnectionString)
                : base(nameOrConnectionString)
            {
    
            }
    
            public JobByteDbContext(DbConnection existingConnection)
                : base(existingConnection, false)
            {
    
            }
    
            public JobByteDbContext(DbConnection existingConnection, bool contextOwnsConnection)
                : base(existingConnection, contextOwnsConnection)
            {
    
            }
    
            private enum DbSysType
            {
                MSSQL,
                MYSQL
            }
    
            private void TableCreationBasedOnDbType<TEntityType>(DbSysType type, DbModelBuilder mB, string schema, string table)
                where TEntityType : class
            {
                switch (type)
                {
                    case DbSysType.MSSQL:
                        {
                            mB.Entity<TEntityType>().ToTable(table, schema);
                            break;
                        }
                    case DbSysType.MYSQL:
                        {
                            mB.Entity<TEntityType>().ToTable(schema + "_" + table);
                            break;
                        }
                }
            }
        }
    
        public class JobByteDbConfiguration : DbConfiguration
        {
            public JobByteDbConfiguration()
            {
                SetProviderServices(
                    "System.Data.SqlClient",
                    System.Data.Entity.SqlServer.SqlProviderServices.Instance
                );
            }
        }
    
  • 0
    exlnt created

    @Jonas452 Thanks for sharing, really appreciate it!