Uploading of large csv to database

See more:

SQL

CSV

ASP.NET5

Hi,

I have a csv file that contain anything from a few hundred records to 50,000 records, although I have the process working the time taking to complete is painfully slow, so was looking for advice on best way of performing the requirement.

The scenario is an order can contain anything upto 50,000 codes, the order number is not present in the upload data so cannot perform a bulk insert (or i don't know how).

The CSV file only has one field such as

abcd123
efgh456


Any advise or links would be appreciated.

What I have tried:

The ID for the order is passed in the controller together with the file details. Controller method below.

[HttpPost]
public ActionResult UploadNewMailingData(int id,HttpPostedFileBase attachmentcsv)
{
    CsvFileDescription csvFileDescription = new CsvFileDescription
    {
        SeparatorChar = ',',
        FirstLineHasColumnNames = true
    };
    CsvContext csvContext = new CsvContext();
    StreamReader streamReader = new StreamReader(attachmentcsv.InputStream);
    IEnumerable<MediaSaleRecord> list = csvContext.Read<MediaSaleRecord>(streamReader, csvFileDescription);
    MediaSaleRecord mediaSaleRecord =  new  MediaSaleRecord();
    List<MediaSaleRecord> myList = new List<MediaSaleRecord>();
    for (int n = 0;n<list.Count();n++)
    {
        mediaSaleRecord.CustomerID = list.ElementAt(n).CustomerID;
        mediaSaleRecord.MediaSaleID = id;
        myList.Add(mediaSaleRecord);
    }
    db.MediaSaleRecords.AddRange(myList);
    db.SaveChanges();
    return Redirect("Index");
}

credit for the above code goes to
Upload CSV (other Delimiter-Separated) Files to Sql Server in Asp.Net MVC - Using EF Code First[^]
The model is defined is simple and only consist of:

    public class MediaSaleRecord
    {
        public int MediaSaleRecordID { get; set; }
        public int MediaSaleID { get; set; }
        public string CustomerID { get; set; }
    }
}