I've got 2 tables:
InboxEvent
int Id
Visitor Owner
[ForeignKey("Owner")]
string OwnerId
Visitor CausingUser
[ForeignKey("CausingUser")]
string CausingUserId
Visitor
string Id
Both OwnerId and CausingUserId point to the Visitor table, yet in Sql Server Entity Framework has gone in and created the InboxEvent table like so:
FK OwnerId
FK CausingUserId
FK Visitor_Id
And, sure enough, it's invented a column:
NVarChar(128) Visitor_Id
OK... odd... Well simple enough, I'll kill the stray column with a Manual Migration:
public partial class KillInboxEventVisitor_Id : DbMigration
{
public override void Up()
{
DropForeignKey("dbo.InboxEvent", "Visitor_Id", "dbo.AspNetUsers");
DropIndex("dbo.InboxEvent", "IX_Visitor_Id");
DropColumn("dbo.InboxEvent", "Visitor_Id");
}
Great, it's gone! And now... every time I try to get EF to save an InboxEvent:
db.InboxEvents.Add(new InboxEvent {...
await db.SaveChangesAsync();
EF explodes with
SqlException: Invalid column name 'Visitor_Id'.
How do I tell the underlying EF model this imagined column just does not exist?
This is plain Entity Framework 6.x, with Asp.Net/OWIN Identity Framework, not the newer EF7/Core.
I was asked to show the full classes. Happy to, but, hold on to your butts, they were abbreviated above for a reason:
public class InboxEvent : IOwnerId
{
public int Id { get; set; }
[Index("IX_UserRead", 2)]
public DateTime CreatedOn { get; set; }
/// <summary>
/// The user whose Inbox this event is deposited into.
/// </summary>
[ForeignKey("Owner"), Required]
[MaxLength(128)]
[Index("IX_UserRead", 0), Index]
public string OwnerId { get; set; }
public Visitor Owner { get; set; }
[Index("IX_EventType_RelatedId1", 0)]
public InboxEventType EventType { get; set; }
public Posting.PostCore Post { get; set; }
[ForeignKey("Post")]
public int? PostId { get; set; }
public Posting.PostReply Reply { get; set; }
[ForeignKey("Reply")]
public int? ReplyId { get; set; }
[Index("IX_UserRead", 1)]
public bool IsRead { get; set; }
/// <summary>
/// The user, if any, that caused/triggered this event. For example if A upvotes B, this is A, and B is Visitor.
/// </summary>
[ForeignKey("CausingUser")]
[MaxLength(128)]
public string CausingUserId { get; set; }
public Visitor CausingUser { get; set; }
[Index]
[Index("IX_EventType_RelatedId1", 1)]
public int RelatedId1 { get; set; }
public int RelatedId2 { get; set; }
public InboxEvent()
{
CreatedOn = DateTime.UtcNow;
}
}
public class Visitor : IdentityUser<string, LoginIdentity, StandardUserRole, IdentityUserClaim>, IVisitor
{
[MaxLength(22), Column(TypeName = "Char"), Index]
public string Uid { get; set; }
[MaxLength(300), Index]
public string FirstName { get; set; }
[MaxLength(300), Index]
public string LastName { get; set; }
public DateTime CreatedOn { get; set; }
public DateTime MemberOn { get; set; }
public int Invites { get; set; }
public int Score { get; set; }
public bool ShouldReceiveReplyNotifications { get; set; }
public DateTime LastEmailNotified { get; set; }
[MaxLength(100), Column(TypeName="VarChar")]
public string ProfilePic { get; set; }
public ICollection<UserPhoto> UserPhotos { get; set; }
public StaticImage StaticImage { get; set; }
// BioViewModel
[MaxLength(100)]
public string Nickname { get; set; }
[MaxLength(400)] // TODO Normalize
public string Disciplines { get; set; }
[MaxLength(1000)]
public string MissionStatement { get; set; }
[MaxLength(100)]
public string Tagline { get; set; }
// SkillsViewModel
[MaxLength(400)]
public string SkillsKnown { get; set; }
[MaxLength(400)]
public string SkillsToLearn { get; set; }
// PrefsViewModel
[MaxLength(10)]
public string PhoneCountryCode { get; set; }
//[MaxLength(20)]
//public string PhoneNumber { get; set; } // Already in base OWIN User model
public ChatService ChatService { get; set; }
[MaxLength(100)]
public string ChatHandle { get; set; }
public byte PrefIpOpenness { get; set; }
public byte PrefNonProfit { get; set; }
public byte PrefMature { get; set; }
public PrivacyLevel PrivacyNameCountryCity { get; set; }
public PrivacyLevel PrivacyBio { get; set; }
public PrivacyLevel PrivacySites { get; set; }
public PrivacyLevel PrivacyCompanies { get; set; }
public PrivacyLevel PrivacySkills { get; set; }
public PrivacyLevel PrivacyContactInfo { get; set; }
public PrivacyLevel PrivacyCollabPrefs { get; set; }
public bool WantMonthlyNewsletter { get; set; }
public bool WantToMentor { get; set; }
public bool WantToVetProjects { get; set; }
public bool WantToReviewProjects { get; set; }
public bool WantToGiveFeedback { get; set; }
// CommitmentViewModel
public CommitmentChoice CommitmentChoice { get; set; }
[MaxLength(1000)]
public string CommitmentText { get; set; }
public ICollection<CountryZip> CountryZips { get; set; }
public ICollection<Org> Orgs { get; set; }
public ICollection<UserSite> UserSites { get; set; }
public ICollection<DraftData> Drafts { get; set; }
public ICollection<Visitor_Team> Teams { get; set; }
public ICollection<InboxEvent> Inbox { get; set; }
public ICollection<SubscribedPost> PostSubscriptions { get; set; }
public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<Visitor, string> manager)
{
// Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
// Add custom user claims here
return userIdentity;
}
public Visitor()
{
CreatedOn = (DateTime)SqlDateTime.MinValue;
MemberOn = (DateTime)SqlDateTime.MinValue;
LastEmailNotified = (DateTime)SqlDateTime.MinValue;
}
}
Db does have an OnModelsCreating though it is unlikely to be relevant here:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
var entity = modelBuilder.Entity<Visitor>();
// By default this is nvarchar max? Uh?
entity.Property(v => v.PhoneNumber).HasMaxLength(20);
}
There are three associations between Visitor and InboxEvent. The two you showed initially:
public class InboxEvent : IOwnerId
{
...
[ForeignKey("Owner"), Required]
public string OwnerId { get; set; }
public Visitor Owner { get; set; }
[ForeignKey("CausingUser")]
public string CausingUserId { get; set; }
public Visitor CausingUser { get; set; }
...
}
And:
public class Visitor : IdentityUser<string, ...
{
...
public ICollection<InboxEvent> Inbox { get; set; }
...
}
Without further notice, EF will assume that these associations are independent of each other: it will create three FKs, of which Visitor_Id is for the latter association.
You probably want InboxEvent.Owner and Visitor.Inbox to be two ends of one association, but EF doesn't know that and it's not going to guess. You have to make it explicit, for example by using the [InverseProperty] attribute:
public class InboxEvent : IOwnerId
{
...
[ForeignKey("Owner"), Required]
public string OwnerId { get; set; }
[InverseProperty("Inbox")]
public Visitor Owner { get; set; }
...
}