Efcore: 'MIN' is not a recognized built-in function name - 2.1.0-preview2-final

Created on 11 Apr 2018 · 3Comments · Source: dotnet/efcore

Since i update EF 2.0.2 Final to 2.1.0-preview2-final I get this error message on my LINQ query using group by with LINQ Min() method.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: 'MIN' is not a recognized built-in function name.
Stack trace: 
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__11.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at System.Linq.AsyncEnumerable.<Aggregate_>d__6`3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at ReporterConsole.DAOs.BatchAuditRepo.<GetTaskList>d__4.MoveNext() in C:\Users\Roni.axelrad\Source\Repos\ReporterConsole\ReporterConsole\DAOs\BatchAuditRepo.cs:line 117
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at ReporterConsole.Utils.QueryManager.<GetQueriesResultList>d__0.MoveNext() in C:\Users\Roni.axelrad\Source\Repos\ReporterConsole\ReporterConsole\Utils\QueryManager.cs:line 43

Steps to reproduce

I use a LINQ Query with join and group by aggregation of the min datetime.
Including my query. the issue is coming out from that line 'StartTime = taskBatchGroup.Min(grp => grp.EntryTime)' although in version 2.0.2 it is working perfectly fine.

```c#
var gbaJoinTasksAndBatches = from gbajoin in _db.GBatchAudit
join task1 in _db.TTask on gbajoin.TaskId equals task1.TaskId
join batch1 in _db.TBatch on gbajoin.BatchId equals batch1.BatchId
where gbajoin.EntryTime >= ReporterArgs.FromDate &&
gbajoin.EntryTime <= ReporterArgs.ToDate &&
gbajoin.EntryType == 1
orderby gbajoin.EntryTime
select new
{
gbajoin.EntryTime,
gbajoin.BatchRunNum,
task1.TaskId,
task1.TaskName,
batch1.BatchId,
batch1.BatchName
};

    var gbaGroupedByErrorMessage = from gbaJoin in gbaJoinTasksAndBatches
                                   group gbaJoin by new
                                   {
                                       gbaJoin.BatchRunNum,
                                       Task = gbaJoin.TaskName,
                                       TaskId = gbaJoin.TaskId,
                                       Batch = gbaJoin.BatchName,
                                       BatchId = gbaJoin.BatchId,
                                   } into taskBatchGroup

                                   select new TaskListDto
                                   {
                                       BatchName = taskBatchGroup.Key.Batch,
                                       BatchId = taskBatchGroup.Key.BatchId,
                                       TaskName = taskBatchGroup.Key.Task,
                                       TaskId = taskBatchGroup.Key.TaskId,
                                       BatchRunNumber = taskBatchGroup.Key.BatchRunNum,
                                       StartTime = taskBatchGroup.Min(grp => grp.EntryTime)
                                   };

    return await gbaGroupedByErrorMessage.OrderBy(t => t.StartTime).ToListAsync();

```

Further technical details

EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 64-bit
IDE: (e.g. Visual Studio 2017 15.6.6)

closed-fixed regression type-bug

Source

roniaxe

Most helpful comment

~@roniaxe - Which version of SQL Server are you using?~

Found the issue. No need for repro. I can figure out how to reproduce.

smitpatel on 11 Apr 2018

🎉1 👍1

All 3 comments

I tried following async query
C# [ConditionalFact] public virtual async Task GroupJoin_complex_GroupBy_Aggregate_OrderBy() { await AssertQuery<Order, Customer>( (os, cs) => (from c in cs.Where(c => c.CustomerID != "DRACD" && c.CustomerID != "FOLKO").OrderBy(c => c.City).Skip(10).Take(50) join o in os.Where(o => o.OrderID < 10400).OrderBy(o => o.OrderDate).Take(100) on c.CustomerID equals o.CustomerID into grouping from o in grouping where o.OrderID > 10300 select o) .GroupBy(o => o.CustomerID) .Select(g => new { g.Key, Min = g.Min(o => o.OrderID) }) .OrderBy(t => t.Min)); }
Which involves joins before grouping followed by aggregate and order by aggregate selection. It still worked correctly.

@roniaxe - We will need a self-contained repro which produces above error to investigate. While you create a repro, it would also be useful if you can post the generate SQL which is throwing exception. That would give us more insight why such exception.

smitpatel on 11 Apr 2018

I will try to create a repro, although the posted query pretty much include it all, while doing it, this is the generated SQL I can track:

`Microsoft.EntityFrameworkCore.Database.Command:Debug: Executing DbCommand [Parameters=[@__ReporterArgs_FromDate_0='?' (DbType = DateTime), @__ReporterArgs_ToDate_1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='150000']
SELECT [batch1].[batch_name] AS [BatchName], [batch1].[batch_id] AS [BatchId], [task1].[task_name] AS [TaskName], [task1].[task_id] AS [TaskId], [gbajoin].[batch_run_num] AS [BatchRunNumber], MIN([gbajoin].[entry_time] AS [EntryTime]) AS [StartTime]
FROM [g_batch_audit] AS [gbajoin]
INNER JOIN [t_task] AS [task1] ON [gbajoin].[task_id] = [task1].[task_id]
INNER JOIN [t_batch] AS [batch1] ON [gbajoin].[batch_id] = [batch1].[batch_id]
WHERE (([gbajoin].[entry_time] >= @__ReporterArgs_FromDate_0) AND ([gbajoin].[entry_time] <= @__ReporterArgs_ToDate_1)) AND ([gbajoin].[entry_type] = CAST(1 AS smallint))
GROUP BY [gbajoin].[batch_run_num], [task1].[task_name], [task1].[task_id], [batch1].[batch_name], [batch1].[batch_id]
ORDER BY [StartTime]
'dotnet.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App2.0.6\System.Diagnostics.StackTrace.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'dotnet.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App2.0.6\System.Reflection.Metadata.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Microsoft.EntityFrameworkCore.Database.Command:Error: Failed executing DbCommand (50ms) [Parameters=[@__ReporterArgs_FromDate_0='?' (DbType = DateTime), @__ReporterArgs_ToDate_1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='150000']
SELECT [batch1].[batch_name] AS [BatchName], [batch1].[batch_id] AS [BatchId], [task1].[task_name] AS [TaskName], [task1].[task_id] AS [TaskId], [gbajoin].[batch_run_num] AS [BatchRunNumber], MIN([gbajoin].[entry_time] AS [EntryTime]) AS [StartTime]
FROM [g_batch_audit] AS [gbajoin]
INNER JOIN [t_task] AS [task1] ON [gbajoin].[task_id] = [task1].[task_id]
INNER JOIN [t_batch] AS [batch1] ON [gbajoin].[batch_id] = [batch1].[batch_id]
WHERE (([gbajoin].[entry_time] >= @__ReporterArgs_FromDate_0) AND ([gbajoin].[entry_time] <= @__ReporterArgs_ToDate_1)) AND ([gbajoin].[entry_type] = CAST(1 AS smallint))
GROUP BY [gbajoin].[batch_run_num], [task1].[task_name], [task1].[task_id], [batch1].[batch_name], [batch1].[batch_id]
ORDER BY [StartTime]

System.Data.SqlClient.SqlException (0x80131904): 'MIN' is not a recognized built-in function name.
at System.Data.SqlClient.SqlCommand.<>c.b__122_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.<BufferlessMoveNext>d__12.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__72.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.<MoveNext>d__11.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.d__5.MoveNext()
ClientConnectionId:c5246146-e210-469a-9323-f6742e3dba8a
Error Number:195,State:10,Class:15
Microsoft.EntityFrameworkCore.Database.Connection:Debug: Opening connection to database 'alis_db_prod' on server 'LFNSQLC03PR'.
Microsoft.EntityFrameworkCore.Database.Connection:Debug: Opened connection to database 'alis_db_prod' on server 'LFNSQLC03PR'.
Microsoft.EntityFrameworkCore.Database.Command:Debug: Executing DbCommand [Parameters=[@__ReporterArgs_FromDate_0='?' (DbType = DateTime), @__ReporterArgs_ToDate_1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='150000']
SELECT [gba].[batch_run_num] AS [BatchRunNum], [tBatch].[batch_name] AS [BatchName], [tTask].[task_name] AS [TaskName], CASE
WHEN SUM([gba].[chunk_id]) > 1
THEN N'Yes' ELSE N'No'
END AS [Chunkked], COUNT() AS [Proccessed]
FROM [g_batch_audit] AS [gba]
INNER JOIN [t_task] AS [tTask] ON [gba].[task_id] = [tTask].[task_id]
INNER JOIN [t_batch] AS [tBatch] ON [gba].[batch_id] = [tBatch].[batch_id]
WHERE ((([gba].[entry_time] > @__ReporterArgs_FromDate_0) AND ([gba].[entry_time] < @__ReporterArgs_ToDate_1)) AND ([gba].[entry_type] = CAST(3 AS smallint))) AND ((CHARINDEX(N'completed , reached', [gba].[description]) > 0) OR ([gba].[batch_id] = 168))
GROUP BY [tTask].[task_name], [tBatch].[batch_name], [gba].[batch_run_num]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ReporterArgs_FromDate_0='?' (DbType = DateTime), @__ReporterArgs_ToDate_1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='150000']
SELECT [gba].[batch_run_num] AS [BatchRunNum], [tBatch].[batch_name] AS [BatchName], [tTask].[task_name] AS [TaskName], CASE
WHEN SUM([gba].[chunk_id]) > 1
THEN N'Yes' ELSE N'No'
END AS [Chunkked], COUNT() AS [Proccessed]
FROM [g_batch_audit] AS [gba]
INNER JOIN [t_task] AS [tTask] ON [gba].[task_id] = [tTask].[task_id]
INNER JOIN [t_batch] AS [tBatch] ON [gba].[batch_id] = [tBatch].[batch_id]
WHERE ((([gba].[entry_time] > @__ReporterArgs_FromDate_0) AND ([gba].[entry_time] < @__ReporterArgs_ToDate_1)) AND ([gba].[entry_type] = CAST(3 AS smallint))) AND ((CHARINDEX(N'completed , reached', [gba].[description]) > 0) OR ([gba].[batch_id] = 168))
GROUP BY [tTask].[task_name], [tBatch].[batch_name], [gba].[batch_run_num]
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Private.CoreLib.dll
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Private.CoreLib.dll
Exception thrown: 'System.Data.SqlClient.SqlException' in ReporterConsole.dll
Microsoft.EntityFrameworkCore.Database.Command:Debug: A data reader was disposed.
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Private.CoreLib.dll
Microsoft.EntityFrameworkCore.Database.Command:Debug: Executing DbCommand [Parameters=[@__ReporterArgs_FromDate_0='?' (DbType = DateTime), @__ReporterArgs_ToDate_1='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='150000']
SELECT [gba].[description] AS [Message], [gba].[primary_key_type] AS [EntityType], [gba].[primary_key] AS [EntityId], [tb].[batch_name] AS [Batch], [tb].[batch_id] AS [BatchId], [tt].[task_name] AS [Task], [tt].[task_id] AS [TaskId], [gba].[batch_run_num] AS [BatchRunNumber]
FROM [g_batch_audit] AS [gba]
INNER JOIN [t_task] AS [tt] ON [gba].[task_id] = [tt].[task_id]
INNER JOIN [t_batch] AS [tb] ON [gba].[batch_id] = [tb].[batch_id]
WHERE (([gba].[entry_time] > @__ReporterArgs_FromDate_0) AND ([gba].[entry_time] < @__ReporterArgs_ToDate_1)) AND (([gba].[entry_type] = CAST(6 AS smallint)) OR ([gba].[entry_type] = CAST(5 AS smallint)))
ORDER BY (SELECT 1)
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Private.CoreLib.dll
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Private.CoreLib.dll
'MIN' is not a recognized built-in function name.`

roniaxe on 11 Apr 2018

~@roniaxe - Which version of SQL Server are you using?~

Found the issue. No need for repro. I can figure out how to reproduce.

smitpatel on 11 Apr 2018

🎉1 👍1

Was this page helpful?

0 / 5 - 0 ratings

Related issues

Update database issue

miguelhrocha · 3Comments

grouping and flatening 1:M:M:1 related data in Entity Framework Core/LINQ

MontyGvMC · 3Comments

How to Implement Conditionally Required Field Validation

spottedmahn · 3Comments

Use INSERT w/OUTPUT Instead of INSERT+SELECT

bgribaudo · 3Comments

In Entity Framework Core 1.0.0-RC2-Final, there seems to be no way to get the underlying DBTransaction after invoking Db.Database.BeingTransaction()

pachecosoftware · 3Comments