ExecuteSqlInterpolatedAsync EF核心在db中插入失败

Question

我很难用ef核心方法ExecuteSqlInterpolatedAsync/ExecuteSqlInterpolated.插入psql db。TableScheme：

      Column  |  Type   | Collation | Nullable |                 Default                  
----------+---------+-----------+----------+------------------------------------------
 Id       | integer |           | not null | nextval('"LookupRows_Id_seq"'::regclass)
 Index    | integer |           | not null | 
 RowData  | text    |           | not null | 
 LookupId | integer |           | not null | 

插入方法：

FormattableString str = $"INSERT into \"LookupRows\" (\"Index\", \"RowData\", \"LookupId\") VALUES {q.First()};";
await _context.Database.ExecuteSqlRawAsync(str.ToString(),cancellationToken);
await _context.Database.ExecuteSqlInterpolatedAsync($"INSERT into \"LookupRows\" (\"Index\", \"RowData\", \"LookupId\") VALUES {q.First()};",cancellationToken);

q.First() = (0, '{{"0":["0","Bucharest","0"]}}', 115)

ExecuteSqlRawAsync工作得很好。条目已成功插入。但是ExecuteSqlInterpolatedAsync总是返回一个错误：MessageText: syntax error at or near "$1"，我的想法用完了。我做错什么了？

Answer 1

两种选择都不起作用。

在第一种情况下，代码使用普通的旧字符串操作来构造SQL查询，从而允许SQL注入和转换错误。如果该q.First()是一个值为('','',''); drop table Users;--的字符串，那么您将得到一个删除的表。

在第二种情况下，语法根本无效。而不是通过参数提供预期的3个值，而是使用单个值。

ExecuteSqlRawAsync的正确语法是：

var sql=@"INSERT into LookupRows (Index, RowData, LookupId) 
VALUES (@index,@row,@lookup);"

var paramObject=new {index=1,row="abc",lookup=100};
await _context.Database.ExecuteSqlRawAsync(sql, paramObject, cancellationToken);

参数对象的属性必须与参数名称匹配。

ExecuteSqlInterpolatedAsync的正确语法是：

await _context.Database.ExecuteSqlInterpolatedAsync(
    @$"INSERT into LookupRows (Index, RowData, LookupId) 
VALUES ({paramObject.index},{paramObject.row},{paramObject.lookup});", 
cancellationToken);

或

FormattableString sql=@$"INSERT into LookupRows (Index, RowData, LookupId) 
VALUES ({paramObject.index},{paramObject.row},{paramOject.lookup});";

await _context.Database.ExecuteSqlInterpolatedAsync(sql, cancellationToken);

ExecuteSqlInterpolatedAsync将检查FormattableString并使用字符串占位符作为位置参数并将其值作为参数值生成一个新的参数化查询。相当于：

var sql=@"INSERT into LookupRows (Index, RowData, LookupId) 
VALUES (?,?,?);"

await _context.Database.ExecuteSqlRawAsync(sql, 1,"abc",100);

使用ExecuteSqlInterpolatedAsync是相当危险的，因为它太容易忘记显式指定FormattableString并以以下方式结束：

var sql=@$"INSERT into LookupRows (Index, RowData, LookupId) 
VALUES ({paramObject.index},{paramObject.row},{paramObject.lookup});";

，它只是一个由数据构造的字符串，并且再次容易受到SQL注入的影响。

插入100 K项

看起来实际的问题是插入100 K行。奥姆斯是这份工作的错误工具。在这种情况下，不是一个单一的对象图，而是没有业务逻辑的100 K行。

执行100 K INSERT语句将花费很长时间，并淹没数据库的事务日志。解决方案是使用SqlBulkCopy使用大容量操作和最小日志记录插入行。

SqlBulkCopy.WriteToServer需要一个DataTable或DataReader。要使用IEnumerable<T>，我们可以使用FastMember's ObjectReader包装它：

IEnumerable<SomeType> data = ... 


using(var bcp = new SqlBulkCopy(connection)) ;
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) 
{ 
  bcp.DestinationTableName = "SomeTable"; 
  bcp.WriteToServer(reader); 
}

导入CSVs

要导入CSV文件，可以使用CsvHelper的CsvDataReader：

using (var reader = new StreamReader("path\\to\\file.csv"))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    // Do any configuration to `CsvReader` before creating CsvDataReader.
    using (var dr = new CsvDataReader(csv))
    using(var bcp = new SqlBulkCopy(connection))
    {       
           
      bcp.DestinationTableName = "SomeTable"; 
      bcp.WriteToServer(dr); 
    }
}