ASP.NET Core MVC SqlException: Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key in object 'dbo.User'

Question

I get an error when uploading a video:

SqlException: Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key in object 'dbo.User'. The duplicate key value is (831769c6-fc01-4203-a345-0aa12702b95e)

This is my code:

public async Task<IActionResult> Upload(VideoUploadViewModel model)
{
    if (ModelState.IsValid)
    {
        var userId = User.FindFirstValue(ClaimTypes.NameIdentifier);
        var user = await _userRepository.GetUserByIdAsync(userId);

        var video = new Video
        {
            Title = model.Title,
            Description = model.Description,
            DurationSeconds = model.DurationSeconds,
            CategoryId = model.CategoryId,
            User = user,
            UserId = userId
        };

        await _videoRepository.AddVideoAsync(video, model.VideoFile);

        return RedirectToAction("Index");
    }

    var categories = await _categoryRepository.GetAllCategoriesAsync();
    ViewBag.Categories = categories;

    return View(model);
}


public async Task AddVideoAsync(Video video, IFormFile videoFile)
{
    try
    {
        if (videoFile != null && videoFile.Length > 0)
        {
            var fileName = Guid.NewGuid().ToString() + Path.GetExtension(videoFile.FileName);
            var filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "videos", fileName);

            using (var stream = new FileStream(filePath, FileMode.Create))
            {
                await videoFile.CopyToAsync(stream);
            }

            video.FilePath = fileName;
        }

        _context.Videos.Add(video);
        await _context.SaveChangesAsync();
    }
    catch (Exception)
    {
        if (!string.IsNullOrEmpty(video.FilePath))
        {
            var filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "videos", video.FilePath);

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
        }
        throw;
    }
}

public class Video
{
    [Key]
    public int VideoId { get; set; }

    [Required(ErrorMessage = "Поле 'Название видео' обязательно для заполнения")]
    [Display(Name = "Название видео")]
    [StringLength(100, ErrorMessage = "Длина 'Названия' не должна превышать 100 символов")]
    public string Title { get; set; }

    [Required(ErrorMessage = "Поле 'Описание видео' обязательно для заполнения")]
    [Display(Name = "Описание видео")]
    [StringLength(500, ErrorMessage = "Длина 'Описания' не должна превышать 500 символов")]
    public string Description { get; set; }

    [Required(ErrorMessage = "Поле 'Продолжительность видео' обязательно для заполнения")]
    [Display(Name = "Продолжительность видео")]
    [Range(0, int.MaxValue, ErrorMessage = "Значение 'Продолжительность' должно быть неотрицательным")]
    public int DurationSeconds { get; set; }

    [ScaffoldColumn(false)]
    public string FilePath { get; set; }

    [NotMapped]
    [Display(Name = "Файл видео")]
    [Required(ErrorMessage = "Выберите видеофайл")]
    public IFormFile VideoFile { get; set; }

    [ScaffoldColumn(false)]
    public DateTime UploadDate { get; set; }

    [Display(Name = "Категория видео")]
    [Required(ErrorMessage = "Поле 'Категория видео' обязательно для заполнения")]
    public int CategoryId { get; set; }
    [ForeignKey("CategoryId")]
    public Category Category { get; set; }

    public string UserId { get; set; }
    [ForeignKey("UserId")]
    public User User { get; set; }

    public ICollection<Comment> Comments { get; set; }
    public ICollection<Like> Likes { get; set; }
    public ICollection<View> Views { get; set; }

    public Video()
    {
        UploadDate = DateTime.UtcNow;
        Comments = new List<Comment>();
        Likes = new List<Like>();
        Views = new List<View>();
    }
}

public class User : IdentityUser
{
    [Key]
    public override string Id { get; set; }

    [Required(ErrorMessage = "Поле 'Email' обязательно для заполнения")]
    [EmailAddress(ErrorMessage = "Некорректный формат 'Email'")]
    public override string Email { get; set; }

    [Required(ErrorMessage = "Поле 'Логин' обязательно для заполнения")]
    [StringLength(50, MinimumLength = 3, ErrorMessage = "Длина 'Логина' должна быть от 3 до 50 символов")]
    public override string UserName { get; set; }

    [ScaffoldColumn(false)]
    [Display(Name = "Дата регистрации")]
    public DateTime RegistrationDate { get; set; }

    public ICollection<Video> Videos { get; set; }
    public ICollection<SearchHistory> SearchHistory { get; set; }
    public ICollection<WatchHistory> WatchHistory { get; set; }
    public ICollection<WatchLater> WatchLaterList { get; set; }
    public ICollection<Notification> Notifications { get; set; }
    public ICollection<View> Views { get; set; }
    public ICollection<Comment> Comments { get; set; }
    public ICollection<Like> Likes { get; set; }

    public User()
    {
        RegistrationDate = DateTime.UtcNow;
        Videos = new List<Video>();
        SearchHistory = new List<SearchHistory>();
        WatchHistory = new List<WatchHistory>();
        WatchLaterList = new List<WatchLater>();
        Notifications = new List<Notification>();
        Views = new List<View>();
        Comments = new List<Comment>();
        Likes = new List<Like>();
    }
}

public class UserRepository : IUserRepository
{
    private readonly UserManager<User> _userManager;

    public UserRepository(UserManager<User> userManager)
    {
        _userManager = userManager;
    }

    public async Task<User> GetUserByIdAsync(string id)
    {
        return await _userManager.FindByIdAsync(id);
    }
}

If you do not pass the User to the model, then the Video will have the User = null property. If you pass it, then for some reason it tries to create a new user with the data from the User that I passed

Answer 1

Probably the easiest option would be to just skip assigning the User property (assigning UserId should be enough to save the Video entity to the database in correct state):

var video = new Video
{
    Title = model.Title,
    Description = model.Description,
    DurationSeconds = model.DurationSeconds,
    CategoryId = model.CategoryId,
    // User = user,
    UserId = userId
};

It seems that GetUserByIdAsync either uses another context instance or performs non-tracking query.

Another options you can look into:

• Attaching the user object instance to the context manually (for example in the AddVideoAsync call)
• Fixing the GetUserByIdAsync so it would use the same context instance and would perform tracking query on it (without seeing the full code it would be hard to tell how to achieve that)