How to resolve: ''A TOP N or FETCH rowcount value may not be negative''

entity-framework entity-framework-6 sql sql-server

Question

Referring to my earlier question here - which works perfectly fine when executed within SQL Server Management Studio, I keep getting the A TOP N or FETCH rowcount value may not be negative error when I try to execute it within C# code with entity framework. The code I use is;

    var sDate = "20180925"; // DateTime.Now.AddMonths(NoOfMonthsBack).ToString("yyyyMMdd");
    var eDate = "20180101"; //DateTime.Now.ToString("yyyyMMdd");


var result =  ctx.Database.ExecuteSqlCommand("; WITH d(d) AS ( SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) " +
                                              "FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects " +
                                              "ORDER BY [object_id] ) AS n ) SELECT [Period] = CONVERT(VARCHAR(4), YEAR(d.d)) + '-' + CONVERT(VARCHAR(2), MONTH(d.d)), QtyTotal = ISNULL(SUM(o.QEXIT),0) " +
                                              "FROM d LEFT OUTER JOIN VE_STOCKTRANS AS o ON o.TRANSDATE >= d.d and STOCKID = @pID AND TRANSTYPE = @TransType AND o.TRANSDATE < DATEADD(MONTH, 1, d.d) " +
                                              "GROUP BY d.d ORDER BY d.d;",
                                              new SqlParameter("@StartDate", sDate),
                                              new SqlParameter("@EndDate", eDate),
                                              new SqlParameter("@pID", pID),
                                              new SqlParameter("@TransType", transType));

I would be grateful if someone with the experience could show me the right direction in order to resolve this issue.

Thanks.

1
0
9/25/2018 12:13:56 PM

Accepted Answer

It looks like NoOfMonthsBack is a positive value (e.g. int or float). You should use it negated if your goal is to go back in time. From the comment in the first line:

var sDate = DateTime.Now.AddMonths(-NoOfMonthsBack).ToString("yyyyMMdd");
2
9/25/2018 12:30:52 PM

Popular Answer

SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) is giving a negative, or could potentially do so

Wrap it with an abs() to guarantee a positive number:

SELECT TOP abs(DATEDIFF(MONTH, @StartDate, @EndDate)) + 1


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow