CODEWITHSUNDEEP
c#entity-frameworkef-fluent-api
agascon
agascon

Reputation: 785

Problem with master/detail tables and Entity Framework

I have a typical master/detail (User / Settings table) table schema (SQL Server) and setup Entity Framework using Fluent API to work with those tables.

I define this as an independent association, so the UserProfileSetting class doesn't include the UserId property, but I understand is correctly mapped in the configuration.

Well, my problem is that when one item of Settings is updated for a profile, at the database level that settings is updated for all users. Basically USER_ID is not considered.

The SQL query produced is this:

UPDATE [dbo].[T_USERPROFILE_SETTING]
SET [VALUE] = @0
WHERE ([KEY] = @1)

Any idea what could be wrong? I guess that if I finally add the UserId property to UserProfileSettings, that will fix the problem, but I wanted to try to fix this without it.

Current code below...

Code updating the data

var entry = profile.Settings.Where(s => s.Key == key).SingleOrDefault();

if (entry != null)
{
    entry.Value = value;
} else {
    var setting = /* Here create a new setting */
    profile.Settings.Add(setting);
}

DataContext.SaveChanges();

Entities:

public partial class UserProfile
{
    [Key]
    public string UserId { get; set; }
    public DateTimeOffset LastLogin { get; set; }
    public ICollection<UserProfileSetting> Settings { get; set; }
}

public class UserProfileSetting
{
    public UserProfileSetting() { }

    public string Key { get; set; }
    public string Value { get; set; }
}

Entity configuration:

public class UserProfileConfiguration : EntityTypeConfiguration<UserProfile>
{
    public UserProfileConfiguration()
    {
        ToTable("T_USERPROFILE");

        HasKey<string>(p => p.UserId);

        Property(p => p.UserId)
            .HasColumnName("USER_ID")
            .HasMaxLength(50)
            .IsUnicode()
            .IsRequired();

        Property(p => p.LastLogin)
            .HasColumnName("LAST_LOGIN_AT")
            .IsRequired();

        HasMany<UserProfileSetting>(p => p.Settings)
            .WithOptional()
            .Map(m => m.MapKey("USER_ID"));
    }
}

public class UserProfileSettingConfiguration : EntityTypeConfiguration<UserProfileSetting>
{
    public UserProfileSettingConfiguration()
    {
        ToTable("T_USERPROFILE_SETTING");

        HasKey(p => p.Key );

        Property(p => p.Key)
            .HasColumnName("KEY")
            .HasMaxLength(50)
            .IsUnicode()
            .IsRequired();

        Property(p => p.Value)
            .HasColumnName("VALUE")
            .IsUnicode()
            .IsRequired();
    }
}

Upvotes: 0

Views: 1002

Answers (2)

agascon
agascon

Reputation: 785

From EF documentation...

When foreign key columns are not included in the model, the association information is managed as an independent object. Relationships are tracked through object references instead of foreign key properties. This type of association is called an independent association. The most common way to modify an independent association is to modify the navigation properties that are generated for each entity that participates in the association.

So, I was wrong. In my code, UserProfile should include UserProfileSetting either as a FK (Just the ID) or as an independent Object.

  • In the 1st case a UserId should be mapped into UserProfileSetting and the navigation property in UserProfile should be changed to...

    HasMany<UserProfileSetting>(p => p.Settings) .WithOptional() .HasForeignKey(s => s.UserId);

  • In the 2nd case, (this is what is called an Independent Association) a new navigation property should be added into UserProfileSetting for UserProfile.

Upvotes: 1

Zapo
Zapo

Reputation: 211

Entity framework maps to relational database and so it must stick with some of it concepts. The main thing here is, that each entity is mapped to a table containing all the records of that entity and it needs some data to distinguish the relation.

Therefore you need to add USER_ID to tell which record is for which user (to define the relation). In other words you need to have it in table and also in C# entity.

I don’t think it is possible in code first to not have the relation property on entity. On the other hand, you can create some extra DTO layer to hide it.

Upvotes: 0

Related Questions