Home

How does LinqPad generate a single SQL across databases?

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. ?

Comments

  • 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;
    }
  • 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
  • edited July 2018
    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; }
    }
  • That's awesome advice! Thanks!
Sign In or Register to comment.