c# - 使用 CaSTLe ActiveRecord 插入多行并忽略任何重复项的推荐方法

Question

我有一个 web 方法，可以将一堆食谱插入数据库中的队列(用于存储用户对 cooking 感兴趣的食谱，类似于 Netflix 的电影队列)。用户能够一次勾选一堆食谱并将它们排队。我有类似这样的代码:

[WebMethod]
public void EnqueueRecipes(SecurityCredentials credentials, Guid[] recipeIds)
{
    DB.User user = new DB.User(credentials);

    using (new TransactionScope(OnDispose.Commit))
    {
       foreach (Guid rid in recipeIds)
       {
          DB.QueuedRecipe qr = new DB.QueuedRecipe(Guid.NewGuid(), user, new DB.Recipe(rid));
          qr.Create();
       }
    }
}

我对 UserId/RecipeId 有一个唯一约束，因此用户只能将一个菜谱加入队列一次。然而，如果他们碰巧选择了一个已经在他们队列中的食谱，我真的不想用错误消息来打扰用户，我只想忽略那个食谱。

如果违反唯一约束，以上代码将抛出 SQL 异常。解决这个问题的最佳方法是什么，而只是忽略重复的行。我目前的想法是:

• 1) 首先从数据库中加载用户的整个队列并检查先列出那个。如果配方已经存在，只需 continue在for 循环。优点:没有不必要的 SQL 插入被发送到数据库。缺点:速度较慢，尤其是在用户队列较多的情况下。
• 2) 不要使用 ActiveRecord 而是传递整个 recipeIds 数组变成一个 SQL 函数。此函数将检查每一行是否存在第一的。优点:可能很快，让 SQL 处理所有脏工作。缺点:破坏 ActiveRecord 模式并需要新的数据库代码，即通常更难维护且实现成本更高。
• 3) 每次循环后创建并刷新。基本上，不要运行整个在单个事务中循环。提交每一行，因为它被添加和捕获 SQL 错误并忽略。优点:启动成本低，而且不会需要新的 SQL 后端代码。缺点:插入速度可能较慢一次将大量行存入数据库，尽管用户对此表示怀疑会一次提交十几个左右的新食谱。

CaSTLe 或 NHibernate 框架还有其他小技巧吗？另外，我的 SQL 后端是 PostgreSQL 9.0。谢谢!

更新:

我尝试了第一种方法，它似乎工作得很好。我突然想到我不必加载整个队列，只需加载出现在 recipeIds 中的队列即可。我相信我的foreach()循环现在是 O(n^2) 取决于 List<Guid>::Contains() 的效率但我认为这对于我将要使用的尺寸来说可能还不错。

//Check for dupes
DB.QueuedRecipe[] dbRecipes = DB.QueuedRecipe.FindAll(Expression.In("Recipe",
   (from r in recipeIds select new DB.Recipe(r)).ToArray()
));

List<Guid> existing = (from r in dbRecipes select r.Recipe.RecipeId).ToList();

using (new TransactionScope(OnDispose.Commit))
{
   foreach (Guid rid in recipeIds)
   {
      if (existing.Contains(rid))
         continue;

      DB.QueuedRecipe qr = new DB.QueuedRecipe(Guid.NewGuid(), user, new DB.Recipe(rid));
      qr.Create();
   }
}

Answer 1

您可以使用一条 SQL 语句来做到这一点:

INSERT INTO user_recipe
SELECT new_UserId, new_RecipeId
FROM   user_recipe
WHERE  NOT EXISTS (
   SELECT *
   FROM   user_recipe
   WHERE  (UserId, RecipeId) = (new_UserId, new_RecipeId)
   );

SELECT 只返回不存在的行，因此只会在这种情况下插入。

---

批量插入的解决方案

如果您要一次插入一长串食谱，您可以:

CREATE TEMP TABLE i(userId int, recipeid int) ON COMMIT DROP;

INSERT INTO i VALUES
(1,2), (2,4), (2,4), (2,7), (2,43), (23,113), (223,133);

INSERT INTO user_recipe
SELECT DISTINCT i.*  -- remove dupes from the insert candidates themselves
FROM   i
LEFT   JOIN user_recipe u USING (userid, recipeid)
WHERE  u.userid IS NULL;

---

一次插入一把的解决方案

正如 Mike 评论的那样，临时表对于仅有的几条记录来说有点矫枉过正。

INSERT INTO user_recipe
SELECT i.* 
FROM  (
    SELECT DISTINCT *     -- only if you need to remove possible dupes
    FROM (
       VALUES (1::int, 2::int)
          ,(2, 3)
          ,(2, 4)
          ,(2, 4)            -- dupe will be removed
          ,(2, 43)
          ,(23, 113)
          ,(223, 133)
       ) i(userid, recipeid)
    ) i
LEFT   JOIN user_recipe u USING (userid, recipeid)
WHERE  u.userid IS NULL;