Bug #45277 Calling User Defined Function using eSql causes NullReferenceException
Submitted: 2 Jun 2009 21:57 Modified: 8 Jan 2010 14:11
Reporter: Muhammad Mosa Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: entity framework, Entity SQL, eSql, udf

[2 Jun 2009 21:57] Muhammad Mosa
Description:
Calling UDF using Entity SQL in Entity Framework causes NullReferenceException.

Participant Classes:
*BinaryFragment class this is where exception take place on WriteSql(StringBuilder) method
*FunctionProcessor class

The cause of this exception:
// do left arg
if (WrapLeft)
   sql.Append("(");
   Left.WriteSql(sql); //At this line, Left is null
if (WrapLeft)
   sql.Append(")");

Reason:
Generate method of FunctionProcessor class searchs only for built in functions and returns null if function not found

How to repeat:
due to my limitted time I couldn't wirte a small sample for this but if you wish to get my full project code no problem. it is an open source anyway and contain test cases and I can highlight which case exactly is failing.

For simple repeate do the following
*create a simple T-SQL user defined function mine is called "EFSearchStory"

*Make sure you made correct mapping in SSDL here is mine
<Function Name="EFSearchStory" Aggregate="false"
    BuiltIn="false" NiladicFunction="false" IsComposable="true" 
    ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="EFSearchStory"
    Schema="dbo" ReturnType="bit">
    <Parameter Name="storyId" Type="uniqueidentifier" Mode="In" />
    <Parameter Name="query" Type="nvarchar" MaxLength="4000" Mode="In" />
  </Function>
Note that StoreFunctionName is different from Name in this example.

* create an ESql statement and execute it mine is:
"select s.Id as Id from YourContainNamer.Story as s where YourStoreFullNamespace.EFSearchStory(s.Id, @query) = true

Execute your ESQL Statement and it should throw a NullReferenceException in the location I specified in the description

Suggested fix:
This is not a fix, this is a work around I made to make my application run.
The workaround I made is in FunctionProcessor.

public SqlFragment Generate(DbFunctionExpression e, SqlGenerator caller)
        {
            callingGenerator = caller;
            if (bitwiseFunctions.ContainsKey(e.Function.Name))
                return BitwiseFunction(e);

            if (dateFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(dateFunctions, e);
            
            if (stringFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(stringFunctions, e);
            
            if (mathFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(mathFunctions, e);
            
            return UdfFunction(e);
        }

private SqlFragment UdfFunction(DbFunctionExpression e)
        {
            var frags = new SqlFragment[e.Arguments.Count];
            
            for (var i = 0; i < e.Arguments.Count; i++)
                frags[i] = e.Arguments[i].Accept(callingGenerator);

            var functionFormat = new StringBuilder(100);
            
            functionFormat.Append(e.Function.Name);
            functionFormat.Append("(");
            for (var i = 0; i < frags.Length; i++)
            {
                functionFormat.Append("{" + i + "}");
                if (i != frags.Length - 1)
                {
                    functionFormat.Append(", ");
                }
            }
            functionFormat.Append(")");
            
            string sql = String.Format(functionFormat.ToString(), frags);
            return new LiteralFragment(sql);
        }
[7 Aug 2009 12:58] Tonci Grgin
Hi Muhammad and thanks for your report.

I would really like to see full self-sufficient test case attached to it.
[7 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 Jan 2010 22:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96040

780 Reggie Burnett	2010-01-05
      - fixed entity framework function processing so that it handles stored functions properly  (bug #45277)
[5 Jan 2010 22:11] Reggie Burnett
fixed in 6.0.6, 6.1.4, and 6.2.3
[5 Jan 2010 22:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96043
[5 Jan 2010 22:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96044
[5 Jan 2010 22:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96046
[8 Jan 2010 14:11] Tony Bedford
An entry was added to the 6.0.6, 6.1.4, and 6.2.3 changelogs:

Calling a User Defined Function using Entity SQL in the Entity Framework caused a NullReferenceException.