CODEWITHSUNDEEP
c#entity-frameworkdata-modeling
Edward B.
Edward B.

Reputation: 417

How to properly model a "selected item" relationship in SQL databases?

I am making a database using the Entity Framework. Right now, my problem is:

I've got the ASP.NET User entity (ApplicationUser). I also have another user entity (LynxUser). The relationship between them is: Every ApplicationUser can open one or more LynxUser accounts. Every ApplicationUser must be logged into one and only one of them at a time. When registering an ApplicationUser account, a LynxUser is registered too. I don't need to worry about security with the LynxUser accounts, the only requirement is that an ApplicationUser may use only their own accounts.

I have difficulties modelling this in a way that EF will like. Right now, my code looks like (edited for brevity):

public class ApplicationUser : IdentityUser {

    [Display(Name = "Active User", AutoGenerateField = false), InverseProperty("SelecterUser")]
    public LynxUser ActiveUser { get; set; }

    [InverseProperty("ApplicationUser")]
    public ICollection<LynxUser> LynxUsers { get; set; }
}

and

public class LynxUser {
    [Key, Display(Name = "User name", AutoGenerateField = true), StringLength(50, MinimumLength = 1),
        Editable(false, AllowInitialValue = true)]
    public string Name { get; set; }

    [Required, Display(Name = "Site User", AutoGenerateField = false), Editable(false, AllowInitialValue = true),
        InverseProperty("LynxUsers")]
    public virtual ApplicationUser ApplicationUser { get; set; }

    /// <summary>
    /// This is required to establish a one-to-one transferrable relationship as the active user.
    /// </summary>
    [Display(Name = "Selecter User", AutoGenerateField = false), InverseProperty("ActiveUser")]
    public virtual ApplicationUser SelecterUser { get; set; }

}

The problem seems to be the recursive relationship between these two entities. How can I better model this? I absolutely do need to record the last LynxUser every ApplicationUser was logged in to.

EDIT: Problem solved, see my answer.

Upvotes: 1

Views: 59

Answers (2)

Edward B.
Edward B.

Reputation: 417

I ended up adding another entity, LynxLogin, that tracks the ApplicationUser to LynxUser logins, along with a DateTime. I still had some problems actually generating the new objects and writing them to the database (I had to manually set db.Entry(user).State = EntityState.Unchanged; to prevent db.SaveChanges() from complaining that the user already exists.

Upvotes: 1

Joehl
Joehl

Reputation: 3701

You can just add a "IsActive" - boolean flag to the LynxUser and remove the ActiveUser from the ApplicationUser.

So you just had to set the "IsActive" flag. The ApplicationUser just knows which LynxUsers it have.

Upvotes: 1

Related Questions