none
Can Entity Framework work with Linked Server RRS feed

  • Question

  • Hi,

     

    First, sorry for my english. Please feel free to let me know if it is unclear to you.

     

    LinqToSQL can work well with the linked server, only what I need to do is using LinkedServername.DatabaseName.dbo.TableName" as the table's source name.

     

    But when I tried this with Entity Framewrok, it failed. I think it is caused by the redundancy  "[" "]" in the TrackString.

    --------------

    <!-- SSDL content -->

    <edmxTongue TiedtorageModels>

    <Schema Namespace="TestModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2000" xmlnsTongue Tiedtore="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

    <EntityContainer Name="TestContainer">

    <EntitySet Name="TableName" EntityType="TestModel.Store.TableName" store:Type="Tables" Schema="LinkedServername.databasename.dbo" store:Name="TableName"/>

    ...........................

     

    -----------------------TrackString--------------

    SELECT
    [Extent1].[column1Name] AS [Column1Name],
    [Extent1].[column2Name] AS [column2Name],
    [Extent1].[column3Name] AS [column3Name]
    FROM [LinkedServername.databasename.dbo].[TableName] AS [Extent1]

    ------------------------------

     

    Does Entity Framework support the Linked Server? If it do, then how?

     

    Thanks

    Friday, September 26, 2008 6:49 AM

Answers

  • Hi Lin,

     

    This is a limitation in the current (V1) version of Entity Framework.

    Defining a Table name like: Schema="LinkedServername.databasename.dbo"

    will cause the entire name to be boxed in the final SQL generated : [LinkedServername.databasename.dbo]

     

    In order to get around this problem, you can create a view which represents the linked table, and map to this view instead. Please let me know if this solves your problem.

     

    Thanks,

    Akhil Karkera

    Wednesday, October 8, 2008 10:29 PM

All replies

  •  

    up,up, anyone can help? thanks very much!
    Wednesday, October 8, 2008 2:33 AM
  • Hi Lin,

     

    This is a limitation in the current (V1) version of Entity Framework.

    Defining a Table name like: Schema="LinkedServername.databasename.dbo"

    will cause the entire name to be boxed in the final SQL generated : [LinkedServername.databasename.dbo]

     

    In order to get around this problem, you can create a view which represents the linked table, and map to this view instead. Please let me know if this solves your problem.

     

    Thanks,

    Akhil Karkera

    Wednesday, October 8, 2008 10:29 PM
  • Hi Akhil,
    I have used your suggestion of a view that wraps a linked server to an Access database and the select functions work fine.


    I get an error trying to update however:
    The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" does not support the required transaction interface.

    Is there are way to turn off transactions for the storage model or some other work around??


    Cheers
    Simon

    (Incidentally, I had to design the model against a local sql dummy version of the table to get the designer to work nicely then I simply changed the connection to the db with the wrapper views. I would love better support in the designer for linked servers and views for that matter as we do a lot of integration work.)
    The problem with views is that the designer does not detect that underlying Primary key, and rather than carrying on to create the storage, conceptual and mapping objects it simply gives up and leaves you with a commented storage model then you have to finish the conceptual model and mapping stuff yourself.
    It would be great if the designer finished this build out process then let you know you have to add the Primary Key. Smile
    Tuesday, November 4, 2008 9:19 PM
  • Hello Akhil and Simon,

    Problem:

         I'm trying to get the same functionality and I had the same problems (including the designer problem you mentioned).
    Your workarounds work fine. However I have the same problem with Access not supporting transactions. (Access simply does not implement it.)

    Attempts:
         I tried to use an entity model with Stored Procedure Mapping (Insert / Delete / Update) but my stored procedures execute inside a transaction. And I have no idea how to prevent this.

         Calling COMMIT TRANSACTION in the procedure doesn't help (raises an exception about transaction count). Calling COMMIT TRANS. and BEGIN TRANS raises another exception . Changing MultipleActiveResultSets in connection string didn't help either. Despite thrown exceptions, my record saves to the Access Database successfully...

         But this is not the workaround I'm looking for. 1) I don't want to COMMIT transaction I haven't started. 2) I don't want to handle those exceptions.

         I tried implementing OnSaveChanges partial method to call Update / Insert / Delete procedures manually. This works fine. But I'm still having problems with Relationship Object State Entries. AcceptChanges method does not prevent them from being default handled and throws an exception.

    Question:
    Is there a way to prevent Entity Framework call mapped stored procedures inside a transaction ? Or any other way to use MS Access databases with Entity Framework ?

    Sincerely
    Ondrej Gratz
    Monday, November 10, 2008 1:57 PM
  •  
    Hi Lin,

    The Entity Framework support Linked Server. But We've to make some changes.

    Following:

    We must create synonyms for all tables using Linked Server to be accessed.

    See a code: CREATE SYNONYM [dbo].[TableOtherServer] FOR [Server].[DataBase].[dbo].[TableOtherServer]

    After you create all synonyms must change the property XACT_ABORT to ON by default.

    See a code:

    exec sp_configure 'show advanced options',1
    go
    RECONFIGURE
    go
    exec sp_configure 'user options',16384
    go
    RECONFIGURE
    go

    This settings allows to create transation between different database.

    Once the database is set up you should create the tables that are used for Linked Server in the database's application to create the physical relationship between them and to be able to map the Entity Framework. Then we can map the EF for those tables that we created. When the mapping is complete in EF is due to change property Entity Set Name to the name that was created in the Synonym. Then just save and test.

    Regarding

     


    "A próxima grande evolução da humanidade será a descoberta de que cooperar é melhor que competir"
    • Proposed as answer by RickyLin Friday, November 4, 2011 6:52 AM
    Friday, July 16, 2010 5:50 PM