How to get next sequence number in DB2 using Entity Framework?

Question

I want to retrieve the next sequence number via an adhoc query in Entity Framework. I'm using:

LogEntities db = new LogEntities();

ObjectQuery<Int64> seq = db.CreateQuery<Int64>("SELECT AUDITLOG.EVENTID_SEQ.NEXTVAL from sysibm.sysdummy1");

This returns the following error:

ErrorDescription = "'sysibm.sysdummy1' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly."

I guess this is because sysdummy1 is not a mapped table in my model.

Anyone know a way I can perform this query using the LINQ 2 Entity context?

Answer 1

An ObjectQuery needs to use Entity SQL, not "regular" SQL. If you want to write "regular" SQL, you need to use a store connection, not an ObjectQuery. That said, I kind of wonder why you're manually retrieving a sequence number. If the entity property is set to auto increment on the server, this will the retrieved automatically when you SaveChanges. If you need to get a store connection, there is a method on the EntityConnection type, CreateDbCommand, which does this. Again, I recommend that you don't do it. Using this feature makes your code provider-specific. Most Entity Framework code should be provider-agnostic.

Answer 2

Thanks for your answer Craig. The reason I am unable to use an auto incrementing identity column is because this particular logical table is physically partitioned into 31 separate (daily) tables and the ID needs to be unique across all tables.

I ended up creating a stored procedure to retrieve the next number from the sequence, and then adding that to my EF Model store.

private static long GetNextEventId(DbConnection dbConnection)
{
    using (DbCommand cmd = dbConnection.CreateCommand())
    {
        cmd.CommandText = "LogEntities.GetNextEventId";
        cmd.CommandType = CommandType.StoredProcedure;

        // Execute the command
        return Convert.ToInt64(cmd.ExecuteScalar());
    }
}

Here's another way of call a stored proc in EF that returns a scalar.

Using Stored Procedures That Return Non Entity Type

Answer 3

Another option would be to create a table with an identity column and use the value from identity val function after the insert.