Can't resolve a Table Valued Function in EF6.1 Entity SQL

Question

I've been spinning my wheels on this for the last couple days and can't pin down what I'm doing wrong. I'm trying to setup a TVF that I can call in esql. I started using this as my guide, updating the details to 6.1.1 as needed. All my efforts receive a "cannot be resolved into a valid type or function." I am able to get the results in a Database.SqlQuery result but not in ESQL or Linq.

Can someone look this over and give me a clue? I would appreciate it.

Here is what I have:

[T-Sql]

CREATE FUNCTION [Reconciliation].[GetAccountUnits]
(           @PerspectiveId     INT
,           @EffectiveDate     DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT  [AccountId]     =   V.[AccountId]   
    ,       [PerspectiveId] =   V.[PerspectiveId]
    ,       [Units]         =   V.[Units]
...
)

[StorageModels]

<Function Name="GetAccountUnits" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="Reconciliation">
  <Parameter Name="PerspectiveId" Type="int" Mode="In"  />
  <Parameter Name="EffectiveDate" Type="datetime" Mode="In" />
  <ReturnType>
    <CollectionType>
      <RowType>
        <Property Name="AccountId" Type="int" Nullable="false" />
        <Property Name="PerspectiveId" Type="int" Nullable="false" />
        <Property Name="Units" Type="decimal" Precision="28" Scale="15" Nullable="false" />
      </RowType>
    </CollectionType>
  </ReturnType>
</Function>

[ConceptualModels]

<EntityContainer>
....
  <FunctionImport Name="GetAccountUnits" IsComposable="true" ReturnType="Collection(MBSA.CARS.Domain.Reconciliation.GetAccountUnits)">
    <Parameter Name="PerspectiveId" Mode="In" Type="Int32" />
    <Parameter Name="EffectiveDate" Mode="In" Type="DateTime" />
  </FunctionImport>
</EntityContainer>
<ComplexType Name="GetAccountUnits">
  <Property Type="Int32" Name="AccountId" Nullable="false" />
  <Property Type="Int32" Name="PerspectiveId" Nullable="false" />
  <Property Type="Decimal" Name="Units" Nullable="false" Precision="28" Scale="15" />
</ComplexType>

[Mappings]

<FunctionImportMapping FunctionImportName="GetAccountUnits" FunctionName="MBSA.CARS.Domain.Reconciliation.Store.GetAccountUnits" >
  <ResultMapping>
    <ComplexTypeMapping TypeName="MBSA.CARS.Domain.Reconciliation.GetAccountUnits">
      <ScalarProperty Name="AccountId" ColumnName="AccountId" />
      <ScalarProperty Name="PerspectiveId" ColumnName="PerspectiveId" />
      <ScalarProperty Name="Units" ColumnName="Units" />
    </ComplexTypeMapping>
  </ResultMapping>
</FunctionImportMapping>

[Function Stub]

public partial class ReconciliationContext : DomainContext
{
...
    [DbFunction("MBSA.CARS.Domain.Reconciliation.Store", "GetAccountUnits")]
    public virtual IQueryable<GetAccountUnits> GetAccountUnits(int perspectiveId, System.DateTime effectiveDate)
    {
        var perspectiveIdParameter = new ObjectParameter("PerspectiveId", perspectiveId);
        var effectiveDateParameter = new ObjectParameter("EffectiveDate", effectiveDate);

        return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<GetAccountUnits>("[ReconciliationContext].[GetAccountUnits](@PerspectiveId, @EffectiveDate)", perspectiveIdParameter, effectiveDateParameter);
    }
}

I've tried all of these:

[ESQL]

select value it from MBSA.CARS.Domain.Reconciliation.Store.GetDecimalProperty(1, DATETIME'2006-05-31 00:00') As it

select value it from Reconciliation.Store.GetDecimalProperty(1, DATETIME'2006-05-31 00:00') As it

select value it from Reconciliation.GetDecimalProperty(1, DATETIME'2006-05-31 00:00') As it

select value it from ReconciliationContext.GetDecimalProperty(1, DATETIME'2006-05-31 00:00') As it

select value it from GetDecimalProperty(1, DATETIME'2006-05-31 00:00') As it

GetDecimalProperty(1, DATETIME'2006-05-31 00:00')

score 0 · Answer 1 · answered Dec 17 '14 at 22:35

After digging into the assembly I discovered the ssdl, csdl, and msl embedded resources were out of date. Further investigation revealed that the post build command to generated views had been lost in in source control somewhere along the line. I added them back in rebuilt the project a couple times and now everything is working fine.

Can't resolve a Table Valued Function in EF6.1 Entity SQL

1 Answers1