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](DbExpressionVisitor
1 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](DbExpressionVisitor
1 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](DbExpressionVisitor
1 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
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
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
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
<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
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
@Jonas452 Thanks for sharing, really appreciate it!