CODEWITHSUNDEEP
c#sqllinqinvalidoperationexception
frosty
frosty

Reputation: 2862

LINQ: InvalidOperationException: The required column does not exist in the results

I'm using LINQ to call a SQL Server sproc. Occasionally I get InvalidOperationException: The required column 'ID' does not exist in the results. The column does exist in the sproc results. I'm using 'using' like suggested in a old post. Any ideas why this can happen at random?

code snippet:

using (MyDataContext dataContext = new MyDataContext(context.ConnectionString))
{
    alertQueueList = dataContext.ExecuteQuery<AlertQueue>("exec dbo.my_AlertQueue_Dequeue @RowsToReturn = {0}", "10");

    // To handle exception when no rows are returned by sproc
    ret = alertQueueList.ToList();
}

exception is thrown on ExecuteQuery().

Exception:
System.InvalidOperationException: The required column 'ID' does not exist in the results.
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1.GetColumnOrdinals(NamedColumn[] namedColumns)
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderBase`1..ctor(ObjectReaderSession`1 session, NamedColumn[] namedColumns, Object[] globals, Object[] arguments, Int32 nLocals)
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderSession`1.CreateReader[TObject](Func`2 fnMaterialize, NamedColumn[] namedColumns, Object[] globals, Int32 nLocals, Boolean disposeDataReader)
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderFactory`2.Create(DbDataReader dataReader, Boolean disposeDataReader, IReaderProvider provider, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries)
   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 System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters)
   at MyNamespace.Logic.AlertQueueLogic.GetForProcessing(IMyNamespaceContext context) in c:\\foo\\AlertQueueLogic.cs:line 26
   at Connect.Processor.AlertProcessor.DequeueAlerts() in c:\\foo\\AlertProcessor.cs:line 123

sproc content: I'm pasting the code inside sproc below:

BEGIN TRANSACTION;

UPDATE TOP (@RowsToReturn) dbo.AlertQueue WITH (ROWLOCK, UPDLOCK, READPAST)
SET AlertStatus = @AlertStatus_Processing,
    LastLockedDttm = @CurrentDttm
    OUTPUT INSERTED.ID INTO @OutputRows
WHERE <my predicate>
COMMIT TRANSACTION;

SELECT  [ID], [ScheduleID], [TriggerName], [AlertStatus], [LastLockedDttm], [RetryCount], [LastRetryDttm], [LastResponse], [DateCreated], [Version]
FROM dbo.AlertQueue
WHERE ID IN (SELECT ID FROM @OutputRows)

Upvotes: 1

Views: 1504

Answers (3)

frosty
frosty

Reputation: 2862

The sproc was called from C# code under TransactionScope() which added serializable isolation level for transaction that had conflicted with READPAST hint in sproc. Removing READPAST or making TransactionScope() as READCOMITTED isolation level fixed the problem.

Upvotes: 1

Bartosz W&#243;jtowicz
Bartosz W&#243;jtowicz

Reputation: 1401

Perhaps that depends on the parameter @RowsToReturn which you always initialize to 10. Maybe in some cases there's less than 10 rows in some table queried by the sproc.

Upvotes: 0

Zuzlx
Zuzlx

Reputation: 1266

Make sure you have a column named "ID" AND it should be the primary key. Try that.

Upvotes: 0

Related Questions