search

Dixin / EntityFramework.Functions

EntityFramework.Functions library implements Entity Framework code first support for stored procedures (with single result type, multiple result types, output parameter), table-valued functions (returning entity type, complex type), scalar-valued functions (composable, non-composable), aggregate functions, built-in functions, niladic functions, and model defined functions.
https://weblogs.asp.net/Dixin/EntityFramework.Functions
MIT License
79 stars 27 forks source link

TVF returning EntityType unable to use Code-First #28

Open 09setht opened 6 years ago

09setht commented 6 years ago

If the Table-Valued Function returns an Entity Type, the DbContext will fail to deploy using Code-First. I found the root cause of this to be that the Types of the properties were CLR types (Int32, String, Guid, etc.) instead of SQL types (int, nvarchar, uniqueidentifier, etc.). More specifically, in the function called by test, the edmx under StorageModels looked like:

<RowType>
    <Property Name="BusinessEntityID" Type="Int32" Nullable="false" p10:StoreGeneratedPattern="Identity" xmlns:p10="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
    <Property Name="Title" Type="String" MaxLength="8" FixedLength="false" Unicode="true" Nullable="true" />
    <Property Name="FirstName" Type="String" MaxLength="50" FixedLength="false" Unicode="true" Nullable="true" />
    <Property Name="LastName" Type="String" MaxLength="50" FixedLength="false" Unicode="true" Nullable="true" />
</RowType>

but should be:

<RowType>
    <Property Name="BusinessEntityID" Type="int" Nullable="false" p10:StoreGeneratedPattern="Identity" xmlns:p10="http://schemas.microsoft.com/ado/2009/02/edm/annotation" />
    <Property Name="Title" Type="nvarchar" MaxLength="8" FixedLength="false" Unicode="true" Nullable="true" />
    <Property Name="FirstName" Type="nvarchar" MaxLength="50" FixedLength="false" Unicode="true" Nullable="true" />
    <Property Name="LastName" Type="nvarchar" MaxLength="50" FixedLength="false" Unicode="true" Nullable="true" />
</RowType>

A fix I found for this was to change this section of Function.GetStoreReturnParameters in Function.DbModel.cs from:

storeReturnParameterRowType = RowType.Create(
             modelReturnParameterEntityType.Properties.Select(property => property.Clone()), null);

to:

storeReturnParameterRowType = RowType.Create(
               modelReturnParameterEntityType.Properties.Select(property =>
                                {
                                    var typeUsage = TypeUsage.Create(model.ProviderManifest.GetStoreType(property.TypeUsage).EdmType, property.TypeUsage.Facets);
                                    var result = EdmProperty.Create(property.Name, typeUsage);
                                    var propertyNames = new[] { nameof(EdmProperty.Name), nameof(EdmProperty.TypeUsage), nameof(EdmProperty.MetadataProperties) };
                                    result.SetMetadataProperties(property.MetadataProperties.Where(m => !propertyNames.Contains(m.Name)));
                                    return result;
                                }),
                            null);

Using this edit, I was able to produced the expected edmx. Overall, I have noticed that all the tests for this project are written against a database loaded from file rather than one generated when running the tests. This seems to be a limitation when testing the application of this library.

@Dixin would you be able to verify and integrate this change? Also, would you consider testing the use of Code-First deployment with this library?