Code First TVF in 6.1.0-alpha1-30113

Topics: EF Runtime
Jan 13 at 9:30 PM
Edited Jan 13 at 9:32 PM
EF People,

My understanding is that the newly made public APIs for metadata will allow us to add enough metadata in to the model so that TVF can be called and composable.

If anyone can point me in the right direction I would greatly appreciate it. Without Composable TVF I have to jump through some major work a rounds. Even considering dumping code first. :(



From looking at the unit test it looks like something a long this line of thought:
 var functionImport = EdmFunction.Create()
                "Foo", "Bar", DataSpace.CSpace,
                new EdmFunctionPayload
                {
                    IsComposable = true,
                    IsFunctionImport = true,
                    ReturnParameters = new[]
                               {
                                   FunctionParameter.Create("functionname", EdmType.GetBuiltInType()
                                       EdmConstants.ReturnType,
                                       TypeUsage.Create(collectionTypeMock.Object),
                                       ParameterMode.ReturnValue),
                               }
                });
...
entityContainer.AddFunctionImport(functionImport);
Thanks,
Brian F
Jan 20 at 3:00 PM
I'm also very interested in this kind of functionality and how it may become possible in Code First scenarios with Entity Framework 6.1.0 as the metadata opens up. I've done some rudimentary spike projects and have not yet met with success. I am curious if this is because I am doing this wrong, because of bugs still remaining in the code or because this is not intended to be supported functionality.

Of the two spikes I've written I've run into issues in two places.

First, if I attempt to add the function import in the conceptual model within a convention I get a model validation error when it attempts to validate the name of the return type of that function, which is set to a CollectionType of that ComplexType. I've compared the name of the metadata item in the MetadataWorkspace from Model First and the names are identical so I think that the validation in Code First is incorrect. The model validation is EdmModelSyntacticValidationRules.EdmModel_NameIsNotAllowed and the name in question here is "collection[CodeFirstNamespace.Sequence_GetInt32s_Result(Nullable=True,DefaultValue=)]". If I do this outside of a convention after the DbModel is built then I can avoid this error.

Second, whether I attempt to do this in conventions or after the DbModel is built, attempting to create the instance of FunctionMappingImportComposable fails. I retrieve the EntityContainerMapping from the ConceptualToStoreMapping property of the DbModel but when passing that to the ctor of FunctionMappingImportComposable a NullReferenceException occurs because the StoreMappingItemCollection of that EntityContainerMapping is null.

I'm in the same boat about Code First. I have a business need which includes mapping to an existing database complete with scalar and tabular functions but also that other assemblies can augment the DbContext to publish additional entities and so on. My team is on the verge of abandoning both the EDMX Designer and Code First for something built in-house (possibly hosted on CodePlex, still considering our options) to bring the fully functionality of EntityFramework to a fluent-API. This includes functionality that not even the EDMX Designer appears to support, like mapping entities to arbitrary subqueries, but EntityFramework supports just fine.

I'd be more than happy to share the code of my spikes if it would be useful and submit issues if these experiences are not caused by incorrectly using the API.

Thanks
Feb 6 at 3:32 PM
Halo_Four,


If you have any code snippets that would be greatly appreciated. I just finished a major sprint and have some extra cycles to devote to testing it out.

Sadly, the current work has a terrible work around. I put the results of the TVF in to a table with the TVF parameters as part of the keys. I call the TVF 1st, then I call the normal EF code with the TVF parms in the where predicate.

I hate coding when I need to leave behind a disclaimer for future people who will wonder "WTF was he thinking".

Can't wait for EF 6.1.


-Brian F
Developer
Feb 12 at 6:02 AM
Folks,

I checked in a change yesterday that enables using TVFs (and stored procs) in Code First. Unfortunately it did make it for EF6.1 Beta that shipped today. To test that the changes really work I created a custom convention that maps TVFs and stored proc methods from the context to the corresponding store functions. I published the code for that on codeplex: https://codefirstfunctions.codeplex.com/. It still needs some polish but should work in general. Once the change I checked in to EF yesterday ships I am planning to publish the convention on NuGet. Note that the https://codefirstfunctions.codeplex.com/ project should be treated just as a sample and is not part of the Entity Framework (or any other Microsoft) project.

Hope this helps,
Pawel
Feb 12 at 4:54 PM
Looks good. I grabbed the nightly and was able to map a simple TVF that returns an IQueryable<int>. I'm now working on testing mapping to an existing entity. I, of course, chose a more complicated scenario where the TVF returns column with names that don't match the property names of the entity and I'm struggling with the return type mapping but I'm sure that's just my inexperience with the metadata API.
Feb 13 at 1:21 PM
So I've been able to successfully map TVFs that return an IQueryable of a scalar type like int as well as TVFs that returns an IQueryable of an entity type where the table schema of the TVF matches that of the entity directly. However, I've been having issues with mapping to a TVF that returns an IQueryable of an entity type where the property names differ from the column names. I get a model validation error on DbModel.Compile that the properties have not been mapped.

The entity type in question is as follows:
public class AccountDataObject
{
    public int FileNumber { get; set; }
    public string CustomerAccountNumber { get; set; }
}
The signature of the TVF in T-SQL is as follows:
CREATE FUNCTION dbo.GetAccounts (@number INTEGER)
RETURNS @results TABLE (
    number INTEGER,
    account VARCHAR(50)
)
AS BEGIN ...
END;
The static function that I'm attempting to map to the TVF is as follows:
public static class TvfFunctions
{
    [DbFunction("CodeFirstContainer", "GetAccounts")]
    public static IQueryable<AccountDataObject> GetAccounts(int number)
    {
        throw new NotImplementedException();
    }
}
The following is the code I'm using against the DbModel instance to attempt to map the TVF:
DbModelBuilder builder = new DbModelBuilder(DbModelBuilderVersion.Latest);
builder.Configurations.Add(new AccountDataObjectConfiguration());

DbModel model = builder.Build(new DbProviderInfo("System.Data.SqlClient", "2008"));

TypeUsage intUsage = model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32)));
TypeUsage varcharUsage = model.ProviderManifest.GetStoreType(TypeUsage.CreateStringTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), false, false, 50));

EdmFunctionPayload storePayload = new EdmFunctionPayload()
{
    Schema = "dbo",
    StoreFunctionName = "GetAccounts",
    IsComposable = true,
    Parameters = new List<FunctionParameter>(),
    ReturnParameters = new List<FunctionParameter>()
};

storePayload.Parameters.Add(FunctionParameter.Create("number", intUsage.EdmType, ParameterMode.In));

EdmProperty numberProperty = EdmProperty.Create("number", intUsage);
numberProperty.Nullable = false;
EdmProperty accountProperty = EdmProperty.Create("account", varcharUsage);

RowType rowType = RowType.Create(new EdmProperty[] { numberProperty, accountProperty }, null);

storePayload.ReturnParameters.Add(FunctionParameter.Create("retval", rowType.GetCollectionType(), ParameterMode.ReturnValue));

EdmFunction storeFunction = EdmFunction.Create("GetAccounts", TestFunctions.Namespace, DataSpace.SSpace, storePayload, null);

model.StoreModel.AddItem(storeFunction);

EntityType entityType = model.ConceptualModel.EntityTypes.Single(et => string.Equals(et.Name, "AccountDataObject"));
EdmFunctionPayload conceptualPayload = new EdmFunctionPayload()
{
    IsComposable = true,
    IsFunctionImport = true,
    Parameters = new List<FunctionParameter>(),
    ReturnParameters = new List<FunctionParameter>(),
    EntitySets = model.ConceptualModel.Container.EntitySets.Where(es => es.ElementType == entityType).ToList()
};
conceptualPayload.Parameters.Add(FunctionParameter.Create("number", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32), ParameterMode.In));
conceptualPayload.ReturnParameters.Add(FunctionParameter.Create("retval", entityType.GetCollectionType(), ParameterMode.ReturnValue));

EdmFunction conceptualFunction = EdmFunction.Create("GetAccounts", "CodeFirstContainer", DataSpace.CSpace, conceptualPayload, null);

model.ConceptualModel.Container.AddFunctionImport(conceptualFunction);

FunctionImportResultMapping resultMapping = new FunctionImportResultMapping();
FunctionImportEntityTypeMapping typeMapping = new FunctionImportEntityTypeMapping(
    new EntityType[0],
    new EntityType[] { entityType },
    new Collection<FunctionImportReturnTypePropertyMapping>()
    {
        new FunctionImportReturnTypeScalarPropertyMapping("FileNumber", "number"),
        new FunctionImportReturnTypeScalarPropertyMapping("CustomerAccountNumber", "account")
    },
    new FunctionImportEntityTypeMappingCondition[0]);
resultMapping.AddTypeMapping(typeMapping);
FunctionImportMappingComposable mapping = new FunctionImportMappingComposable(conceptualFunction, storeFunction, resultMapping, model.ConceptualToStoreMapping);
model.ConceptualToStoreMapping.AddFunctionImportMapping(mapping);

DbCompiledModel compiled = model.Compile();
The exception I'm getting is as follows:
Schema specified is not valid. Errors: 
(12,6) : error 2104: No mapping specified for the conceptual property 'FileNumber' of type 'CodeFirstNamespace.AccountDataObject' in the result mapping of the function import 'CodeFirstContainer.GetAccounts(In Edm.Int32(Nullable=True,DefaultValue=))'.
(12,6) : error 2104: No mapping specified for the conceptual property 'CustomerAccountNumber' of type 'CodeFirstNamespace.AccountDataObject' in the result mapping of the function import 'CodeFirstContainer.GetAccounts(In Edm.Int32(Nullable=True,DefaultValue=))'.
I don't doubt that I'm getting the metadata mapping wrong, but I'm not sure how. Any insight would be appreciated.
Developer
Feb 13 at 4:35 PM
@Halo_Four: due to time constraints related to 6.1 schedule I was only able to verify (and fix) code that does simple mapping. I am pretty much sure that for stored procs nothing else (like more complicated mappings) would currently work because I let EF infer the mapping. For TVFs however, since I already build a simple mapping for complex types I thought it might be possible to do the same for Entity Types. Another reason why I thought more flexible mapping was of a less priority was that there is currently no API I could use to configure the mapping for function imports and using a convention and simple mapping is kind of in-line with the default behavior of CodeFirst. Anyways, I will try to see whether it is possible to do more complicated mapping for entity types but it won't be before weekend.
Feb 14 at 7:25 PM
This looks great! Is there potential for this feature making it into EF 6.1, or will this fore sure be in a post-6.1 release?
Developer
Feb 14 at 8:45 PM
@drmcclelland: As explained above this convention is not part of EF (or any other Microsoft) project. I cannot promise anything but the changes that made creating this convention possible are currently checked in to the 6.1 release branch. Once a version of EF containing the required changes is shipped I am planning to release a NuGet package containing this convention so that it is easy to use in your EF6 app.
Feb 17 at 8:24 PM
No problems. I'm quite grateful for the capabilities exposed thus far and they would serve probably all of my needs. If you managed to get the underlying metadata code to support complex mapping that would be icing on the cake.

I have created my own convention and attributes for 6.0.2 for mapping scalar UDFs and I would probably just extend them for supporting TVFs. This is all internal for now but I'd love to collaborate. I haven't had the opportunity to look into the Entity Framework source but I'd be happy to contribute. I am very interested in anything that brings the fluent API closer to supporting the full mapping capabilities of Entity Framework.
Feb 18 at 12:13 PM
I was poking around in the code last night and found the reason why the mappings weren't working. The MslXmlSchemaWriter class is simply not emitting any ResultMapping and EntityTypeMapping elements. I modified the code locally to check if the structural type is EntityType and if so to emit the EntityTypeMapping element and then emit the ScalarProperty elements in the same fashion as the ComplexTypeMapping and the code functions just fine:
public void WriteFunctionImportMappingElement(FunctionImportMappingComposable functionImportMapping)
{
    DebugCheck.NotNull(functionImportMapping);

    WriteFunctionImportMappingStartElement(functionImportMapping);

    if (functionImportMapping.StructuralTypeMappings != null && functionImportMapping.StructuralTypeMappings.Count > 0)
    {
        _xmlWriter.WriteStartElement(MslConstructs.FunctionImportMappingResultMapping);

        Debug.Assert(
            functionImportMapping.StructuralTypeMappings.Count == 1,
            "multiple result sets not supported.");

        var structuralMapping = functionImportMapping.StructuralTypeMappings.Single();

        if (structuralMapping.Item1.BuiltInTypeKind == BuiltInTypeKind.ComplexType)
        {
            _xmlWriter.WriteStartElement(MslConstructs.ComplexTypeMappingElement);
            _xmlWriter.WriteAttributeString(MslConstructs.ComplexTypeMappingTypeNameAttribute, structuralMapping.Item1.FullName);
        }
        else 
        {
            _xmlWriter.WriteStartElement(MslConstructs.EntityTypeMappingElement);
            _xmlWriter.WriteAttributeString(MslConstructs.EntityTypeMappingTypeNameAttribute, structuralMapping.Item1.FullName);
        }

        foreach (ScalarPropertyMapping propertyMapping in structuralMapping.Item3)
        {
            WritePropertyMapping(propertyMapping);
        }

        _xmlWriter.WriteEndElement();
        _xmlWriter.WriteEndElement();
    }

    WriteFunctionImportEndElement();
}
You mentioned time constraints leading up to the 6.1.0 release, I was curious if you thought that this change could make it in within those constraints.
Developer
Feb 19 at 7:05 AM
I have not looked into it yet but you are most likely right - the MslXmlSchemaWriter class typically does not write elements that are either not supported in CodeFirst or are not used by the EF designer. When I added the WriteFunctionImportMappingElement method due to time constraints I decided to support only the simplest possible thing and hence code for writing mappings for entities was missing.
EF6.1 is pretty much closed at this point - for runtime we can only check in fixes to critical bugs and can't take pull requests. Note that you can still submit a pull request and, if accepted, it will be checked in to the master branch and should be part of the next release.
Feb 19 at 2:20 PM
D'oh, if only I had an extra week. Currently waiting on the bureaucracy to approve the Contribution License Agreement otherwise I could've submitted the pull request already, but I imagine that by the time you posted about this change that it was already too late to consider such requests. Oh well, as I said the ability to map TVFs at all is of huge benefit and if I can map and rename to complex types then I could just use that against existing entity sets to obtain the actual entities.
Developer
Feb 19 at 4:12 PM
You are correct. The changes to support FunctionImports in its current form was one of the last changes we took for EF 6.1 runtime. I would still encourage you to submit this pull request though - once it is checked in it will be part of signed nightly builds. btw. Are you waiting for accepting CLA on our side or on your side?
Feb 19 at 5:51 PM
It's my side. I hadn't looked into what would be required to submit to CodePlex up until I found this code change and then noticed the whole CLA requirement so that's going through review at my employer. That's why I mentioned the change here first as I thought that maybe an interested and already enabled party (like yourself :) ) could enact it on a faster time scale. Once all of the CLA stuff is done I'll be happy to submit a pull request.
Developer
Apr 10 at 2:32 PM
I finally published the convention that allows using TVFs and SProcs in EF6.1 Code First on NuGet. Here is the link: http://www.nuget.org/packages/EntityFramework.CodeFirstStoreFunctions and here is the link to the blogpost containing all the details: http://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/

Thanks,
Pawel
Apr 11 at 12:11 AM
Sorry about the delay, work and other aspects of life were keeping me from working on this.

So I made changes to my fork to include the changes you mentioned. I added condition support for composable functions. I also added support for any number of result mappings in non-composable functions to entities or complex types, including support for conditions. I added tests which should cover all of these scenarios.

I did have some trouble writing the tests for the non-composable functions what with the result mapping requiring a ContainerMapping with a StorageMappingItemCollection. I think I'm setting that stuff up correctly but I'm not sure.

Check out the pull request I just submitted. If you feel that it's premature feel free to smack me around.
Developer
Apr 11 at 4:51 PM
Thanks - will take a look at let you know.