使用 Castle ActiveRecord 插入多行并忽略任何重复的推荐方法

我有一个 webmethod，可以将一堆菜谱插入到数据库的队列中（用于存储用户有兴趣烹饪的菜谱，类似于 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）每次循环后CreateAndFlush。基本上，不要运行整个 在单个事务中循环。添加添加的每一行并提交 捕获 SQL 错误并忽略。优点：启动成本低，而且不 需要新的 SQL 后端代码。缺点：插入速度可能较慢 一次将大量行存入数据库，尽管用户对此表示怀疑 会一次提交十几个左右的新食谱。

Castle 或 Hibernate 框架还有其他小技巧吗？另外，mySQL 后端是 PostgreSQL 9.0。谢谢！

Update:

我尝试了第一种方法，看起来效果很好。我突然想到我不必加载整个队列，只需加载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();
   }
}

您可以使用单个 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)
   );

The 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;

一次插入一把的解决方案

正如迈克所评论的，临时表对于只有几条记录来说就有点过大了。

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;