I need to generate a query where I group by 2 fields and since Simple.Data doesn't have a "GroupBy" function, I'm adding an aggregate to my select query to force a "GroupBy" on the remaining fields in this way:
Simple.Data.Ado.AdoAdapterException was unhandled by user code
HResult=-2146233088
Message=Incorrect syntax near the keyword 'AND'.
Source=Simple.Data.Ado
CommandText=select COUNT(*) from [dbo].[Album] WHERE (([dbo].[Album].[ArtistId] IS NOT NULL AND [dbo].[Album].[ArtistId] > @p1_c0) AND [dbo].[Album].[ArtistId] < @p2_c0); WITH __Data AS (SELECT [dbo].[Album].[AlbumId], ROW_NUMBER() OVER(ORDER BY [dbo].[Album].[Price]) AS [_#_]
from [dbo].[Album] WHERE (([dbo].[Album].[ArtistId] IS NOT NULL AND [dbo].[Album].[ArtistId] > @p1_c1) AND [dbo].[Album].[ArtistId] < @p2_c1) GROUP BY [dbo].[Album].[Price],[dbo].[Album].[AlbumArtUrl])
SELECT [dbo].[Album].[Price],[dbo].[Album].[AlbumArtUrl],Count([dbo].[Album].[Price]) FROM __Data JOIN [dbo].[Album] ON [dbo].[Album].[AlbumId] = __Data.[AlbumId] WHERE (([dbo].[Album].[ArtistId] IS NOT NULL AND [dbo].[Album].[ArtistId] > @p1_c1) AND [dbo].[Album].[ArtistId] < @p2_c1) GROUP BY [dbo].[Album].[Price],[dbo].[Album].[AlbumArtUrl] AND [_#_] BETWEEN 3 AND 10
StackTrace:
at Simple.Data.Ado.DbCommandExtensions.TryExecuteReader(IDbCommand command)
at Simple.Data.Ado.DataReaderMultipleEnumerator.ExecuteReader()
at Simple.Data.Ado.DataReaderMultipleEnumerator.MoveNext()
at Simple.Data.Ado.AdoAdapterQueryRunner.<RunQueries>d__b.MoveNext()
at Simple.Data.Ado.AdoAdapterQueryRunner.RunQueryWithCount(SimpleQuery query, IEnumerable`1& unhandledClauses)
at Simple.Data.Ado.AdoAdapterQueryRunner.RunQuery(SimpleQuery query, IEnumerable`1& unhandledClauses)
at Simple.Data.Ado.AdoAdapter.RunQuery(SimpleQuery query, IEnumerable`1& unhandledClauses)
at Simple.Data.DatabaseRunner.RunQuery(SimpleQuery query, IEnumerable`1& unhandledClauses)
at Simple.Data.SimpleQuery.Run()
at Simple.Data.SimpleQuery.Cast[T]()
at Simple.Data.SimpleQuery.TryConvert(ConvertBinder binder, Object& result)
at CallSite.Target(Closure , CallSite , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
at Ibfx.DataAccess.DataView`1.JoinFetchAll(Int32 startIndex, Int32 pageSize, Int32& count) in d:\Code\Dev\Src\Core\Ibfx.DataAccess\DataView.cs:line 747
at Ibfx.DataAccess.DataView`1.DynamicFetchAll(Int32 startIndex, Int32 pageSize, Int32& count) in d:\Code\Dev\Src\Core\Ibfx.DataAccess\DataView.cs:line 805
at Ibfx.DataAccess.TSS.Tests.AccountTest.TestCrazyEverything() in d:\Code\Dev\Src\Tests\Unit\Ibfx.DataAccess.TSS.Tests\AccountTest.cs:line 487
InnerException:
Skip seems to be the culprit. If I remove Skip, this query works.
I'm trying to do a pretty robust query and Simple.Data is giving me an error. I have an SQL table called "Album" with the following structure:
AlbumId (PK) bigint GenreId (FK) bigint ArtistId (FK) bigint Title varchar(128) Price decimal(18, 2) AlbumnArtUrl varchar(128)
I need to generate a query where I group by 2 fields and since Simple.Data doesn't have a "GroupBy" function, I'm adding an aggregate to my select query to force a "GroupBy" on the remaining fields in this way:
And I get the following error:
Skip seems to be the culprit. If I remove Skip, this query works.