none
Entity Framework on Sql 2000 vs. Sql 2005 and ProviderManifestToken

    Question

  •  

    Hi Everybody,

     

    I have a problem with using the Entity Framework on Sql 2000 vs. Sql 2005. My software is a product released up to 400 customers who use Sql 2000, Sql 2005 or Sql 2008.

     

    When I try to call the First() Linq To Entity extension method on an Sql 2000 server like this:

     

    MyObjectContext objMyObjectContext = CreateObjectContext( existingConnection );

    var p = objMyObjectContext.Person.First();

     

    I got the System.Data.SqlClient.SqlException: Line 7: Incorrect syntax near '('. exception.

     

    Yes, the generated Sql Script is

    SELECT TOP(1) [Extent1].[Name] /*...*/ FROM [dbo].[Person] AS [Extent1]

    where the brackets of TOP(1) should be omitted.

     

    This time I changed the ProviderManifestToken="2005" setting in edmx file to 2000. This time, the TOP 1 worked without error.

    As I mentioned, I have several customers using all the currently supported SQL Server versions I don’t want to keep and distribute a build for SQL 2000 and a build for SQL 2005 of my application.

     

    I checked the ProviderManifestToken schema attribute in MSDN Library, and it was said that this setting is determined dynamically, so I shouldn’t deal with it.

     

    I think the problem is caused by the way I have to create the Entity ObjectContext.

    I have to use an existing SqlConnection to open the EntityConnection regardless of it has to be closed:

     

    public MyObjectContext GetObjectContext( SqlConnection connection )

    {

        EntityConnection ecn = null;

     

        bool isOpen = connection.State != System.Data.ConnectionState.Closed;

     

        if( isOpen ) connection.Close();

     

        try

        {

            ecn = new EntityConnection( new MetadataWorkspace( new string[]{ "res://*/" }, new Assembly[]{ Assembly.GetExecutingAssembly() } ), connection );

            return new MyObjectContext( ecn );

        }

        finally

        {

            if( isOpen )

            {

                if( ecn != null )

                    ecn.Open();

                else

                    connection.Open();

            }

        }

    }

     

    My goal would be

    -          Open an EntityConnection/Create the MyObjectContext using an existing connection

    -          The ProviderManifestToken setting should be determined by the Entity Framework at runtime.

     

    What kind of mistake I made?

     

    Thank you for your replies.

    Best regards,

     

                    Aron Kolozs

    Tuesday, February 10, 2009 8:45 AM

Answers

  • There are multiple reasons why token is in SSDL and why it should not be determined at runtime.

    Entity Framework requires store metadata (such as information about primitive types which are supported) to be available for many operations, such as: model validation (which happens every time you save your EDMX file), view generation (which happens at runtime or at build time), and the most important one query compilation. Some query features are dependent on the version of the server we're talking to, for example Skip() generates different T-SQL on SQL 2005 and SQL 2000).

    In pre-RTM releases we had this behavior, where we went to the database to determine the exact version number, but that was inconvenient for many reasons:

    - you couldn't perform model validation without specifying a connection string
    - you couldn't generate views (which usually happens at build machine) without connecting to the database
    - you couldn't determine EDM-level return type of the query without being connected to the database
    - you couldn't call ToTraceString() on a query before calling Connection.Open()

    There were many other reasons, but in the end we decided to move provider manifest token (and provider name) to SSDL. This way, we can resolve all our metadata questions without ever going to the database and all of the mentioned scenarios become possible.

    In fact, in the runtime API the provider has no way to go to the database and ask for current provider manifest token, because we're not providing it with the connection information. Token is only obtained during design time (for example when you reverse-engineer a database) and it is also reflected in the API.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 17, 2009 11:47 PM
    Moderator

All replies

  • The token is specified in SSDL - you could manually update it before creating the collection, or you could maintain two copies of it, each with different tokens, depending on the target database...
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 11, 2009 1:29 AM
    Moderator
  • There are multiple reasons why token is in SSDL and why it should not be determined at runtime.

    Entity Framework requires store metadata (such as information about primitive types which are supported) to be available for many operations, such as: model validation (which happens every time you save your EDMX file), view generation (which happens at runtime or at build time), and the most important one query compilation. Some query features are dependent on the version of the server we're talking to, for example Skip() generates different T-SQL on SQL 2005 and SQL 2000).

    In pre-RTM releases we had this behavior, where we went to the database to determine the exact version number, but that was inconvenient for many reasons:

    - you couldn't perform model validation without specifying a connection string
    - you couldn't generate views (which usually happens at build machine) without connecting to the database
    - you couldn't determine EDM-level return type of the query without being connected to the database
    - you couldn't call ToTraceString() on a query before calling Connection.Open()

    There were many other reasons, but in the end we decided to move provider manifest token (and provider name) to SSDL. This way, we can resolve all our metadata questions without ever going to the database and all of the mentioned scenarios become possible.

    In fact, in the runtime API the provider has no way to go to the database and ask for current provider manifest token, because we're not providing it with the connection information. Token is only obtained during design time (for example when you reverse-engineer a database) and it is also reflected in the API.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 17, 2009 11:47 PM
    Moderator
  • The provider manifest token should be setable at design time. Is there not a way to set it to 2005 in EF4???? We have customers that are on 2005. We want to maintain backwards compatiblity. We don't care that the designers are using SQL Server 2008, we want it to generate stuff with 2005 in mind.

     

    How? (without manually editing the edmx in xml every dam time we make a change to the schema?)

    Wednesday, May 12, 2010 2:22 PM
  • I agree with John - a compatibility mode would be useful.
    Tuesday, May 25, 2010 8:42 AM
  • I agree too.  This is a major problem.
    Tuesday, September 14, 2010 7:39 PM
  • I agree too. Its a real pain remembering to change this every time a release takes place.

    True it shouldn't matter what database server version a developer should use, however what does matter is that the database is set to the correct compatibility level. Therefore shouldn't the provider manifest token take its setting from the database compatibility level rather than the server version. 

     


    Saturday, October 16, 2010 7:33 AM
  • Has this issue been solved or at least a workaround?
    Thursday, May 10, 2012 11:18 AM
  • On 5/10/2012 7:18 AM, Cryo75 wrote:
    > Has this issue been solved or at least a workaround?
     
    You cannot use EF with SQL Server 2000 or lower versions of the SQL
    Server database. You can only use it with SQL Server 2005 or better.
     
    Thursday, May 10, 2012 11:39 AM
  • ....AND if your dev server is SQL 2008 or later but you still need to target 2005 with your entity framework then you MUST manually edit the EDMX file every single time you get changes from your database and update your model. (which is just silly)

    AND if you're using EF code first, as far as I can tell, you're SOL unless you're using migrations, in which case you can manually edit the migrations and make them use sql to add the fields that have conflicting, non-existent types etc.

    Thursday, May 10, 2012 12:35 PM
  • ....AND if your dev server is SQL 2008 or later but you still need to target 2005 with your entity framework then you MUST manually edit the EDMX file every single time you get changes from your database and update your model. (which is just silly)

    AND if you're using EF code first, as far as I can tell, you're SOL unless you're using migrations, in which case you can manually edit the migrations and make them use sql to add the fields that have conflicting, non-existent types etc.


    This is a regular issue that I run into.  My dev servers are all 2008, but we need to support 2005.  The workaround that I have been doing is to keep an empty 2005 database around (with the same structure as my 2008 db) and point the EF to that when loading new tables/columns.  It's not ideal since you have to keep them in sync, but it works.
    Thursday, May 10, 2012 3:39 PM