Saturday, September 25, 2010

Problem when using stored procedure in Linq to SQL

A while ago, I tried to add a stored procedure to the dbml file using the Linq to SQL designer. However it failed with the following error message:

linqtosqlerror

The Linq to SQL designer queries the stored procedure for metadata information (in order to determine what to write to the dbml file). The warnings indicate that SQL Server was unable to determine the shape of the result set returned by the stored procedure.

The only solution I found is to provide the mapping for such sprocs yourself. Just create a partial class for the Linq to SQL context and add the code to call the stored procedure:

   1:  [Function(Name="RVV.GetLeveringDataByLeveringId")]
   2:  public ISingleResult<LeveringData> GetLeveringDataByLeveringId([Parameter(DbType="NVarChar(20)")] string id)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this,         ((MethodInfo)(MethodInfo.GetCurrentMethod())), id);
   5:      return ((ISingleResult<LeveringData>)(result.ReturnValue));
   6:  }

No comments: