LINQPad, using multiple datacontexts

Question

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

I've seen people suggest simply changing the connection string to pull the data from the other source into the current schema but, as I mentioned, this will not do. Did I just skip a page in the FAQ? This seems a fairly routine procedure to be unavailable to me.

In the "easy" world, I'd love to be able to simply reference the typed datacontext that LINQPad creates. Then I could simply:

DB1DataContext db1 = new DB1DataContext();

DB2DataContext db2 = new DB2DataContext();

And work from there.

Solution 1

Update: it's now possible to do cross-database SQL Server queries in LINQPad (from LINQPad v4.31, with a LINQPad Premium license). To use this feature, hold down the Control key while dragging databases from the Schema Explorer to the query window.

It's also possible to query linked servers (that you've linked by calling sp_add_linkedserver). To do this:

1. Add a new LINQ to SQL connection.
2. Choose Specify New or Existing Database and choose the primary database you want to query.
3. Click the Include Additional Databases checkbox and pick the linked server(s) from the list.

Solution 2

Keep in mind that you can always create another context on your own.

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());
}

Solution 3

You can instantiate as many contexts as you like to disparate SQL instances and execute pseudo cross database joins, copy data, etc. Note, joins across contexts are performed locally so you must call ToList(), ToArray(), etc to execute the queries using their respective data sources individually before joining. In other words if you "inner" join 10 rows from DB1.TABLE1 with 20 rows from DB2.TABLE2, both sets (all 30 rows) must be pulled into memory on your local machine before Linq performs the join and returns the related/intersecting set (20 rows max 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=> a.d, b=> (short?)b.Id, (a,b)=>new{b.Col1, b.Col2, a.Col1})
    .Dump();

localContext.Database.Connection.Close();
localContext = null;

Solution 4

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();

Solution 5

I do not think you are able to do this. See this LinqPad request.

However, you could build multiple dbml files in a separate dll and reference them in LinqPad.

View more solutions

Comments

[Admin]

So far, this has been my conclusion as well. I was hoping I was wrong! Thanks much.

[Piotr Owsiak]

That's awesome, exactly what I need!

[Piotr Owsiak]

"Error: Invalid object name 'sys.servers'."

[Piotr Owsiak]

"Error: The OLE DB provider "SQLNCLI10" for linked server "XXXXXXXXXX" does not contain the table ""master"."sys"."databases"". The table either does not exist or the current user does not have permissions on that table."

[Piotr Owsiak]

Users, keep in mind that it is cross-database, not cross-server - "Multi-database queries are supported only for SQL Server databases on the same server (or linked servers)."

[Piotr Owsiak]

Would be nice to have support for multiple servers that are not linked just the way Scott describes with multiple data contexts.

[Joe Albahari]

If the servers are not linked, the resultant SQL statement will fail when it tries to join the tables.

[Alex KeySmith]

n.b. although this does work fine for Linq-to-SQL it won't work for DbContext. I realise the question is specifically for Linq-to-SQL, but just in case (Like me) anyone else doesn't read the question properly :-)

[Marvin Zumbado]

Would be nice to have that feature in the free version

[NetMage]

Did you mean to close and null localContext or remoteContext at the bottom? Also, where is YourContext defined?

[Mong Zhu]

the link is dead with a 404 Page not found error.

[Mehdi Dehghani]

What is FooEntities? can I write this code inside LINQPad?if so, how?

[NetMage]

@MongZhu I fixed the link, but the other answer is correct now.

[Guerrilla]

holding down control doesn't do anything different for me, I have premium version

[AceMark]

I know this is a late reply to the comment above, but YourContext would be defined somewhere needed. I believe localContext should have been remoteContext. from how I use it, localContext would be the assigned connection of the Linqpad file, and remoteContext is the "2nd" context. Thanks @Adam

[Matt]

This answer silently assumes that you have created an Assembly containing a class FooEntities. How would it work if you just have a connection string? And which NUGET package do you need to add to have EntityConnectionStringBuilder ?