I need to get a NEXTVAL from a SEQUENCE in an Oracle database. The modelbuilder does have a

builder.HasSequence("TABLE_SEQ");

But I have no clue on how to use that. The only way I can think of is scalar executing a raw SQL to retrieve the next value. Is that the way to go or are there better ways to do this?

I've found several posts that say I should use context.Database.SqlQuery() but in my solution that is missing. Do I need to add a library to get this functionality for EF 6.0?

Examples I found:

Example 1:

public int GetNewCertificateTradeRequestIdentity()
{
    using var command = _context.Database.GetDbConnection().CreateCommand();
    command.CommandText = "SELECT ts.seq_certificate_trade_request.NEXTVAL FROM DUAL";
    _context.Database.OpenConnection();
    using var reader = command.ExecuteReader();
    reader.Read();
    return reader.GetInt32(0);
}

Example 2:

users = await context.Database.SqlQuery<User>("Select * from User", new object[] { }).ToListAsync();

Both the _context.Database.GetDbConnection() context.Database.SqlQuery<x> are missing. Where can I find them?

CodePudding user response：

Ok, in EF6 you have the context.Database.GetDbConnection().CreateCommand(). With that command you can execute a query on the database and receive the result. I've also found a solution for getting the tablename from the EF6 Metadata and added an extension method to handle that. Now I can do the the following:

private Tijdverantwoording Create(decimal? mdwid, decimal? deelprjid, Datum? date)
{
    if (mdwid == null || deelprjid == null || date == null) throw new ArgumentNullException();

    Weekstaatstatus weekstaatStatus = _WeekstaatStatusService.GetOrCreate(mdwid.Value, date.Jaarweekcode, WeekStaatStatussen.InBewerking, DateTime.Now);

    var tijdverantwoording = new Tijdverantwoording
    {
        Tijdverantwoordingid = GetId<Tijdverantwoording>(), // <= Generate id
        Mdwid = mdwid.Value,
        Deelprjid = deelprjid.Value,
        Datum = date.DagDatum,
        Syncstatus = (decimal)SyncStatuses.InBewerking,
        Syncdate = DateTime.Now.Date,
        Weekstaatstatusid = weekstaatStatus.Weekstaatstatusid
    };

    _modelContext.Tijdverantwoordingen.Add(tijdverantwoording);

    return tijdverantwoording;
}

The base class used for a service.

using Microsoft.EntityFrameworkCore;
using MyProjects.Core.Extensions;
using MyProjects.Core.Model;

namespace MyProjects.Core.Services
{
    public class ServiceBase
    {
         private ModelContext? _modelContext;

        public ServiceBase(ModelContext modelContext)
        {
            _modelContext = modelContext;
        }

        public decimal GetId<T>()
            where T : class
        {
            var command = _modelContext.Database.GetDbConnection().CreateCommand();

            var tableName = _modelContext.TableName(typeof(T));

            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = $"SELECT {tableName}_SEQ.NEXTVAL FROM DUAL";

            _modelContext.Database.OpenConnection();

            try
            {
                var result = (decimal?)command.ExecuteScalar();

                return result.Value;
            }
            finally
            {
                _modelContext.Database.CloseConnection();
            }
        }
    }
}

And the extension method

using Microsoft.EntityFrameworkCore;

namespace MyProjects.Core.Extensions
{
    public static class DatabaseExtensions
    {
        public static string? TableName(this DbContext context, Type type)
        {
            var entityType = context.Model.FindEntityType(type);
            
            return entityType?.GetTableName() ?? throw new NullReferenceException($"Can't find name for type {type.Name}");
        }
    }
}