Avoiding the 2100 parameter limit in LINQ to SQL

In a project I am currently working on, I need to access 2 databases in LINQ in the following manner:

1. I get a list of all trip numbers between a specified date range from DB1, and store this as a list of 'long' values

2. I perform an extensive query with a lot of joins on DB2, but only looking at trips that have their trip number included in the above list.

Problem is, the trip list from DB1 often returns over 2100 items - and I of course hit the 2100 parameter limit in SQL, which causes my second query to fail. I've been looking at ways around this, such as desc开发者_如何学运维ribed here, but this has the effect of essentially changing my query to LINQ-to-Objects, which causes a lot of issues with my joins

Are there any other workarounds I can do?

---

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

• have a stored proc that takes an array as a input then puts the values in a temp table to join on
• likewise by taking a string that the stored proc splits

Or upload all the values to a temp table yourself and join on that table.

However maybe you should rethink the problem:

• Sql server can be configured to allow query against tables in other databases (including oracle), if you are allowed this may be an option for you.
• Could you use some replication system to keep a table of trip numbers updated in DB2?

---

Not sure whether this will help, but I had a similar issue for a one-off query I was writing in LinqPad and ended up defining and using 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 only had one int column, but you should be able to define whatever column(s) type you want, (as long as you have a primary key).

---

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