Efcore: Support specifying catalog for table mapping

[zpul]

I need to access a Table from another database located in the same server, so I did specify its location in the table name but this do not work.

The problem are the angled brackets put in the name of the table. Can I avoid inserting this brackets into the table name?

I receive this exception:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.<>c__DisplayClass17_0.<ExecuteReader>b__0(DbCommand cmd, IRelationalConnection con) at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.Execute[T](IRelationalConnection connection, Func3 action, String executeMethod, Boolean openConnection, Boolean closeConnection)
at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, Boolean manageConnection)
at Microsoft.Data.Entity.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext() at Microsoft.Data.Entity.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToListTSource in D:gitvnextWebApplication1srcWebApplication1ControllersIdentsController.cs:line 33

[rowanmiller]

Currently we only support schema and table name (and do all the appropriate escaping to deal with extra .s etc.). We agree it would be good to have an overload of ToTable that allows you to specify a catalog though. Moving to the backlog for the moment, since we won't be doing this for initial 7.0.0 RTM.

[xyting]

@rowanmiller Have any plan to release this feature? our team waiting to use this feature in spring of 2017

[rowanmiller]

@xyting it's not on our list for an upcoming release. It would make a good external contribution though 😄

[nvivo]

Just to add one use case here, I currently have an application that uses 3 databases databases in the same server, and a dozen servers that access the same 3 databases. Because the connection string is different for each database, each application has 3 connection pools to the same server. This causes lots of connections being open to the same server, which can cause the server to stop accepting connections sometimes.

This can be kept under control to some extent using Max Pool Size and Connection Lifetime, but at some point I found it simpler to stop using entities in favor of pure SQL for a lot of stuff to be able to reuse idle connections from the pool.

So, I'm in line for this feature. =)

[MaklaCof]

This would also solve problems with common (shared) tables when solving multiple tenants with Separate Database for every tenant:
each tenant has it's own database, but some tables, like users, groups, countries ..., must be common (shared).

There is currently no work around.

[kryptus47]

Just to add another usecase. I am dealing with a legacy database that is composed of no less than 23 different databases with tons of unmaintainable legacy applications using it and querying across the different databases.

Basically I am creating an OData Web API using MVC Core + EF Core to abstract the misery for future applications.
I am also using AutoMapper with EF Core to tranform the objects as needed which is working flawlessely.

The only show stopper for now is the fact that I cannot specify the catalog and expect entity framework to behave like a good boy.

[mrswain]

I have a modification I made to the 2.2 code that would handle this, but only for QueryTypes, because to me, this feels like the only place walking across a catalog is practical. It would be possible to add the support for entity types as well, but I feel that in that case, you would be better off having a separate context for that purpose.

https://github.com/aspnet/EntityFrameworkCore/compare/release/2.2...mrswain:add-catalog-support

If this looks like it is a good approach I can submit a PR but I am not sure what tests may be needed or if some of my code changes are API no-nos.

Edit: I also added ,following the patter, support for a default catalog thought I did not limit it to just querytypes as I was unsure at the point it is implemented, on how to do that properly. This does limit the usefullness of a default catalog property.

[hnviradiya]

This feature would be great, because single database can't handle large datasets. So many use by separating them for different tenants. in that case some tables are shared. so this feature can be a game changer.

[divega]

@mrswain The approach seems ok to me in general. If you send a PR, we can discuss a few details there.

In the meantime, here are a few things that come to mind:

1. We usually try to triangulate with other databases besides SQL Server to decide if how first class a concept should be in our relational model and APIs. And I am not sure how database-independent this feature is.

2. I tend to agree that specifying a non-default catalog is strongly correlated with not wanting that EF Core migrations to try to maintain the database object. ToView implies the latter, so ToView is a good fit, and ToTable, not so much. FWIW, in 3.0 we are making query types just "entities with no keys" and whether you call ToTable or ToView becomes independent of whether the table has a key.

3. I may be wrong, but I don't think we want/need the ability to set a default catalog, at least for the scenario in which most tables are mapped to the database on which we opened the connection and only a few of them live somewhere else.

[ajcvickers]

Possible workaround: use a DiagnosticListener to intercept commands and add the catalog where necessary.

Proof of concept:
I have a server with two databases:

I now make an EF model pretending that the tables in these database are actually are in the same database:

```C#
public class DatabaseOne : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer(@"Server=(localdb)mssqllocaldb;Database=DatabaseOne;ConnectRetryCount=0");

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TableOne>().ToTable("TableOne", "SchemaOne");
    modelBuilder.Entity<TableTwo>().ToTable("TableTwo", "SchemaTwo");
}

}

public class TableOne
{
public int Id { get; set; }
public string Foo { get; set; }
}

public class TableTwo
{
public int Id { get; set; }
public string Foo { get; set; }
}

(Note this clearly won't work if both database contain tables with the same table and schema name.)

Now create an interceptor-like construct using `DiagnosticListener`. This will do pattern matching on the SQL to find tables that are in the other databases and add the appropriate catalog.

```C#
public class CommandInterceptor : IObserver<KeyValuePair<string, object>>
{
    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    public void OnNext(KeyValuePair<string, object> value)
    {
        if (value.Key == RelationalEventId.CommandExecuting.Name)
        {
            var command = ((CommandEventData) value.Value).Command;

            // Do command.CommandText manipulation here...
            command.CommandText = command.CommandText.Replace(
                "[SchemaTwo].[TableTwo]",
                "[DatabaseTwo].[SchemaTwo].[TableTwo]");
        }
    }
}

public class EfGlobalListener : IObserver<DiagnosticListener>
{
    private readonly CommandInterceptor _commandInterceptor = new CommandInterceptor();

    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    public void OnNext(DiagnosticListener listener)
    {
        if (listener.Name == DbLoggerCategory.Name)
        {
            listener.Subscribe(_commandInterceptor);
        }
    }
}

Registering and testing:
```C#
public class Program
{
public static void Main()
{
DiagnosticListener.AllListeners.Subscribe(new EfGlobalListener());

    using (var context = new DatabaseOne())
    {
        foreach (var entity in context.Set<TableOne>())
        {
            Console.WriteLine($"{entity.Id}: {entity.Foo}");
        }

        foreach (var entity in context.Set<TableTwo>())
        {
            Console.WriteLine($"{entity.Id}: {entity.Foo}");
        }
    }
}

}
```