CODEWITHSUNDEEP
c#entity-frameworkoracle11gentity-framework-6entity-framework-core
Felix Castor
Felix Castor

Reputation: 1675

EF6 -> EFCore ExecuteSqlCommand equivelant

I'm working to move our model over to EF Core and I'm having trouble finding a way to call the following code:

InitializeTime = context.ExecuteSqlCommand<DateTime>("SELECT CURRENT_TIMESTAMP FROM DUAL").FirstOrDefault();

We use the database time to synchronize everything but from what I can find calling raw sql outside a DbSet isn't available and the return type is restricted to the DbSet type argument.

I was looking around and found that there are DbQuery objects (Article) but that seems like heavy lifting just to get the current system date time.

Has anyone else come across this and found a easier solution?

Note: this is a desktop application so the code would be running client side and the system date time isn't reliable.

Upvotes: 3

Views: 3919

Answers (2)

Felix Castor
Felix Castor

Reputation: 1675

In case anyone runs into the same issue. I ended up with the following:

Create a new class containing a DateTime property

public class SystemDateTime
{
    /// <summary>
    /// System datetime from the server.
    /// </summary>
    public DateTime DateTime { get; set; }
}

Add a DbSet of type SystemDateTime to the Model

    /// <summary>
    /// Set for getting the current date time from the database.
    /// </summary>
    public DbSet<SystemDateTime> SystemDateTimes { get; set; }

Note: In EFCore 3.1 it needs to be of type DbSet but in EFCore 2.? there is a different type devoted to this kind of set called DbQuery. See the article Here.

Add the mapping to the model builder

modelBuilder.Entity<SystemDateTime>().HasNoKey();
modelBuilder.Entity<SystemDateTime>().Property<DateTime>(x => x.DateTime).HasColumnName(@"DateTime").HasColumnType(@"TIMESTAMP").IsRequired().ValueGeneratedNever();

Use DbSet's FromSqlRaw to query the database

List<SystemDateTime> systemDateTime = context.SystemDateTimes.FromSqlRaw("SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') \"DateTime\" FROM DUAL").ToList();

This will return a list of one element so use First() or Single to get the value needed.

Upvotes: 2

rgvlee
rgvlee

Reputation: 3193

The EFCore namesakes context.Database.ExecuteSqlRaw(sql)/context.Database.ExecuteSqlRInterpolated(sql) (or context.Database.ExecuteSqlCommand(sql) prior to EFCore 3, you haven't specified what version you're working with) can only return an int, the number of rows affected by executing the sql statement.

context.Query<TQuery>() (up to EFCore 3, deprecated from EFCore 3 onwards) or a keyless entity (EFCore 3 onwards) backed by an ordinary POCO are your options as far as I am aware if you want to use EFCore to execute the query.

Keyless Entity Types doco:

Usage scenarios

Some of the main usage scenarios for keyless entity types are:

Serving as the return type for raw SQL queries.

In terms of the overhead, it's not really when you think in terms of the unit of work/repository pattern that EFCore implements; it essentially forces you to implement a repository rather than have ad-hoc queries around the place.

Upvotes: 6

Related Questions