asp.net - Prevent XPO from generating SQL with too many parameters? - Stack Overflow

admin管理员组

文章数量:1122846

Our clients want to do a single query to a Person table that fetches the records whose integer ids are included in a large list, often thousands of ids at once, e.g.:

List<int> idsOfRecordsToRetrieve = (list of 5000 ids starting with 100, 205, 215);

List<Person> people = new XPQuery<Person>(session).Where(p => idsOfRecordsToRetrieve.Contains(p.Id).ToList();

A problem we have is that the SQL ends up being written like: select ... from Person where id in (@p0, @p1, @p2 ... @p0 = 100, @p1 = 205, @p2 = 215 ...

But we hit the SQL Server parameter limit because it tries to create a parameter for every id

Is there some way to prevent it from making parameters like this and write out something more like select ... from Person where id in (100, 205, 215 ...)

Within the limitations of our client's requirements and the system we're working with, it would be very hard to break up that query or find something else in common between all the records we want to query that uses up fewer parameters

For now, we are trying to migrate to other systems which allow us to generate better SQL (or at least have more control over how our SQL is generated), but there is pressure to come up with some immediate solution

I mean, sometimes there is no immediate solution, but I'm hoping there are some options out there

本文标签： aspnetPrevent XPO from generating SQL with too many parametersStack Overflow