LINQPad using multiple datacontexts

Question

I often compare data in tables in different databases. These databases do not have the same schema. In TSQL, I can reference them using the DB> user> table structure (DB1.dbo.Stores, DB2.dbo.OtherPlaces) to pull out the data for comparison. I like the idea of ​​LINQPad quite a bit, but I just can't easily pull data from two different data contexts in one set of statements.

I saw how people suggested simply changing the connection string to pull data from another source into the current scheme, but, as I already mentioned, this will not be done. Did I just skip the FAQ page? This seems like a fairly ordinary procedure, unavailable to me.

In the "light" world, I would just refer to the typed datacontext created by LINQPad. Then I could simply:

DB1DataContext db1 = new DB1DataContext ();

DB2DataContext db2 = new DB2DataContext ();

And work from there.

Answer 1

Update : You can now run SQL Server cross-database queries in LINQPad (from LINQPad v4.31 with LINQPad Premium license). To use this function, hold down the Control key while dragging databases from the schema explorer to the query window.

It is also possible to query related servers (which you linked by calling sp_add_linkedserver ). For this:

• Add a new LINQ to SQL join.
• Select Specify a new or existing database and select the primary database that you want to query.
• Select the Enable additional databases check box and select a linked server from the list.

Answer 2

Keep in mind that you can always create a different context yourself.

public FooEntities GetFooContext() { var entityBuilder = new EntityConnectionStringBuilder { Provider = "Devart.Data.Oracle", ProviderConnectionString = "User Id=foo;Password=foo;Data Source=Foo.World;Connect Mode=Default;Direct=false", Metadata = @"D:\FooModel.csdl|D:\FooModel.ssdl|D:\FooModel.msl" }; return new FooEntities(entityBuilder.ToString()); } 

Answer 3

I do not think you can do this. See this LinqPad request.

However, you can collect multiple dbml files into a separate dll and link to them in LinqPad.

Answer 4

You can create as many contexts as you want to split SQL instances and merge pseudo-cross databases, copy data, etc. Please note: context joins are performed locally, so you must call ToList (), ToArray (), etc. Complete queries using your respective data sources, separately before joining. In other words, if you “internally” join 10 lines from DB1.TABLE1 with 20 lines from DB2.TABLE2, both sets (all 30 lines) must be pulled into memory on your local computer before Linq makes the connection and returns the associated / intersecting set (maximum 20 lines per example).

 //EF6 context not selected in Linqpad Connection dropdown var remoteContext = new YourContext(); remoteContext.Database.Connection.ConnectionString = "Server=[SERVER];Database=" + "[DATABASE];Trusted_Connection=false;User ID=[SQLAUTHUSERID];Password=" + "[SQLAUTHPASSWORD];Encrypt=True;"; remoteContext.Database.Connection.Open(); var DB1 = new Repository(remoteContext); //EF6 connection to remote database var remote = DB1.GetAll<Table1>() .Where(x=>x.Id==123) //note...depending on the default Linqpad connection you may get //"EntityWrapperWithoutRelationships" results for //results that include a complex type. you can use a Select() projection //to specify only simple type columns .Select(x=>new { x.Col1, x.Col1, etc... }) .Take(1) .ToList().Dump(); // you must execute query by calling ToList(), ToArray(), // etc before joining //Linq-to-SQL default connection selected in Linqpad Connection dropdown Table2.Where(x=>x.Id = 123) .ToList() // you must execute query by calling ToList(), ToArray(), // etc before joining .Join(remote, a=> ad, b=> (short?)b.Id, (a,b)=>new{b.Col1, b.Col2, a.Col1}) .Dump(); localContext.Database.Connection.Close(); localContext = null; 

Answer 5

Drag and drop approach: hold down the Ctrl key while dragging additional databases from the schema explorer to the query editor.

Use Case:

 //Access Northwind var ID = new Guid("107cc232-0319-4cbe-b137-184c82ac6e12"); LotsOfData.Where(d => d.Id == ID).Dump(); //Access Northwind_v2 this.NORTHWIND_V2.LotsOfData.Where(d => d.Id == ID).Dump();