Styjun

Why are Oscar, India, and X-Ray (O, I, and X) not used as taxiway identifiers?

Strange LED behavior: Why is there a voltage over the LED with only one wire connected to it?

Is it ethical to tell my teaching assistant that I like him?

Can someone explain the English 'W' sound?

Is it OK to accept a job opportunity while planning on not taking it?

How can I deal with someone that wants to kill something that isn't supposed to be killed?

Can I make an Opportunity Attack during Time Stop?

Are rockets faster than airplanes?

"It is what it is" in French

Short story where a flexible reality hardens to an unchanging one

Would using carbon dioxide as fuel work to reduce the greenhouse effect?

What is a plausible power source to indefinitely sustain a space station?

Do I care if the housing market has gone up or down, if I'm moving from one house to another?

What is the best word describing the nature of expiring in a short amount of time, connoting "losing public attention"?

Was US film used in Luna 3?

Does quantity of data extensions impact performance?

Extrapolation v. Interpolation

What gave NASA the confidence for a translunar injection in Apollo 8?

Does starting a profile in Mon Projet mean I applied to enter or remain in Canada?

Why is the UH-60 tail rotor canted?

How to work a regular job as a former celebrity

Why did NASA use Imperial units?

How could Barty Crouch Jr. have run out of Polyjuice Potion at the end of the Goblet of Fire movie?

What is "ass door"?

Entity Framework Core - ExecuteSqlCommand: MAX function returning wrong value

Select columns from result set of stored procedureEntity Framework Core - Lazy LoadingEntity Framework Core Connection String - Environment VariableC# Linq to SQL vs Entity Framework .Net CoreEntity Framework Core 2.0 on .NET 4.6.1modelBuilder.Properties() in Entity Framework CoreEntity framework Core Raw SQLQueries with custom modelEntity Framework Core 2.1, ChangeTracker.TrackedEntity Framework Core Code First changing added entities values before save causes identity insert errorEntity Framework Core Default Value Not Loading After Add Range

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

0

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:

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

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

What's going on?

sql-server .net-core entity-framework-core asp.net-core-2.0

share|improve this question

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MAX(ID) +1 can easily return duplicates if eg the latest row is deleted. That's why it's seldom used to generate IDs. Use IDENTITY or a SEQUENCE instead. EF has no trouble working with IDENTITY values either. It doesn't have to know the IDs in advance to set up foreign keys. When a new root object and nested objects are added, EF will generate the appropriate statements to set all IDs according to the relations configured on the DbContext
  
  – Panagiotis Kanavos
  Mar 26 at 15:34
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi @PanagiotisKanavos; Thanks for the advice. I understand the identity strategy, but the point is that I'm dealing with a legacy database where it's creator didn't use IDENTITY, so I have to use the MAX+1 strategy... Sad but it's the reality. So, I need to execute an insert query into the legacy db through a secondary connection, that's why I need to run a query on the fly, without binding it to a model.
  
  – romeromedeiros
  Mar 26 at 21:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Dapper doesn't change the fact that MAX+1 creates duplicates. You'd still get them whether you used Dapper or straight ADO.NET SqlCommand. In fact, if you have more than 1 concurrent users, you risk both of them getting the same MAX value and trying to insert data using the same ID
  
  – Panagiotis Kanavos
  May 23 at 14:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In fact, EF Core offers the HiLo key strategy to safely generate IDs on the client.
  
  – Panagiotis Kanavos
  May 23 at 14:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much for the help, @PanagiotisKanavos.
  
  – romeromedeiros
  May 23 at 14:36
  

  
  
  
  

add a comment | 

0

0

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:

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

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

What's going on?

sql-server .net-core entity-framework-core asp.net-core-2.0

share|improve this question

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  MAX(ID) +1 can easily return duplicates if eg the latest row is deleted. That's why it's seldom used to generate IDs. Use IDENTITY or a SEQUENCE instead. EF has no trouble working with IDENTITY values either. It doesn't have to know the IDs in advance to set up foreign keys. When a new root object and nested objects are added, EF will generate the appropriate statements to set all IDs according to the relations configured on the DbContext
  
  – Panagiotis Kanavos
  Mar 26 at 15:34
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi @PanagiotisKanavos; Thanks for the advice. I understand the identity strategy, but the point is that I'm dealing with a legacy database where it's creator didn't use IDENTITY, so I have to use the MAX+1 strategy... Sad but it's the reality. So, I need to execute an insert query into the legacy db through a secondary connection, that's why I need to run a query on the fly, without binding it to a model.
  
  – romeromedeiros
  Mar 26 at 21:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Dapper doesn't change the fact that MAX+1 creates duplicates. You'd still get them whether you used Dapper or straight ADO.NET SqlCommand. In fact, if you have more than 1 concurrent users, you risk both of them getting the same MAX value and trying to insert data using the same ID
  
  – Panagiotis Kanavos
  May 23 at 14:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In fact, EF Core offers the HiLo key strategy to safely generate IDs on the client.
  
  – Panagiotis Kanavos
  May 23 at 14:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much for the help, @PanagiotisKanavos.
  
  – romeromedeiros
  May 23 at 14:36
  

  
  
  
  

add a comment | 

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:

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

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

What's going on?

sql-server .net-core entity-framework-core asp.net-core-2.0

share|improve this question

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

private int getColumNextValue(string table, string column)

 string query = $"SELECT MAX(column) + 1 FROM table;"; 
 return base.context.Database.ExecuteSqlCommand(query);

sql-server .net-core entity-framework-core asp.net-core-2.0

sql-server .net-core entity-framework-core asp.net-core-2.0

share|improve this question

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

share|improve this question

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

asked Mar 26 at 14:22

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

2 Answers
2

active

oldest

votes

0

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();

share|improve this answer

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are opening the connection associated with the context, but not closing it afterwards, which is not good.
  
  – Ivan Stoev
  Mar 27 at 9:23
  

  
  
  
  

add a comment | 

0

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.

share|improve this answer

edited May 24 at 11:24

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55359475%2fentity-framework-core-executesqlcommand-max-function-returning-wrong-value%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

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();

share|improve this answer

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are opening the connection associated with the context, but not closing it afterwards, which is not good.
  
  – Ivan Stoev
  Mar 27 at 9:23
  

  
  
  
  

add a comment | 

0

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();

share|improve this answer

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You are opening the connection associated with the context, but not closing it afterwards, which is not good.
  
  – Ivan Stoev
  Mar 27 at 9:23
  

  
  
  
  

add a comment | 

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();

share|improve this answer

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

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();
 
 

public async Task<IActionResult> Index()

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

share|improve this answer

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

answered Mar 27 at 6:06

Tao ZhouTao Zhou

9,80633 gold badges1616 silver badges3434 bronze badges

0

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.

share|improve this answer

edited May 24 at 11:24

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

add a comment | 

0

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.

share|improve this answer

edited May 24 at 11:24

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

add a comment | 

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.

share|improve this answer

edited May 24 at 11:24

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

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);
 

share|improve this answer

edited May 24 at 11:24

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges

answered May 23 at 14:31

romeromedeirosromeromedeiros

50888 silver badges1212 bronze badges