Entity Framework schema always empty in EdmFunction when generating code using T4

Question

My goal was to use Entity Framework stored procedures and functions in an async way, but the built-in support isn't there. The default T4 code only generates sync methods.

After a lot of searching and trial and error I settled with modifying my Model.Context.tt to generate appropriate code by calling ExecuteStoreCommandAsync and/or ExecuteStoreQueryAsync.

Unlike ExecuteFunction which simply wants the function name regardless of schema, ExecuteStoreCommandAsync also needs schema prefixed to the procedure name (e.g. [MySchema].[MyProcedure]).

The class EdmFunction apparently has a Schema property but it's empty for all my functions. If I open EDMX as text I can clearly see something like:

<Function Name="MyProcedure" Schema="MySchema">

The question is - how can I access the correct schema in TT for my stored procedure/function?

I am using EF 6.2 in a NET Framework 4.7 project.

Answer 1

EF6 metadata system is quite complicated, probably due to the attempt to cover too many scenarios - database first, code first and model first. They have separate metadata organized in so called dataspaces - store model, object model and conceptual model plus mappings between them.

The problem here is that the standard EF6 T4 generator uses the conceptual model. It's because ExecuteFunction and CreateQuery work with EntityCommands (Entity SQL) which later are transformed to "store" commands (raw SQL). While ExecuteStoreCommand[Async] and ExecuteStoreQuery[Async] work directly with "store" commands (raw SQL).

So what you need is an access to the "store" model. Note that both "conceptual" and "store" models contain EdmFunction objects, but their names are different, so are the parameter names, types etc. And since Schema makes sense only for "store" (database), that's why you are getting always null from the conceptual model.

Here is how you can load and get EdmFunctions from the store mode. The standard EF6 T4 template includes a file called EF6.Utility.CS.ttinclude which contains many helpers used by code generation. One of them is a class called EdmMetadataLoader with method CreateEdmItemCollection used by the standard template for loading the conceptual model from EDMX. It can be used as base to extract a method that we need, which looks like this (add it at the end of the modified Context.tt inside the section of code helpers - before the last closing #>):

private static StoreItemCollection CreateStoreItemCollection(string sourcePath, IDynamicHost host, System.Collections.IList errors)
{
    var root = XElement.Load(host.ResolvePath(sourcePath), LoadOptions.SetBaseUri | LoadOptions.SetLineInfo);
    var schemaElement = root.Elements()
        .Where(e => e.Name.LocalName == "Runtime")
        .Elements()
        .Where(e => e.Name.LocalName == "StorageModels")
        .Elements()
        .Where(e => e.Name.LocalName == "Schema")
        .FirstOrDefault() ?? root;
    if (schemaElement != null)
    {
        using (var reader = schemaElement.CreateReader())
        {
            IList<EdmSchemaError> schemaErrors;
            var itemCollection = StoreItemCollection.Create(new[] { reader }, null, null, out schemaErrors);
            foreach (var error in schemaErrors)
            {
                errors.Add(
                    new CompilerError(
                        error.SchemaLocation ?? sourcePath,
                        error.Line,
                        error.Column,
                        error.ErrorCode.ToString(CultureInfo.InvariantCulture),
                        error.Message)
                    {
                        IsWarning = error.Severity == EdmSchemaErrorSeverity.Warning
                    });
            }
            return itemCollection ?? new StoreItemCollection();
        }
    }
    return new StoreItemCollection();   
}

Then find the line

var itemCollection = loader.CreateEdmItemCollection(inputFile);

and insert the following line after it

var storeItemCollection = CreateStoreItemCollection(inputFile, textTransform.Host, textTransform.Errors);

Now you can replace the standard

foreach (var edmFunction in container.FunctionImports)
{
    WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);
}

with

var functions = storeItemCollection
    .GetItems<EdmFunction>()
    .Where(f => !f.IsFromProviderManifest)
    .ToList();
foreach (var edmFunction in functions)
{
#>
    // [<#=edmFunction.Schema ?? ""#>].[<#=edmFunction.Name#>]
<#
}

The body just outputs a comment with [Schema].[Name] of each db function import to prove the correct edmFunction.Schema property (the target of the question). Replace it with the actual code generation.

---

In case you need both conceptual (code) and storage (db) definitions of a function, you can create StorageMappingItemCollection in a similar fashion (the only difference is that it requires passing EdmItemCollection and StoreItemCollection that you already have in addition to the xml reader), e.g. (CreateStorageMappingItemCollection is the method you have to create and implement):

var storageMappingItemCollection = CreateStorageMappingItemCollection(
    (EdmItemCollection)itemCollection, storeItemCollection,
    inputFile, textTransform.Host, textTransform.Errors);

and then use

var functionImports = storageMappingItemCollection
    .GetItems<EntityContainerMapping>()
    .SelectMany(m => m.FunctionImportMappings)
    .ToList();

to get a list of FunctionImportMapping objects having two EdmFunction type properties: FunctionImport (conceptual model) and TargetFunction (storage model).

Update You really need to use the aforementioned "mapping" approach. FunctionImport provides the necessary information for defining the C# method (name, arguments, return type) while TargetFunction provides the information needed to call the db function/procedure.

So the helper method looks like this:

private static StorageMappingItemCollection CreateStorageMappingItemCollection(EdmItemCollection edmItemCollection, StoreItemCollection storeItemCollection, string sourcePath, IDynamicHost host, System.Collections.IList errors)
{
    var root = XElement.Load(host.ResolvePath(sourcePath), LoadOptions.SetBaseUri | LoadOptions.SetLineInfo);
    var schemaElement = root.Elements()
        .Where(e => e.Name.LocalName == "Runtime")
        .Elements()
        .Where(e => e.Name.LocalName == "Mappings")
        .Elements()
        .Where(e => e.Name.LocalName == "Mapping")
        .FirstOrDefault() ?? root;
    if (schemaElement != null)
    {
        using (var reader = schemaElement.CreateReader())
        {
            IList<EdmSchemaError> schemaErrors;
            var itemCollection = StorageMappingItemCollection.Create(edmItemCollection, storeItemCollection, new[] { reader }, null, out schemaErrors);
            foreach (var error in schemaErrors)
            {
                errors.Add(
                    new CompilerError(
                        error.SchemaLocation ?? sourcePath,
                        error.Line,
                        error.Column,
                        error.ErrorCode.ToString(CultureInfo.InvariantCulture),
                        error.Message)
                    {
                        IsWarning = error.Severity == EdmSchemaErrorSeverity.Warning
                    });
            }
            if (itemCollection != null) return itemCollection;
        }
    }
    return new StorageMappingItemCollection(edmItemCollection, storeItemCollection);    
}

and the sample usage:

var storageMappingItemCollection = CreateStorageMappingItemCollection(
    (EdmItemCollection)itemCollection, storeItemCollection,
    inputFile, textTransform.Host, textTransform.Errors);

var functionImports = storageMappingItemCollection
    .GetItems<EntityContainerMapping>()
    .SelectMany(m => m.FunctionImportMappings)
    .ToList();

foreach (var item in functionImports)
{
#>
    // <#=item.FunctionImport.Name#> => [<#=item.TargetFunction.Schema ?? ""#>].[<#=item.TargetFunction.Name#>]
<#
}