In a project I am currently working on, I need to access 2 databases in LINQ in the following manner:
I get a list of all trip numbers between a specified date range from DB1, and store this as a list of 'long' values
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 described 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
Or upload all the values to a temp table yourself and join on that table.
However maybe you should rethink the problem:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With