none
F# TypeProviders Don't Work with AdventureWorks

    Question

  • I'm failing to create either a SQLDataConnection or an EntityDataConnection to AdventureWorks. I'm using F# 3.0 in Visual Studio 2012 and AdventureWorks 2012 on SQLExpress. I'm using syntax like this:

    type SqlConnection = SqlDataConnection<ConnectionString = @"Data Source=(localdb)\Projects;Initial Catalog=AdventureWorks2012;Integrated Security=True;">

    type entityConnection = SqlEntityConnection<ConnectionString="Server=(localdb)\Projects;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;MultipleActiveResultSets=True">

    Here are some of the errors I get from trying to create a SQLDataConnection to AdventureWorks:

    error FS3033: The type provider 'Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders' reported an error: Error reading schema. 
    Warning : SQM1021: Unable to extract column 'SpatialLocation' of Table 'Person.Address' from SqlServer because the column's DbType is a user-defined type (UDT).
    Error : Could not identify primary key column 'DocumentNode' in table 'Production.Document'.

    And here are some errors from trying to create a EntityDataConnection:

    warning 6005: The data type 'hierarchyid' is currently not supported for the target .NET Framework version; the column 'DocumentNode' in table 'AdventureWorks2012.Production.Document' was excluded.
    warning 6031: The column 'DocumentNode' on the table/view 'AdventureWorks2012.Production.Document' was excluded, and is a key column.  The table/view has been excluded.  Please fix the entity in the schema file, and uncomment.
    warning 6002: The table/view 'AdventureWorks2012.HumanResources.vEmployee' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
    error 6004: The table 'AdventureWorks2012.Production.Document' is referenced by a relationship, but cannot be found.
    warning 6005: The function 'uspUpdateEmployeeLogin' has a parameter 'OrganizationNode' at parameter index 1 that has a data type 'hierarchyid' which is currently not supported for the target .NET Framework version. The function was excluded.

    So there are some columns and tables that the data connectors don't like. Fine, but the upshot is that I can't create a data connector to _anything_ in the database.

    Is there a way to make the data connectors ignore things they don't support? Or does one unsupported data type in one table shut data connectors out of the entire database?
    Saturday, August 18, 2012 1:34 PM

Answers

  • The only workaround I see is to build an edmx model that either omits the offending data types or uses a stored procedure or view to convert them to something EF can understand; then point the edmx type provider to that model. This is extra work if you weren't already using EF in your project, or if you were using it code-first. And it raises the question whether the F# type provider adds any value over EF alone.

    In any case I don't see anything about these limitations in the F# documentation.

    Monday, August 20, 2012 1:24 PM
  • The use of F# type providers are not limited by the access to SQL databases data. They are useful in some cases when code generation is just not feasible, for example large scale Web data stores.

    And even in the case of SQL DB access using of type providers is much easier than EF or other ORM code generation. But I agree that limitations and domain of out-of-the-box F# type providers should be documented. It could help to avoid confusion in many cases.


    Petr

    Monday, August 20, 2012 1:39 PM

All replies

  • Under the hood F# type providers are using SQLMetal (LINQ to SQL) to generate DB schema. Till recently SQLMetal didn't supported new HierarchyId data type (introduced in SQL Server 2008, I believe). Not sure if this datatype is supported by SQLMetal now. From your error log it seems that it is still not supported.


    Petr

    Saturday, August 18, 2012 5:32 PM
  • Petr,

    I'm sorry to hear that, but what do we do?

    Do the existing type providers have workarounds?

    If not should we write our own type providers for SQL? Would that be a good use of our time?

    (Note this Microsoft comment: "[Y]ou should avoid writing a type provider where an ordinary (or even an existing) .NET library would suffice."http://msdn.microsoft.com/en-us/library/hh361034%28v=VS.110%29.aspx Did Microsoft follow its own advice?)

    Richard

    Sunday, August 19, 2012 9:41 PM
  • As I can see this is not a problem of type provider per se but general limitation of ORM technologies used in Microsoft stack. See for example: http://stackoverflow.com/questions/4316069/hierarchyid-in-entity-framework-not-working

    There also listed one of possible workarounds (changing database scheme).

    If this datatype is critical for your database then probably you have to write your own Type provider but I don't think it's feasible. Or use some other technology to access your database.


    Petr

    Monday, August 20, 2012 12:31 PM
  • The only workaround I see is to build an edmx model that either omits the offending data types or uses a stored procedure or view to convert them to something EF can understand; then point the edmx type provider to that model. This is extra work if you weren't already using EF in your project, or if you were using it code-first. And it raises the question whether the F# type provider adds any value over EF alone.

    In any case I don't see anything about these limitations in the F# documentation.

    Monday, August 20, 2012 1:24 PM
  • The use of F# type providers are not limited by the access to SQL databases data. They are useful in some cases when code generation is just not feasible, for example large scale Web data stores.

    And even in the case of SQL DB access using of type providers is much easier than EF or other ORM code generation. But I agree that limitations and domain of out-of-the-box F# type providers should be documented. It could help to avoid confusion in many cases.


    Petr

    Monday, August 20, 2012 1:39 PM