Foreign key issue: EF Core 7 code-first: "Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF."

Question

I'm sure the issue is in OnModelCreating but I'm not sure what's missing. I'm trying to create two DbSets, one for SocialMediaUrls with a foreign key to SocialMediaTypes. When I'm trying to insert a row into SocialMediaUrls, the code is also trying to insert something into SocialMediaTypes which it shouldn't.

Here are my DbSets:

public class SocialMediaUrls
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public Guid? ProfileId { get; set; }

    public SocialMediaTypes? SocialMediaTypes { get; set; }

    [Required]
    public string Url { get; set; } = string.Empty;

    [Required]
    public bool isActive { get; set; }
}

public class SocialMediaTypes
{
    [Key]
    public int SocialMediaTypesId { get; set; }

    [Required]
    public string Name { get; set; }

    public string? Url { get; set; } = string.Empty;
    public string? PlaceholderText { get; set; } = string.Empty;
    public string? IconUrl { get; set; } = string.Empty;

    [Required]
    public bool isPaymentApp { get; set; }

    [Required]
    public bool isActive { get; set; }
}

This is the code that does the update or insert:

public async Task<SocialMediaUrls> AddUpdateProfileSocialMediaAsync(SocialMediaUrls ProfileSocialMedia)
{
    var foundSocialMedia = await _context.SocialMediaUrls.FirstOrDefaultAsync(e => e.Id == ProfileSocialMedia.Id);

    if (foundSocialMedia != null)
    {
        foundSocialMedia.Url = ProfileSocialMedia.Url;
        foundSocialMedia.SocialMediaTypesId = ProfileSocialMedia.SocialMediaTypesId;

        await _context.SaveChangesAsync();

        return foundSocialMedia;
    }
    else
    {
        try
        {
            var addedEntity = await _context.SocialMediaUrls.AddAsync(ProfileSocialMedia);

            await _context.SaveChangesAsync();

            return addedEntity.Entity;
        }
        catch (Exception ex)
        {
            throw;
        }
    }
}

DbContext code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SocialMediaUrls>().HasIndex(s => s.Id).IsUnique(true);

    modelBuilder.Entity<SocialMediaTypes>().HasIndex(s => s.SocialMediaTypesId).IsUnique(true);

    modelBuilder.Entity<SocialMediaTypes>().HasData(
            new SocialMediaTypes { SocialMediaTypesId = 1, Name = "Website", IconUrl= "Website.png", isActive = true, Url="", PlaceholderText="", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 2, Name = "Facebook", IconUrl = "Facebook.png", isActive = true, Url = "www.facebook.com/", PlaceholderText = "link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 3, Name = "Twitter", IconUrl = "Twitter.png", isActive = true, Url = "www.twitter.com/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 4, Name = "Instagram", IconUrl = "Instagram.png", isActive = true, Url = "www.instagram.com/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 5, Name = "YouTube", IconUrl = "YouTube.png", isActive = true, Url = "www.youtube.com/", PlaceholderText = "link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 6, Name = "LinkedIn", IconUrl = "LinkedIn.png", isActive = true, Url = "www.linkedin.com/in/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 7, Name = "Snapchat", IconUrl = "Snapchat.png", isActive = true, Url = "www.snapchat.com/add/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 8, Name = "TikTok", IconUrl = "TikTok.png", isActive = true, Url = "www.tiktok.com/@", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 9, Name = "Twitch", IconUrl = "Twitch.png", isActive = true, Url = "www.twitch.tv/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 10, Name = "Pinterest", IconUrl = "Pinterest.png", isActive = true, Url = "www.pinterest.com/", PlaceholderText = "link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 11, Name = "Vimeo", IconUrl = "Vimeo.png", isActive = true, Url = "www.vimeo.com/", PlaceholderText = "username or link", isPaymentApp = false },
            new SocialMediaTypes { SocialMediaTypesId = 12, Name = "CashApp", IconUrl = "CashApp.png", isActive = true, Url = "www.cash.app/$", PlaceholderText = "username or link", isPaymentApp = true },
            new SocialMediaTypes { SocialMediaTypesId = 13, Name = "Venmo", IconUrl = "Venmo.png", isActive = true, Url = "www.venmo.com/", PlaceholderText = "link", isPaymentApp = true },
            new SocialMediaTypes { SocialMediaTypesId = 14, Name = "PayPal", IconUrl = "PayPal.png", isActive = true, Url = "www.paypal.me/", PlaceholderText = "username or link", isPaymentApp = true }
        );
}

Also tried this:

modelBuilder.Entity<SocialMediaUrls>()
                .HasOne(s => s.SocialMediaTypes)
                .WithMany()
                .OnDelete(DeleteBehavior.Restrict);

Answer 1

It seems that you are posting SocialMediaUrls.SocialMediaTypes too. Usual approach would be to just create a DTO or special model for posting and map between them (this will solve other issues too - like overposting atacks), but as quick-n-dirty solution you can just either create a new SocialMediaUrls instance and fill it:

else
{
    var addedEntity = await _context.SocialMediaUrls.AddAsync(new SocialMediaUrls
    {
        Url = ProfileSocialMedia.Url,
        SocialMediaTypesId = ProfileSocialMedia.SocialMediaTypesId,
        // ...
    });

    await _context.SaveChangesAsync();

    return addedEntity.Entity;
}

or attach SocialMediaTypes:

else
{
    if (ProfileSocialMedia.SocialMediaTypes is not null)
    {
        _context.SocialMediaTypes.Attach(ProfileSocialMedia.SocialMediaTypes);
    }
    var addedEntity = await _context.SocialMediaUrls.AddAsync(ProfileSocialMedia);

    await _context.SaveChangesAsync();

    return addedEntity.Entity;
}