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, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, TaskCompletionSource`1 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, Func`3 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.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.Data.Entity.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WebApplication1.Controllers.IdentsController.Details(Nullable`1 id) in D:\git\vnext\WebApplication1\src\WebApplication1\Controllers\IdentsController.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.

[rigofunc]

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

[Bitsonthefloor]

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:

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.

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:

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

[John0King]

Should there is a Option for DbContext to just inlucde DatabaseName in the Query ? I mean for different Database , EF Core should use different DbContext, and with an option to specify the DatabaseName for the DbContext

[DatabaseName("Db1")]
public class Db1DbContext :DbContext{}

//or

public class Db2DbContext:DbContext
{
   override onModelCreate(ModelBuilder builder)
{
    builder.UseDatabaseName("Db2")
}
} 

[alexreich]

Another workaround (similar to @ajcvickers answer above and derived from this blog) - this removes hardcoding of database names:

using Microsoft.EntityFrameworkCore;  
using Microsoft.EntityFrameworkCore.Metadata.Builders;  

namespace MulitpleDb.Sample.Data  
{  
    public class FooConfiguration : IEntityTypeConfiguration<FooTableClass>  
    {  
        public void Configure(EntityTypeBuilder<Planet> builder)  
        {  
            builder.ToTable("FooTable","[Database2].[dbo]");
            builder.HasKey(e => e.Id);  
            builder.Property(a => a.Name).IsRequired();  
        }  
    }  
}  

Interceptor Workaround classes:

    public class GlobalCommandInterceptor : 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;
                command.CommandText = command.CommandText.Replace(
                    "[[",
                    "[").Replace("]]]", "]").Replace("]]", "]");
            }
        }
    }
    public class GlobalListener : IObserver<DiagnosticListener>
    {
        private readonly GlobalCommandInterceptor _commandInterceptor = new GlobalCommandInterceptor();
        public void OnCompleted()
        {
        }

        public void OnError(Exception error)
        {
        }

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

and called in Startup.cs:

DiagnosticListener.AllListeners.Subscribe(new GlobalListener());

[John0King]

From 2015-12 to 2021-8 , almost 6 years , anything new on this (expect drop from next version again)

[roji]

@John0King see this for how we decide what to work on. This issue has received only 5 votes since it was opened in 2015, and there is are reasonable workaround (i.e. introduce the database name directly into the SQL via a command interceptor).

[tbasallo]

The workarounds work. However, it seems a little heavy handed. I now have to listen to, check and potentially change every query. I am not sure of the overall impact since I just tested this and it works, but, jeez. I am a little concerned.

I realize that multiple databases are perhaps not very common (maybe??), I've moved our team into Azure SQL and PAAS which doesn't support cross-database queries (to get away from maintenance and administration) and I now use schemas for what we used to use databases for. I just happened to find myself in someone else's org doing work on servers that have upwards of 30 databases each and I wanted to use EF core and not start hand-writing SQL again...

Food for thought, I work in a very large organization where every non-PAAS SQL database uses multiple databases. It's a culture thing in this enterprise (the Oracle databases we have do the same). They would be hard-pressed to move to EF for this alone. And they would not bother coming in here and voting for this to ensure the team sees it as important. I think most people are this way. While I know you need some way to determine what's important, somethings should just work if they work in the underlying systems (like cross-database queries).