Avoiding parameter limit 2100 in LINQ to SQL

Question

In the project I'm working on now, I need to access 2 databases in LINQ as follows:

• I get a list of all trip numbers between the specified date range from DB1 and save it as a list of "long" values

• I am performing an extensive query with a large number of connections to DB2, but I only look at trips for which their trip number is listed above.

The problem is that the shutdown list from DB1 often returns more than 2100 items - and of course, I fell into the limit of parameter 2100 in SQL, which leads to the failure of my second query. I was looking for ways to get around this, for example, described here , but it essentially changes my query to LINQ-to-Objects, which causes a lot of problems with my connections

Are there any other workarounds?

Answer 1

since LINQ-to-SQL can call stored procs, you could

• have a stored proc that takes an array as input, then puts the values ​​in the temp table to join
• likewise, taking the string that stores proc breaks

Or load all the values ​​into a temporary table yourself and join this table.

However, perhaps you should rethink the problem:

• The Sql server can be configured to allow querying tables in other databases (including oracle), if you are allowed, this may be for you.
• Could you use some replication system to update your trip number table in DB2?

Answer 2

Not sure if this will help, but I had a similar problem for the one-time query I wrote in LinqPad, and ultimately determined and used a temporary table like this.

[Table(Name="#TmpTable1")] public class TmpRecord { [Column(DbType="Int", IsPrimaryKey=true, UpdateCheck=UpdateCheck.Never)] public int? Value { get; set; } } public Table<TmpRecord> TmpRecords { get { return base.GetTable<TmpRecord>(); } } public void DropTable<T>() { ExecuteCommand( "DROP TABLE " + Mapping.GetTable(typeof(T)).TableName ); } public void CreateTable<T>() { ExecuteCommand( typeof(DataContext) .Assembly .GetType("System.Data.Linq.SqlClient.SqlBuilder") .InvokeMember("GetCreateTableCommand", BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod , null, null, new[] { Mapping.GetTable(typeof(T)) } ) as string ); } 

Usage is something like

 void Main() { List<int> ids = .... this.Connection.Open(); // Note, if the connection is not opened here, the temporary table // will be created but then dropped immediately. CreateTable<TmpRecord>(); foreach(var id in ids) TmpRecords.InsertOnSubmit( new TmpRecord() { Value = id}) ; SubmitChanges(); var list1 = (from r in CustomerTransaction join tt in TmpRecords on r.CustomerID equals tt.Value where .... select r).ToList(); DropTable<TmpRecord>(); this.Connection.Close(); 

}

In my case, the temporary table had only one int column, but you should be able to define whatever type of columns you want (as long as you have the primary key).

Answer 3

You can split your query or use a temporary table in database2 populated with results from database1.