locked
Cross DB calls Entity framework Raw SQL queries RRS feed

  • Question

  • I am using Entityframework 6.0

    We have a legacy application, we are migrating SQL queries that uses ADO.NET to Raw SQL queries of Entity framework.

    We have many cross DB queries in the legacy application.

    cross DB what I mean is: 

    TableA in DB1 joins with TableB in DB2 using a table view created in DB1. Both of the DB's are on the same server.

    ex: Table View name in DB1: vwDetailHistory having query as

    select * from DB2..DetailHistory

    Using entity framework (Database.ExecuteSqlCommand) I get error 

    Invalid object name 'DB2..DetailHistory' Could not use view or function 'vwDetailHistory' because of binding errors.

    I came to know from many threads that we can't do cross DB queries using Entityframework

    Is there a work around for this as I am using Raw SQL queries but not the models with DB context?


    Wednesday, March 14, 2018 10:46 AM

Answers

  • Hi Chaitu D,

    Based on your description, I create a simple sample as below, and the two database in the same server, which works.

    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp1
    {
        public class TestQuery
        {
            public string Title { get; set; }
            public string Content { get; set; }
            public string Name { get; set; }
    
        }
        class Program
        {
            static void Main(string[] args)
            {
              
                using (var db = new EFDemoContext())
                {
                    var query = db.Database.SqlQuery<TestQuery>("select a.Title, a.Content, b.Name from EFDemo.dbo.Posts a join Sales.dbo.Blogs b on a.BlogId = b.BlogId").ToList();
    
                    Console.ReadKey();
                    
                }
            }
                
        }
    }
    

    Could you please provide a bit more information, which we could reproduce the issue on our side, such as a complete simple sample.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by crazy coding Thursday, March 15, 2018 12:40 PM
    Thursday, March 15, 2018 8:34 AM

All replies

  • Is there a work around for this as I am using Raw SQL queries but not the models with DB context?

    No there is no workaround, and your solution should be using the Repository pattern  a generic Repository that is using the ADO.NET traditionally.

    https://msdn.microsoft.com/en-us/library/ff649690.aspx?f=255&MSPPError=-2147217396

    A non generic Repository that is using the DAO pattern so that you can do things across database context using ADO.NET and SQL Command objects.

    http://blog.sapiensworks.com/post/2012/11/01/Repository-vs-DAO.aspx

    You will need to possibly use the DTO pattern as an alternate model.

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    • Marked as answer by crazy coding Thursday, March 15, 2018 10:00 AM
    • Unmarked as answer by crazy coding Thursday, March 15, 2018 10:04 AM
    Wednesday, March 14, 2018 3:29 PM
  • Hi Chaitu D,

    Based on your description, I create a simple sample as below, and the two database in the same server, which works.

    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp1
    {
        public class TestQuery
        {
            public string Title { get; set; }
            public string Content { get; set; }
            public string Name { get; set; }
    
        }
        class Program
        {
            static void Main(string[] args)
            {
              
                using (var db = new EFDemoContext())
                {
                    var query = db.Database.SqlQuery<TestQuery>("select a.Title, a.Content, b.Name from EFDemo.dbo.Posts a join Sales.dbo.Blogs b on a.BlogId = b.BlogId").ToList();
    
                    Console.ReadKey();
                    
                }
            }
                
        }
    }
    

    Could you please provide a bit more information, which we could reproduce the issue on our side, such as a complete simple sample.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by crazy coding Thursday, March 15, 2018 12:40 PM
    Thursday, March 15, 2018 8:34 AM
  • actually I am not able to reproduce that in a standalone application, where as in my application it shows stack trace as below...

    I need to check if something wrong in the application!

     System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'DB2..DetailHistory'.
    Could not use view or function 'DetailHistory' because of binding errors.
       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.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, Boolean describeParameterEncryptionRequest)
       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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
       at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
       at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)
       at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
       at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
       at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)

    Thursday, March 15, 2018 12:09 PM
  • @D

    How can you say that it works and you got an answer when it's blowing up in your face? :)

    Thursday, March 15, 2018 3:41 PM
  • I can understand your feeling :)

    Answer given by Zhanglong made me to realize the problem is with my application, which I still need to figure out where it is, as it works fine with standalone implementation.

    Friday, March 16, 2018 4:27 AM