VistaDB Support Center

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)


1 Comment

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