C# nhibernate sql-server multiple-databases

从不在大集合中的数据库中选择 id，提供了太多参数

发布于 2024-12-04 11:00:53 字数 454 浏览 2 评论 0原文

我正在开发一个有两个数据库的应用程序。

我有一个对象，让我们称之为“permit”，它有一个 id 列表，引用表中的 id，让我们在另一个数据库中调用该“tasks”。

我正在尝试运行以下查询：

var listOfUsedIds = select taskid from Permit_Task;
Select * from task where id not in (listOfUsedIds)

当我运行此代码时，出现错误：

传入的表格数据流（TDS）远程过程调用（RPC）协议流不正确。
此 RPC 请求中提供了太多参数。最大是 2100。

我无法运行子选择或任何东西，因为 NHibernate 不允许我在两个数据库上执行此操作。

谁能帮我解决这个问题？

原文

I am working on a application that has two databases.

I have an object, let's call it permit, that has a list of id's referencing to id's from a table, lets call that tasks, in the other database.

I am trying to run the following query:

var listOfUsedIds = select taskid from Permit_Task;
Select * from task where id not in (listOfUsedIds)

When I run this code I get the error:

The incoming tabular data stream (TDS) remote procedure call (RPC)
protocol stream is incorrect.
Too many parameters were provided in this RPC request. The maximum
is 2100.

I can't run a sub select or any thing because NHibernate won't let me do that over two databases.

Can anyone help me how to work around this problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南街女流氓 2024-12-11 11:00:53

using (var tx = session.BeginTransaction())
{
    session.CreateSQLQuery("CREATE TEMP TABLE usedIds (id INT)").ExecuteUpdate();

    for (int index = 0; index < ids.Length; index++)
    {
        // TODO: batch this
        session.CreateSQLQuery("INSERT INTO usedIds VALUES (:p" + index + ")")
            .SetParameter("p" + index, id)
            .ExecuteUpdate();
    }

    session.CreateSQLQuery("CREATE INDEX usedIds_idx ON usedIds (id)").ExecuteUpdate();


    Batch batch;
    while((batch.List = session.CreateSQLQuery("SELECT id FROM tasks t WHERE 1 = (SELECT COUNT(*) FROM usedIds u WHERE u.id = t.id) LIMIT 10 OFFSET " + batch.Number).List<int>()).Count > 0)

    {
        var tasks = session.QueryOver<Task>()
            .Where(t => t.Id.IsIn(batch))
            .List();
        // Do something with the tasks
    }
    tx.Commit();
}

或者

public TaskMap()
{

    Map(x => x.IsUsedCount).Formula("SELECT (SELECT COUNT(*) FROM usedIds u WHERE u.Id = Id)").LazyLoad();
}

var tasks = session.QueryOver<Task>()
    .Where(t => t.IsUsedCount == 0)
    .List();

using (var tx = session.BeginTransaction())
{
    session.CreateSQLQuery("CREATE TEMP TABLE usedIds (id INT)").ExecuteUpdate();

    for (int index = 0; index < ids.Length; index++)
    {
        // TODO: batch this
        session.CreateSQLQuery("INSERT INTO usedIds VALUES (:p" + index + ")")
            .SetParameter("p" + index, id)
            .ExecuteUpdate();
    }

    session.CreateSQLQuery("CREATE INDEX usedIds_idx ON usedIds (id)").ExecuteUpdate();


    Batch batch;
    while((batch.List = session.CreateSQLQuery("SELECT id FROM tasks t WHERE 1 = (SELECT COUNT(*) FROM usedIds u WHERE u.id = t.id) LIMIT 10 OFFSET " + batch.Number).List<int>()).Count > 0)

    {
        var tasks = session.QueryOver<Task>()
            .Where(t => t.Id.IsIn(batch))
            .List();
        // Do something with the tasks
    }
    tx.Commit();
}

public TaskMap()
{

    Map(x => x.IsUsedCount).Formula("SELECT (SELECT COUNT(*) FROM usedIds u WHERE u.Id = Id)").LazyLoad();
}

var tasks = session.QueryOver<Task>()
    .Where(t => t.IsUsedCount == 0)
    .List();

回复收藏 0 原文