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; }
     }

Serge · Accepted Answer · 2021-04-17 18:38:31Z

1

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<File> Files { get; set; }
    }

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

edited Apr 17, 2021 at 18:38

answered Apr 17, 2021 at 17:35

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Serge Over a year ago

Can you post all your 3 classes from dbcontext pls?

hamaronooo Over a year ago

Added Context classes to issue description. Check it please.

Serge Over a year ago

@Никита I updated the answer. Try again. Maybe you will need to repeat db migration

hamaronooo Over a year ago

Thanks for answer, but its still same error, i have dropped tables and create new few times, its not works. It seems in debug mode, like save changes not work properly, like context in Upload method not same contex with CreateExtension method or something like that.....

Serge Over a year ago

Are you sure that it is a fileextension table error? There is nothing to make an error. Maybe it is another table?

|

Collectives™ on Stack Overflow

SQL Server & Entity Framework unique constraint exception when SaveChanges

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related