I want to insert a list of data into a table in Postgresql using Entity Framework.

Can anyone help by suggesting the syntax or an algo for that?

CodePudding user response：

Like @Nick said raw sql isn't ideal especially if you are inserting 100k values,Tons of options when it comes to bulk insert i specifically use BulkInsert find it much easier example below

 using (var dbContextTransaction = _Context.Database.BeginTransaction())
            {
                try
                {
                    //bulk insert list of user objects in users table
                    _Context.BulkInsert(list_of_users, options =>
                    {
                        options.InsertIfNotExists = true
                        options.ColumnPrimaryKeyExpression = x => new{x.id};

                    });
                    dbContextTransaction.Commit();
                    
                }
                catch (Exception ex)
                {
                    dbContextTransaction.Rollback();
                    throw;

                }
            }

But if you are sticking to the hard route of building the raw sql query and executing it here is how i would do it,i would divide up the list into batches of 10k to avoid memory issues

var insert_query = "INSERT INTO TABLE (Column_1,Column_2,Column_3)VALUES";
int count=0;

            foreach (var obj in list_data)
            {
               var size = count   1;
                if(count is 0)
                    insert_query  = $"({obj.param1},{obj.param2},{obj.param3})";
                else 
                    insert_query  = $",({obj.param1},{obj.param2},{obj.param3)";

                
                if(size.Equals(data.Count()))
                    insert_query  = ";";

              count  ;
            }
            int rows_affected = _Context.Database.ExecuteSqlRaw(insert_query);