Entity Framework - Table-Valued Functions - Parameter Already Exists

Question

I am using table-valued functions with Entity Framework 5. I just received this error:

A parameter named 'EffectiveDate' already exists in the parameter collection. Parameter names must be unique in the parameter collection. Parameter name: parameter

It is being caused by me joining the calls to table-valued functions taking the same parameter.

Is this a bug/limitation with EF? Is there a workaround? Right now I am auto-generating the code (.edmx file).

Answer 1

It would be really nice if Microsoft would make parameter names unique, at least on a per-context basis.

I've created an issue for this here.

In the meantime, I was able to get this to work by tweaking a few functions in the .Context.tt file, so that it adds a GUID to each parameter name at runtime:

private void WriteFunctionImport(TypeMapper typeMapper, CodeStringGenerator codeStringGenerator, EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) {
    if (typeMapper.IsComposable(edmFunction))
    {
#>

    [EdmFunction("<#=edmFunction.NamespaceName#>", "<#=edmFunction.Name#>")]
    <#=codeStringGenerator.ComposableFunctionMethod(edmFunction, modelNamespace)#>
    {       var guid = Guid.NewGuid().ToString("N"); <#+
        codeStringGenerator.WriteFunctionParameters(edmFunction, " + guid", WriteFunctionParameter);
#>
        <#=codeStringGenerator.ComposableCreateQuery(edmFunction, modelNamespace)#>
    } <#+
    }
    else
    {
#>

    <#=codeStringGenerator.FunctionMethod(edmFunction, modelNamespace, includeMergeOption)#>
    { <#+
        codeStringGenerator.WriteFunctionParameters(edmFunction, "", WriteFunctionParameter);
#>
        <#=codeStringGenerator.ExecuteFunction(edmFunction, modelNamespace, includeMergeOption)#>
    } <#+
        if (typeMapper.GenerateMergeOptionFunction(edmFunction, includeMergeOption))
        {
            WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: true);
        }
    } }

...

public void WriteFunctionParameters(EdmFunction edmFunction, string nameSuffix, Action<string, string, string, string> writeParameter)
{
    var parameters = FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef);
    foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable))
    {
        var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null";
        var notNullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\"" + nameSuffix + ", " + parameter.FunctionParameterName + ")";
        var nullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\"" + nameSuffix + ", typeof(" + parameter.RawClrTypeName + "))";
        writeParameter(parameter.LocalVariableName, isNotNull, notNullInit, nullInit);
    }
}

...

public string ComposableCreateQuery(EdmFunction edmFunction, string modelNamespace)
{
    var parameters = _typeMapper.GetParameters(edmFunction);

    return string.Format(
        CultureInfo.InvariantCulture,
        "return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<{0}>(\"[{1}].[{2}]({3})\"{4});",
        _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace),
        edmFunction.NamespaceName,
        edmFunction.Name,
        string.Join(", ", parameters.Select(p => "@" + p.EsqlParameterName + "\" + guid + \"").ToArray()),
        _code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray())));
}

Answer 2

Not a bug. Maybe a limitation or an omission. Apparently this use case has never been taken into account. EF could use auto-created parameter names, but, yeah, it just doesn't.

You'll have to resort to calling one of the functions with .AsEnumerable(). For some reason, this must be the first function in the join (as I have experienced). If you call the second function with .AsEnumerable() it is still translated to SQL and the name collision still occurs.