0

I have this SQL Query :

DECLARE @pageNo int=1
DECLARE @pageSize int=2

SELECT top 2 id,
Title,
Author,
NumberOfPages,
PublishedAt,
RowNumber
FROM 

(

SELECT top 2 id, Title, Author,
NumberOfPages,PublishedAt,
Row_Number() over (order by id desc) as RowNumber 
FROM Books 

)T
WHERE
T.RowNumber BETWEEN ((@pageNo-1)*@pageSize)+1 AND (@pageNo*@pageSize)

I want Execute this SQL Query with C# in ASP.NET core

I tried this Code :

[HttpGet]
public async Task<ActionResult<IEnumerable<Books>>> GetBooks()
        {
          

  
      return await _context.Books.FromSqlRaw("Select * from Books where id = 2").ToListAsync();

           

        }

But this Code work only with Single Sql Query Line like last example

Select * from Books where id = 2

but when I try with Complex SQL Query with Multi lines is not Working

I hope your help

thank so much

Mohammad Eibou
  • 13
  • 4
  • I'm not sure I follow what you're trying to achieve here. Your subquery will only return between 0 and 2 rows, so if the value of `@pageNo` is ever higher than 2, you'll get no rows. Also, if `@pageSize` is ever higher than `2` you'll still only get 2 rows. – Thom A Feb 14 '22 at 13:25
  • this SQL Query is working perfect with other programming language for example with php was perfect working but I dont know how exeute Same query with ASP.net core – Mohammad Eibou Feb 14 '22 at 13:34

2 Answers2

0

you have to put the whole query in one line or use "+" or string builder to connect lines

var query ="DECLARE @pageNo int=1 DECLARE @pageSize int=2 SELECT top 2 id,..";

//or
 var query = "DECLARE @pageNo int=1 " +
             "DECLARE @pageSize int=2 " + 
             "SELECT top 2 id, " +
Serge
  • 40,935
  • 4
  • 18
  • 45
0

I think best approach will be to write that query in text file & place it in your project then use this line of code to read text from that file

string queryText = File.ReadAllText(@"D:\yourpathhere\QueryTextFile.txt");

then use queryText variable

saif
  • 26
  • 1