Difference between ExecuteMethodCall and CreateMethodCallQuery

If you drag stored procedures or functions on linq to SQL designer, the code generated to uses ExecuteMethodCall or CreateMethodQuery.

If you are calling a stored procedure or a function that returns a scalar value, than generated code uses ExecuteMethodCall. ExecuteMethodCall is a protected method that takes 3 parameter. First parameter is an instance of the datacontext, second parameter is the method info object. ExecuteMethodCall requires methodinfo object to read the attributes that will tell what stored procedure to call. Furthermore methodinfo object will tell the parameter names and datatypes stored procedures requires so stored procedure can be called with correct parameters.The third parameter is array of input parameter values required for the stored procedure.

ExecuteMethodCall returns an object implementing IExecuteResult interface.This interface has a property called returnvalue. If your stored procedure or function returns a scalar value, you need to cast the return value to appropriate value type. If your stored procedure returns single table, IExecuteResult.ReturnValue needs to be cast to ISingleResult of T where T is the entity return type for the stored procedure.ISingleResult is a wrapper to IEnumerable of T that allows you to access single resultset. If the stored procedure returns multiple tables, the results needs to be casted to IMultipleResult of T. IMultipleResult is allows you to access multiple tables by calling GetResult and passing in the Type of entity you want to retrieve. Below is an example that makes use of ExecuteMethodCall to get the return value for a function.

The above method is marked with function attribute and we are setting IsComposable to true to indicate that we are calling a function. To execute the function and gets its return value we are calling ExecuteMethodCall which returns an object of type IExecuteResult and than casting its return value gives us the value returned by the function.

If you are using table valued functions, the generated code will use CreateMethodCallQuery to call the function. The parameters for CreateMethodCallQuery is exactly the same as ExecuteMethodCall except the return value is IQueryable of T where is the entity type returned by the function. Since the table valued function returns a IQueryable you can further compose the queries by apply filter and sorts and the entire query get send to the database. Below is an example where the generated code uses CreateMethodCallQuery.

In future blog posting, I will go deeper into IExecuteResult and talk about GetParameterValue and ReturnValue value property and when the Returnvalue needs to be casted to value type, ISingleResult or IMultipleResult.