SQL Server & Entity Framework unique constraint exception when SaveChanges

Question

I have this data:

When I'm uploading files to srv a have follow code:

DataContext db = new DataContext();    
[HttpPost]
public ActionResult UploadFiles(HttpPostedFileBase[] files, int? folderid, string description)
{
    foreach (HttpPostedFileBase file in files)
    {
        if (file != null)
        {
            string fileName = Path.GetFileNameWithoutExtension(file.FileName);
            string fileExt = Path.GetExtension(file.FileName)?.Remove(0, 1);

            int? extensionid = db.FileExtensions.FirstOrDefault(m => m.displayname == fileExt)
                ?.file_extensionid;

            if (extensionid == null)
            {
                    CreateExtension(fileExt, out int? extid);
                    extensionid = extid;
            }

            if (CheckFileExist(fileName, fileExt, folderid))
            {
                fileName = fileName + $" ({DateTime.Now.ToString("dd-MM-yy HH:mm:ss")})";
            }

            File dbFile = new File();
            dbFile.folderid = folderid;
            dbFile.displayname = fileName;
            dbFile.file_extensionid = extensionid;
            dbFile.file_content = GetFileBytes(file);
            dbFile.description = description;

            db.Files.Add(dbFile);
            db.SaveChanges();
        }
    }
    return RedirectToAction("Partial_SuccesUploadedToast", "Toast");
}

And if FileExtension not already in database i want to create it with this method:

 private void CreateExtension(string name, out int? extid)
    {
        if (db.FileExtensions.Any(m => m.displayname == name))
        {
            extid = db.FileExtensions.FirstOrDefault(m => m.displayname == name)?.file_extensionid;
            return;
        }

        FileExtension fileExtension = new FileExtension()
        {
            displayname = name
        };
        db.FileExtensions.Add(fileExtension);
        extid = fileExtension.file_extensionid;
        db.SaveChanges();
    }

So, when I uploading some files with one extension, I get an exception that entity with new file ext can't be added because already has (unique constraint). What can I do with that? I saving EXT and next foreach cycle iteration must check that EXT already exists, idk... help please!

Context classes:

    public class File
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        public int fileid { get; set; }

        [Required(ErrorMessage = "Обязательно укажите название!")]
        [StringLength(200, MinimumLength = 1,
            ErrorMessage = "Название файла должно быть от 1 до 200 символов")]
        public string displayname { get; set; }

        [StringLength(1000,
            ErrorMessage = "Описание файла должно до 1000 символов")]
        public string description { get; set; }

        [HiddenInput(DisplayValue = false)]
        public int? file_extensionid { get; set; }

        [HiddenInput(DisplayValue = false)]
        public int? folderid { get; set; }

        public byte[] file_content { get; set; }

        [ForeignKey("file_extensionid")]
        public FileExtension FileExtension { get; set; }
    
        [ForeignKey("folderid")]
        public Folder Folder { get; set; }

    }



    public class FileExtension
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        public int file_extensionid { get; set; }

        [Required(ErrorMessage = "Обязательно укажите название расширения!")]
        [StringLength(20, MinimumLength = 1,
            ErrorMessage = "Название расширения должно быть от 1 до 20 символов")]
        public string displayname { get; set; }

        [StringLength(200, MinimumLength = 1,
            ErrorMessage = "Название иконки должно быть от 1 до 200 символов")]
        public string icon_filename { get; set; }
    }

     public class Folder
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        public int folderid { get; set; } = 0;

        [Required(ErrorMessage = "Обязательно укажите название!")]
        [StringLength(100, MinimumLength = 1,
            ErrorMessage = "Название папки должно быть от 1 до 100 символов")]
        public string displayname { get; set; }

        [HiddenInput(DisplayValue = false)]
        public int? parent_folderid { get; set; }

        //

        [ForeignKey("parent_folderid")]
        public Folder ParentFolder { get; set; }
     }

user_11392290 · Answer

You have a bug. You are trying to get extension id before you had saved changes. And also, you are checking an extension twice. To avoid this you can use this func

private int? GetExtensionId(string name)
{
        
var  extItem= db.FileExtensions.FirstOrDefault(m => m.displayname == name);
if(extItem!=null) return extItem.file_extensionid;

 var fileExtension = new FileExtension()
  {
     displayname = name
  };
 db.FileExtensions.Add(fileExtension);
  db.SaveChanges();
  return fileExtension.file_extensionid;
    }

and replace

int? extensionid = db.FileExtensions.FirstOrDefault(m => m.displayname == fileExt)
                ?.file_extensionid;

            if (extensionid == null)
            {
                    CreateExtension(fileExt, out int? extid);
                    extensionid = extid;
            }

with this

var extensionid = GetExtensionId(fileExt);

also try add one to many relations between file and fileext

 public class File
    {
       .....
      
        [HiddenInput(DisplayValue = false)]
        public int? file_extensionid { get; set; }

       [ForeignKey(nameof(file_extensionid))]
        [InverseProperty("Files")]
        public FileExtension FileExtension { get; set; }

    public class FileExtension
    {
        .....
       
       [InverseProperty(nameof(File.FileExtension))]
        public virtual ICollection Files { get; set; }
    }

maybe you will need to repeat a db migration if it is still not working

SQL Server & Entity Framework unique constraint exception when SaveChanges

1 Answers