Exception when calling stored procedure via Linq-to-SQL

Question

I get the following stack trace when my code tries to execute a store procedure that is present in the .dbml file.

Exception finding transfer:System.Data.SqlClient.SqlException:
Invalid object name 't_wfe_user_role'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
at LinqDataClasses.DataClasses.WorkflowsDataContext.sp_wfe_transfer_task_force(Nullable1 p_d2_user_ref, Nullable1 p_d2_diary_ref, Nullable1 p_state_ref, Nullable1 p_transfer_ref) in C:\Documents and Settings\Administrator\My Documents\SPSM-Bidragshantering_Origo\SPSM\Main\CommonData\LinqDataClasses\DataClasses\Workflows.designer.cs:line 347
at SPSM.ContinueWorkflows.ContinueWFs(Int32[] cases, String type) in C:\Documents and Settings\Administrator\My Documents\SPSM-Bidragshantering_Origo\SPSM\Main\SIS\Applications\SIS-PreliminaryDecision\ContinueWorkflows.cs:line 67

My only suspect is that pluralization has some how tripped itself. There are no table named t_wfe_user_role in the DB. It is called t_wfe_user_roles, but then Linq renames it t_wfe_user_role in the GUI of the .dbml file. This table is a connection table with two foreign keys.

Any ideas!?

This is a code snippet from where I call the store procedure. The call is at the end (sp_wfe_transfer_task_force):

public class ContinueWorkflows
{
    public AppVariables _APPVARS = new Utility.AppVariables();
    public void ContinueWFs(int[] cases, string type)
    {
        foreach (int Case in cases)
        {
            if (Case != 0)
            {
                _APPVARS = (Utility.AppVariables)System.Web.HttpContext.Current.Session["CommonAppVariables"];
                LinqDataClasses.DataClasses.WorkflowsDataContext wfdc = new LinqDataClasses.DataClasses.WorkflowsDataContext(_APPVARS.SQLConnectionString);
                LinqDataClasses.DataClasses.t_wfe_workflow wf = wfdc.t_wfe_workflows.Where(p => p.c_d2_case_ref == Case && p.c_cancelled != true).FirstOrDefault();
                if (wf != null)
                {
                    LinqDataClasses.DataClasses.t_wfe_state state = wfdc.t_wfe_states.Where(p => p.c_workflow_ref == wf.c_rowid && p.c_cancelled != true).FirstOrDefault();
                    if (state != null)
                    {
                        LinqDataClasses.DataClasses.t_wfe_transfer transfer;
                        if (type == "Beslut")
                        {
                            transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Till beslut" && p.c_cancelled != true).FirstOrDefault();
                            if (transfer == null)
                            {
                                transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Övergång direkt till beslut" && p.c_cancelled != true).FirstOrDefault();
                            }
                            if (transfer == null)
                            {
                                transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Övergång till beslut" && p.c_cancelled != true).FirstOrDefault();
                            }
                        }else if (type == "BVS")
                        {
                            transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Stopp" && p.c_cancelled != true).FirstOrDefault();
                        }
                        else if (type == "TUFF")
                        {
                            transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Preliminärt besked" && p.c_cancelled != true).FirstOrDefault();
                        }
                        else // SIS Prel Besked
                        {
                            transfer = wfdc.t_wfe_transfers.Where(p => p.c_from_step_ref == state.c_step_ref && p.c_name == "Övergång till preliminärt besked" && p.c_cancelled != true).FirstOrDefault();
                        }
                        if (transfer != null)
                        {
                            //Do transfer
                            int transref = transfer.c_rowid;
                            wfdc.sp_wfe_transfer_task_force(1, _APPVARS.DiaryRef, state.c_rowid, transref);
                        }
                    }
                }
            }
        }
    }
}

Zia Zia · Accepted Answer · 2012-08-01T14:55:17

if there is no issue with re generating data model then you can turn off pluralization and the generate class . in visual studio On the Tools menu, click Options.

In the Options dialog box, expand Database Tools.

(Note:Select Show all settings if the Database Tools node is not visible.)

Click O/R Designer.

Set Pluralization of names to Enabled = False to set the O/R Designer so that it does not change class names.

Set Pluralization of names to Enabled = True to apply pluralization rules to the class names of objects added to the O/R Designer.

otherwise You can change the generated name by right-clicking the table in the dbml designer window and selecting properties. There is a field called 'name' with which you should be able to define a custom name.

Exception when calling stored procedure via Linq-to-SQL

1 Answers