LINQ to Entity query across multiple databases?

rkb

I have a simple query like

Items.Select(i => new { i.Name })

Now I would like to pull in a table from a different database (hence a different connection/context). Is this possible?

Something like:

Items.Select(i => new { i.Name, .Where(f => f.ID == i.formID).Select(f => new { f.Name }) })
(Where is in a different database.)

Thank you for the tips.

Kevin

nescafe

As LINQPad supports only one connection; not that simple.


You're talking Linq to Entity, so assuming you've created a DbContext class, you could include the secondary class as a reference (F4 => Additional references) and combine the two in local objects. E.g.:

var db = new DbContext(); // The secondary DbContext

var result =
  from i in this.Items.Cache() // Fetch items from first DbContext (LINQPad connection)
  let forms = db.Forms.Cache() // Fetch forms from secondary DbContext (Referenced assembly)
  select new { i.Name, form = forms.Where(f => f.ID == i.formID).Select(f => f.Name) };

result.Dump();

Note that the source tables are completely cached. You could apply some filtering before the .Cache() if this is too much. The records are joined on LINQPad side which will cause some problems in text compare since the database collation will be ignored, but I'm assuming ID and formID are numeric fields.


If you want to combine two databases on the same server or you could add the secondary server as a linked server to the primary server, use the 'Include additional databases' option in the default LINQ to SQL driver.

rkb

Thank you. I can autogenerate DbContext from an existing database but I was hoping there was someway around the "single connection problem" so I could form the query only using LINQPad. But you answered my question.

nescafe

I've tried re-using the generated TypedDataContext_[random].dll, but it fails because of namespace conflicts:

CS0433 The type 'TypedDataContext' exists in both 'TypedDataContext_lvfvkb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' and 'TypedDataContext_ndgtgb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'

You could try using some reflection.. perhaps:

var assembly = Assembly.LoadFrom(Environment.ExpandEnvironmentVariables(@"%LOCALAPPDATA%\Temp\LINQPad5\_wxtapnqq\TypedDataContext_ndgtgb.dll"));
var type = assembly.GetType("LINQPad.User.TypedDataContext");
var context2 = Activator.CreateInstance(type);
context2.GetType().GetField("sysobjects").Dump();

Edit:

Err.. actually it *is* possible and not that difficult:

var assembly = Assembly.LoadFrom(Environment.ExpandEnvironmentVariables(@"%LOCALAPPDATA%\Temp\LINQPad5\_wxtapnqq\TypedDataContext_ndgtgb.dll"));
var type = assembly.GetType("LINQPad.User.TypedDataContext");
var context = Activator.CreateInstance(type);
context.GetType().GetProperty("sysobjects").GetValue(context).Dump();

Gives back expected results. Only problem is that you cannot use intellisense because you cannot reference the secondary dbContext directly but only via activator / reflection.

In summary, you can do the following:
- Create the first connection, execute the query, naviate to %LOCALAPPDATA%\Temp\LINQPad5\... and copy the generated TypedDataContext to another location
- Create the second connection
- Add a reference to the first connection

Add a little IEnumerable<dynamic> magic..

var assembly = Assembly.LoadFrom(Environment.ExpandEnvironmentVariables(@"%LOCALAPPDATA%\Temp\LINQPad5\_wxtapnqq\TypedDataContext_ndgtgb.dll"));
var type = assembly.GetType("LINQPad.User.TypedDataContext");
var context = Activator.CreateInstance(type);
var sysobjects = context.GetType().GetProperty("sysobjects").GetValue(context) as IEnumerable<dynamic>;
sysobjects.Select(x => new { x.name, x.id, x.xtype }).Take(10).Dump();

Note that it will do a full table scan on sysobjects (despite the Take(10)) because we cannot use IQuerable<T> (it doesn't seem to support dynamic types).

nescafe

Without the reflection:

var assembly = Assembly.LoadFrom(Environment.ExpandEnvironmentVariables(@"%LOCALAPPDATA%\Temp\LINQPad\_wxtapnqq\TypedDataContext_ndgtgb.dll"));
var type = assembly.GetType("LINQPad.User.TypedDataContext");
dynamic context = Activator.CreateInstance(type);
var sysobjects = context.sysobjects as IEnumerable<dynamic>;

sysobjects
  .Select(x => new { x.name, x.id, x.xtype })
  .Take(10)
  .Dump();

nescafe

Please vote for the suggestion here if you like:

http://linqpad.uservoice.com/forums/18302-linqpad-feature-suggestions/suggestions/1926753-allow-multiple-data-contexts-per-query