CODEWITHSUNDEEP
sql-server.net-coreentity-framework-coreasp.net-core-2.0
rom
rom

Reputation: 930

Entity Framework Core - ExecuteSqlCommand: MAX function returning wrong value

I'm trying to do a simple thing: to get the MAX of a column. Is quite simple, you know, just run SELECT MAX(column) FROM table;

The problem is when I try to do this in my .NET Core 2.1 project, using Entity Framework.

I have a function that should return the next value of a column.

private int getColumNextValue(string table, string column)
{
  string query = $"SELECT MAX({column}) + 1 FROM {table};";    
  return base.context.Database.ExecuteSqlCommand(query);
}

The query is being generated correclty:

enter image description here

However, it's returning -1 instead of the real value.

enter image description here

But when I run the exact same query in Sql Server Management Studio, the result is correct:

enter image description here

What's going on?

Upvotes: 1

Views: 1610

Answers (2)

rom
rom

Reputation: 930

I used QueryFirstOrDefault<int> to solve my problem. I wrote this helper method for reusable purpose:

private int GetColumNextValue(string table, string column)
{
  using (SqlConnection conn = new SqlConnection(this.configuration.GetConnectionString("MyConnection")))
  {
    string query = $"SELECT MAX({column}) + 1 FROM {table};";
    return conn.QueryFirstOrDefault<int>(query);
  }
}

I hope it can help other people.

Upvotes: 0

Edward
Edward

Reputation: 30056

For ExecuteSqlCommand, it only return the number of rows affected. It would not run the query and return the result.

For a workaround, you could try like: 

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    public DbSet<User> User { get; set; }


    public async Task<T> ExecuteScalarAsync<T>(string rawSql, params object[] parameters)
    {
        var conn = Database.GetDbConnection();
        using (var command = conn.CreateCommand())
        {
            command.CommandText = rawSql;
            if (parameters != null)
                foreach (var p in parameters)
                    command.Parameters.Add(p);
            await conn.OpenAsync();
            return (T)await command.ExecuteScalarAsync();
        }
    }
}

And use ExecuteScalarAsync like 

public async Task<IActionResult> Index()
{
    string query = $"SELECT MAX(SEQ) + 1 FROM [User];";
    var result = await _context.ExecuteScalarAsync<int>(query);
    return View();
}

Upvotes: 2

Related Questions