Gibraltar Support

Start a new topic

Insert problem with Linq to SQL class

I am porting a large application that uses Linq to SQL Data Classes targeted to SQL Server 2008/14 to VistaDB. Everything else seems to be working fine except an insert in one table with an identity and Timestamp field. The table schema is:

 

CREATE TABLE [SessionInfo] (
 [Session_ID] Int IDENTITY(1091,1) NOT NULL,
 [Sessionstamp] Timestamp NOT NULL,
 [SessionStart] DateTime NOT NULL,
 [UptimeHours] Int NOT NULL
);
ALTER TABLE [SessionInfo] ADD CONSTRAINT [PK_SessionInfo] PRIMARY KEY ([Session_ID]);

 

The code has a corresponding Session Info class that I fill our(DateTime and UptimeHours). like the following:

 

SessionInfo record = new SessionInfo(); 
record.SessionStart = rectime;
record.UptimeHours = uptime;
// we'll be adding the record..
dc.SessionInfos.InsertOnSubmit(record);

 

and then finally do a SubmitChanges to


dc.SubmitChanges();


This code works fine on SQL server 2008 and 2014 but fails on VistaDB. I have logged to data context and code and this is what appears on the submit.


INSERT INTO [dbo].[SessionInfo]([SessionStart], [UptimeHours])
VALUES (@p0, @p1)

SELECT [t0].[Session_ID], [t0].[Sessionstamp]
FROM [dbo].[SessionInfo] AS [t0]
WHERE [t0].[Session_ID] = (SCOPE_IDENTITY())

-- @p0: Input Object (Size = -1; Prec = 0; Scale = 0) [01/04/2017 12:41:42]

-- @p1: Input Object (Size = -1; Prec = 0; Scale = 0) [0]

-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.1586.0

VistaDB.Diagnostic.VistaDBSQLException (0x000001FD):
Error 617 (Provider v. 5.2.2.1587): Name or alias cannot be reserved word:  SCOPE_IDENTITY
Line #: 6; Column #: 42
Error 509 (Provider v. 5.2.2.1587): Invalid or incomplete statement:  SELECT
Line #: 4; Column #: 1
 ---> VistaDB.Diagnostic.VistaDBSQLException (0x00000269):
Error 617 (Provider v. 5.2.2.1587): Name or alias cannot be reserved word:  SCOPE_IDENTITY
Line #: 6; Column #: 42

  at VistaDB.Engine.SQL.SQLParser.ValidateNameOrAlias(String name, Int32 rowNo, Int32 colNo)
  at VistaDB.Engine.SQL.SQLParser.GetNamePart(Int32& startIndex, String name, Int32 rowNo, Int32 colNo)
  at VistaDB.Engine.SQL.SQLParser.ParseFunctions(Boolean includeSystem)
  at VistaDB.Engine.SQL.SQLParser.ParseExpressions()
  at VistaDB.Engine.SQL.SQLParser.ParsePriority(Int32 priority)
  at VistaDB.Engine.SQL.SQLParser.ParseExpressions()
  at VistaDB.Engine.SQL.SQLParser.ParsePriority(Int32 priority)
  at VistaDB.Engine.SQL.SQLParser.NextSignature(Boolean needSkip, Boolean raiseException, Int32 priority)
  at VistaDB.Engine.SQL.Signatures.BinaryCompareOperator.DoParseRightOperand(SQLParser parser, Int32 priority)
  at VistaDB.Engine.SQL.Signatures.BinaryOperator..ctor(Signature leftOperand, SQLParser parser, Int32 priority)
  at VistaDB.Engine.SQL.Signatures.BinaryCompareOperator..ctor(Signature leftOperand, SQLParser parser)
  at VistaDB.Engine.SQL.Signatures.EqualOperator..ctor(Signature leftOperand, SQLParser parser)
  at VistaDB.Engine.SQL.Signatures.EqualOperatorDescr.CreateSignature(Signature leftSignature, SQLParser parser)
  at VistaDB.Engine.SQL.SQLParser.ParsePriority(Int32 priority)
  at VistaDB.Engine.SQL.SQLParser.NextSignature(Boolean needSkip, Boolean raiseException, Int32 priority)
  at VistaDB.Engine.SQL.BaseSelectStatement.ParseWhereClause(SQLParser parser)
  at VistaDB.Engine.SQL.SelectStatement.OnParse(LocalSQLConnection connection, SQLParser parser)
  at VistaDB.Engine.SQL.Statement.Parse(LocalSQLConnection connection, SQLParser parser)
  at VistaDB.Engine.SQL.Statement.Parse(LocalSQLConnection connection, SQLParser parser)
  at VistaDB.Engine.SQL.Statement..ctor(LocalSQLConnection connection, Statement parent, SQLParser parser, Int64 id)
  at VistaDB.Engine.SQL.BaseSelectStatement..ctor(LocalSQLConnection connection, Statement parent, SQLParser parser, Int64 id)
  at VistaDB.Engine.SQL.SelectStatement..ctor(LocalSQLConnection connection, Statement parent, SQLParser parser, Int64 id)
  at VistaDB.Engine.SQL.SelectStatement.CreateSelectStatement(LocalSQLConnection connection, Statement parent, SQLParser parser, Int64 id)
  at VistaDB.Engine.SQL.SelectStatementDescr.CreateStatement(LocalSQLConnection conn, Statement parent, SQLParser parser, Int64 id)
  at VistaDB.Engine.Internal.LocalSQLConnection.ParseStatement(Statement parent, Int64 id)
  at VistaDB.Engine.Internal.LocalSQLConnection.ParseStatementBatch(Int64 id)
  at VistaDB.Engine.Internal.LocalSQLConnection.CreateBatchStatement(String text, Int64 id)
  at VistaDB.Engine.Internal.LocalSQLConnection.VistaDB.Engine.Internal.ILocalSQLConnection.CreateQuery(String commandText)
  at VistaDB.Provider.VistaDBConnection.CreateQuery(String commandText)
  at VistaDB.Provider.VistaDBCommand.CreateQuery()
  at VistaDB.Provider.VistaDBCommand.PrepareQuery()
  at VistaDB.Provider.VistaDBCommand.ExecuteReader(CommandBehavior behavior)
  at VistaDB.Provider.VistaDBCommand.ExecuteDbDataReader(CommandBehavior behavior)
  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.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
  at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
  at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
  at Aii.Assurance.ProductDbs.RunSession.RecordSystemStart(Int32 policy) in C:\Project\csharp\Database\ProductDbs\RunSession.cs:line 77


From this it looks like the insert SQL generated by Linq to SQL for setting the Session_ID is causing the weird problem (i.e. using SCOPE_IDENTITY instead of @@IDENTITY but the problem is that I'm not generating the SQL code, Linq is.. so the question is how can I restructure this to work with VistaDB (hopefully within the context of Linq)



To update this in case anyone following or finding this thread was wondering, our recent 5.5 release added partial support for the SCOPE_IDENTITY() function to at least act as a substitute for @@IDENTITY to allow this usage to work, in most cases.


As noted in the release notes for version 5.5.3, it is not currently scope-isolated, so usage depending on the scope-isolated behavior of SCOPE_IDENTITY() will get incorrect results.  We plan to correct this in a future release so that it can work the same as documented for SQL Server.

VistaDB does not currently support SCOPE_IDENTITY(), only @@IDENTITY and a VistaDB-specific LASTIDENTITY(...) function.  So, unfortunately, the Linq to SQL text generated for SQL Server (which we're *mostly* compatible with) is broken by that.  Other than dropping the use of an IDENTITY column--which may not be very practical to do for your needs--I'm not sure there's any way to work around it, currently.


Adding a proper scope-isolated implementation of SCOPE_IDENTITY() will take some time to research and engineer, but in the meantime we can pretty easily add a fake SCOPE_IDENTITY() function which merely maps to the implementation used for @@IDENTITY as a temporary measure to patch the problem (with the caveat that it will not work correctly in cases where scope isolation is actually needed).

Login to post a comment