score:1

Accepted answer

Functions declared in the database provider are normally available under the provider's namespace. So if this was a normal Entity SQL query you should be able to invoke STUFF() adding SqlServer before it, e.g. SqlServer.STUFF(strTime, 3, 0, ':').

However, the Entity SQL in the body of a model-defined functions can only refer to other canonical functions or other model-defined functions, i.e. model-defined functions cannot be made provider specific by referring to provider specific functions.

This is a limitation that was adopted by choice. When your application executes a LINQ query you can execute provider specific functions because your application already depends on a full model with conceptual and store schemas and a mapping specification. Model-defined functions on the other hand are part of the conceptual model, which is supposed to be self-contained: you should be able swap the provider, store schema or mapping specification for different ones without ever rendering the conceptual model invalid. Hence things that are defined in the conceptual model cannot depend on things that are defined elsewhere.

Workaround:

I did some experimentation and came up with this naïve way to simulate SQL Server's Stuff() using only canonical functions that should work across providers. You can use something like this from your own model-defined function by specifying the namespace of the conceptual model, e.g. Model1.Stuff(strTime, 3, 0, ':')

<Function Name="Stuff" ReturnType="String">
  <Parameter Name="character_expression" Type="String" />
  <Parameter Name="start" Type="Int32" />
  <Parameter Name="length" Type="Int32" />
  <Parameter Name="replaceWith_expression" Type="String" />
  <DefiningExpression>
    Left(character_expression,start-1) 
    + replaceWith_expression 
    + Substring(character_expression, start + length, Length(character_expression))
  </DefiningExpression>
</Function>

Related Articles