使用LINQ添加大量记录

本文关键字：记录 添加 LINQ 使用 | 更新日期: 2023-09-27 18:14:41

我必须从Excel导入数百条记录到数据库。

每条记录必须被验证:

    对复制
  2. 必须在另一个表中有外键

我想知道我应该如何以最高的性能做到这一点。我知道我不应该在每个记录之后使用db.SaveChanges();，所以经过验证-我将每个记录添加到临时列表(var recordsToAdd)，并且我毕竟保存了该列表。请检查我的代码下面，这是一个很好的方法来做到这一点吗?

using (var db = new DbEntities())
{
    var recordsToAdd = new List<User>();
    for (var row = 2; row <= lastRow; row++)
    {
        var newRecord = new User
        {
            Id = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToNullSafeString()),
            FirstName = worksheet.Cells[firstNameColumn + row].Value.ToNullSafeString(),
            LastName = worksheet.Cells[lastNameColumn + row].Value.ToNullSafeString(),
            SerialNumber = worksheet.Cells[serialNumber + row].Value.ToNullSafeString()
        };
        bool exists = db.User.Any(u => u.Id == newRecord.Id) || recordsToAdd.Any(u => u.Id == newRecord.Id);
        if (!exists)
        {
            bool isSerialNumberExist = db.SerialNumbers.Any(u => u.SerialNumber == newRecord.SerialNumber);
            if (isSerialNumberExist)
            {
                recordsToAdd.Add(newRecord);
            }
            else
            {
                resultMessages.Add(string.Format("SerialNumber doesn't exist"));
            }
        }
        else
        {
            resultMessages.Add(string.Format("Record already exist"));
        }
    }
    db.User.AddRange(recordsToAdd);
    db.SaveChanges();
}

使用LINQ添加大量记录

首先让我们将代码分成两部分。第一部分是创建要插入的有效User记录列表。第二部分是将这些记录插入数据库(代码的最后两行)。

假设您使用EntityFramework作为ORM，第二部分可以通过批量插入记录来优化。它有许多现成的解决方案，可以很容易地找到。(例子)

关于第一部分有一些建议。

  1. 加载HashSet或Dictionary中的用户id。这些数据结构针对搜索进行了优化。var userDbIds = new HashSet<int>(db.User.Select(x => x.Id));。您将快速检查id是否存在，而无需向DB发出请求。

  2. 对serialNumber做同样的操作。var serialNumbers = new HashSet<string>(db.SerialNumber.Select(x => x.SerialNumber));假设SerialNumber属性类型为string

  3. 出于同样的原因，将recordToAdd变量的类型更改为Dictionary<int, User>

在检查中看起来像这样:

    bool exists = userDbIds.Contains(newRecord.Id) || recordsToAdd.ContainsKey(newRecord.Id);
    if (!exists)
    {
        bool isSerialNumberExist = serialNumbers.Contains(newRecord.SerialNumber);
        if (isSerialNumberExist)
        {
            recordsToAdd[newRecord.Id] = newRecord;
        }
        else
        {
            resultMessages.Add(string.Format("SerialNumber doesn't exist"));
        }
    }
    else
    {
        resultMessages.Add(string.Format("Record already exist"));
    }

提高性能的一种方法是通过使用快速查找数据结构来执行验证来最小化db调用和线性搜索- HashSet<string>用于Id, Dictionary<string, bool>用于SerialNumber:

using (var db = new DbEntities())
{
    var recordsToAdd = new List<User>();
    var userIdSet = new HashSet<string>();
    var serialNumberExistsInfo = new Dictionary<string, bool>();
    for (var row = 2; row <= lastRow; row++)
    {
        var newRecord = new User
        {
            Id = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToNullSafeString()),
            FirstName = worksheet.Cells[firstNameColumn + row].Value.ToNullSafeString(),
            LastName = worksheet.Cells[lastNameColumn + row].Value.ToNullSafeString(),
            SerialNumber = worksheet.Cells[serialNumber + row].Value.ToNullSafeString()
        };
        bool exists = !userIdSet.Add(newRecord.Id) || db.User.Any(u => u.Id == newRecord.Id);
        if (!exists)
        {
            bool isSerialNumberExist;
            if (!serialNumberExistsInfo.TryGetValue(newRecord.SerialNumber, out isSerialNumberExist))
                serialNumberExistsInfo.Add(newRecord.SerialNumber, isSerialNumberExist = 
                    db.SerialNumbers.Any(u => u.SerialNumber == newRecord.SerialNumber));
            if (isSerialNumberExist)
            {
                recordsToAdd.Add(newRecord);              
            }
            else
            {
                resultMessages.Add(string.Format("SerialNumber doesn't exist"));
            }
        }
        else
        {
            resultMessages.Add(string.Format("Record already exist"));
        }
    }
    db.User.AddRange(recordsToAdd);
    db.SaveChanges();
}

使用表值参数代替LINQ将是最有效的。这样，您就可以使用基于集合的方法来处理这个问题，即单个连接、单个存储过程执行和单个事务。基本设置显示在我在以下回答中提供的示例代码中(在S.O.上):

如何在最短的时间内插入1000万条记录?

存储过程可以处理两个验证:

  • 不要插入重复的记录
  • 确保SerialNumber存在

用户定义表类型(UDTT)类似于:

CREATE TYPE dbo.UserList AS TABLE
(
  Id           INT NOT NULL,
  FirstName    NVARCHAR(50) NOT NULL,
  LastName     NVARCHAR(50) NULL,
  SerialNumber VARCHAR(50) NOT NULL
);
-- Uncomment the following if you get a permissions error:
-- GRANT EXECUTE ON TYPE::[dbo].[UserList] TO [ImportUser];
GO

存储过程(通过SqlCommand.ExecuteNonQuery执行)看起来像这样:

CREATE PROCEDURE dbo.ImportUsers
(
  @NewUserList  dbo.UserList READONLY
)
AS
SET NOCOUNT ON;
INSERT INTO dbo.User (Id, FirstName, LastName, SerialNumber)
  SELECT  tmp.Id, tmp.FirstName, tmp.LastName, tmp.SerialNumber
  FROM    @NewUserList tmp
  WHERE   NOT EXISTS (SELECT *
                      FROM   dbo.User usr
                      WHERE  usr.Id = tmp.[Id])
  AND     EXISTS (SELECT *
                  FROM   dbo.SerialNumbers sn
                  WHERE  sn.SerialNumber = tmp.[SerialNumber]);

上面的存储过程直接忽略无效记录。如果你需要"错误"的通知，你可以使用下面的定义(通过SqlCommand.ExecuteReader执行):

CREATE PROCEDURE dbo.ImportUsers
(
  @NewUserList  dbo.UserList READONLY
)
AS
SET NOCOUNT ON;
CREATE TABLE #TempUsers
(
  Id           INT NOT NULL,
  FirstName    NVARCHAR(50) NOT NULL,
  LastName     NVARCHAR(50) NULL,
  SerialNumber VARCHAR(50) NOT NULL,
  UserExists          BIT NOT NULL DEFAULT (0),
  InvalidSerialNumber BIT NOT NULL DEFAULT (0)
);
INSERT INTO #TempUsers (Id, FirstName, LastName, SerialNumber)
  SELECT  tmp.Id, tmp.FirstName, tmp.LastName, tmp.SerialNumber
  FROM    @NewUserList tmp;
-- Mark existing records
UPDATE tmp
SET    tmp.UserExists = 1
FROM   #TempUsers tmp
WHERE  EXISTS (SELECT *
               FROM   dbo.User usr
               WHERE  usr.Id = tmp.[Id]);
-- Mark invalid SerialNumber records
UPDATE tmp
SET    tmp.InvalidSerialNumber = 1
FROM   #TempUsers tmp
WHERE  tmp.UserExists = 0 -- no need to check already invalid records
AND    NOT EXISTS (SELECT *
                   FROM   dbo.SerialNumbers sn
                   WHERE  sn.SerialNumber = tmp.[SerialNumber]);
-- Insert remaining valid records
INSERT INTO dbo.User (Id, FirstName, LastName, SerialNumber)
  SELECT  tmp.Id, tmp.FirstName, tmp.LastName, tmp.SerialNumber
  FROM    #TempUsers tmp
  WHERE   tmp.UserExists = 0
  AND     tmp.InvalidSerialNumber = 0;
-- return temp table to caller as it contains validation info
SELECT  tmp.Id, tmp.FirstName, tmp.LastName, tmp.SerialNumber,
        tmp.UserExists, tmp.InvalidSerialNumber
FROM    #TempUsers tmp
-- optionally only return records that had a validation error
--  WHERE   tmp.UserExists = 1
--  OR     tmp.InvalidSerialNumber = 1;

当这个版本的存储过程完成后，循环执行SqlDataReader.Read()以获得验证信息。

相关文章：