How does LinqPad generate a single SQL across databases?

rmagruder

Just wondering in this era of Entity Framework Core, how does LinqPad under the covers support querying across databases? I click on the SQL tab I see a unified SQL statement with fully qualified database.schema.table names and everything works. Is there any way to do this within my applications? Everything I find online indicates that short of creating views or other hacks, you can't query across databases with DbContext. But somehow, LinqPad manages. ?

nescafe

Assuming LINQPad premium, you can inspect the data context by pressing Alt+Shift+R (Query, Reflect Query in ILSpy).

It seems that nothing really special is going on, the additional types are declared in another class and the datacontext is passed by reference.

The trick is to specify the fully qualified name in the name attribute:

void Main()
{
  var dataContext = new System.Data.Linq.DataContext(@"Data Source=(local);Initial Catalog=master");
  var products = dataContext.GetTable<Product>();
  products.Dump();
}

// Define other methods and classes here
[Table(Name = @"[Nutshell].[dbo].[Product]")] // <-- fully qualified table name
public class Product
{
  [Column(IsPrimaryKey = true, DbType = "Int NOT NULL", UpdateCheck = UpdateCheck.Never)]
  public int ID;

  [Column(DbType = "Bit NOT NULL", UpdateCheck = UpdateCheck.Never)]
  public bool Discontinued;

  [Column(DbType = "DateTime NOT NULL", UpdateCheck = UpdateCheck.Never)]
  public DateTime LastSale;
}

rmagruder

I did try the fully qualified name in the Table attribute, but at least in .net Core 2.1 I get an error saying that's not a valid table name. So I don't know if this is a EF Core specific issue or not. Thanks, though ! I'll keep trying stuff

nescafe

LINQPad uses LINQ to SQL, not Entity Framework.

For EF, it won't work, even with 'hackish' schema.

void Main()
{
  Database.SetInitializer<NutshellContext>(null);

  var connectionString = "Data Source=(local);Initial Catalog=master";
  var databaseContext = new NutshellContext(connectionString);
  databaseContext.Customers.Dump();
}

// Define other methods and classes here
public class NutshellContext : DbContext
{
  public NutshellContext(string connectionString)
    : base(connectionString)
  { }

  public DbSet<Customer> Customers { get; set; }
}

[Table("Customer", Schema="Nutshell.dbo")]
public class Customer
{
  public int ID { get; set; }
}

Exception:
Invalid object name 'Nutshell.dbo.Customer'.

There is a workaround described here: https://stackoverflow.com/a/26922902/1105812

Example code:

void Main()
{
  DbConfiguration.SetConfiguration(new MyConfiguration());
  Database.SetInitializer<NutshellContext>(null);
  
  var connectionString = "Data Source=(local);Initial Catalog=master";
  var databaseContext = new NutshellContext(connectionString);
  databaseContext.Customers.Dump();
}

// Define other methods and classes here
public class MyConfiguration : DbConfiguration
{
  public MyConfiguration()
  {
    this.AddInterceptor(new MyInterceptor());
  }
}
public class MyInterceptor : DbCommandInterceptor
{
  public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
  {
    // Here, I can just replace the CommandText on the DbCommand - but remember I
    // want to only do it on NutshellContext
    if (interceptionContext.DbContexts.FirstOrDefault() is NutshellContext)
    {
      command.CommandText = command.CommandText.Replace("[dbo].[Customer]", "[Nutshell].[dbo].[Customer]");
    }

    base.ReaderExecuting(command, interceptionContext);
  }
}

public class NutshellContext : DbContext
{
  public NutshellContext(string connectionString)
    : base(connectionString)
  { }

  public DbSet<Customer> Customers { get; set; }
}

[Table("Customer")]
public class Customer
{
  public int ID { get; set; }
}

rmagruder

That's awesome advice! Thanks!