locked
SQL in ASP.net COre Webpi RRS feed

  • Question

  • User2041008840 posted

    hi I Want write this query in asp net core web api 

    select SUM(Rate * Quantity) as SubTotal, SUM((Rate * Quantity) + ((Rate * Quantity)* (GST/100))) as GSTAmount , InvoiceNo, 
    [Name], GSTNo,(SUM(Rate * Quantity) + SUM((Rate * Quantity) + ((Rate * Quantity)* (GST/100)))) as Total  from 
    SalesItem si Inner join Sales s on  si.SalesID = s.ID
    inner join Customer c on s.CustomerID = c.ID
    where s.SetupID = 1
    Group by InvoiceNo, [Name],GSTNo

    i tried in linq and entity framework but its not working properly and not giving any output. 

    linq -  var a = _context.SalesItem
                    .Include(a => a.Sales)
                    .Where(a => a.Sales.SetupID == setupID)
                    .GroupBy(a => new { a })
                    .Select(a => new SaleReportViewModel
                    {
                        SubTotal = a.Sum(x => x.Rate * x.Quantity),
                        GSTTotal = a.Sum(x => (x.Rate * x.Quantity) + (x.Rate * x.Quantity) * (x.GST / 100)),
                    }).AsQueryable();
                var query = (from si in _context.SalesItem
                             join s in _context.Sales on si.SalesID equals s.ID
                             where s.SetupID == setupID
                             group new { si } by new
                             {
                                 si.IGST,
                                 si.GST,
                                 si.Rate,
                                 si.Quantity
                             } into g
                             select new SaleReportViewModel
                             {
                                 SubTotal = g.Sum(a => a.si.Rate * a.si.Quantity)
                             }).AsQueryable();

    how do i implement or use it and get output in json

    Thursday, September 24, 2020 2:16 PM

All replies

  • User1358036820 posted

    We you say you are not getting any output what do you mean?  How are you checking for output?  A query like this would only return once you start accessing the results.

    Thursday, September 24, 2020 3:08 PM
  • User475983607 posted

    My preference is to not covert a complex TSQL script to LINQ.  Especially if the TSQL script works as expected.  Simply execute a raw SQL script.  LINQ converts to SQL anyway so why go through the extra conversion?  

    https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

    Thursday, September 24, 2020 3:15 PM
  • User2041008840 posted

    So how to use Raw SQL in ENTITY FRAMEWORK. 
    I mean 

    I know this code i read it from article on microsoft. but i only see simple example but not complex one. 
    i can use FromSQLRaw but still i dont know, how i merge this query into it.

    Thursday, September 24, 2020 5:10 PM
  • User2041008840 posted

    my function is correct but the problem is in query. when i run the query in tsql the all rows are merge and sum up and show one row but in linq its show separate and not sum up.

    Thursday, September 24, 2020 5:12 PM
  • User1120430333 posted

    So how to use Raw SQL in ENTITY FRAMEWORK. 
    I mean 

    I know this code i read it from article on microsoft. but i only see simple example but not complex one. 
    i can use FromSQLRaw but still i dont know, how i merge this query into it.

    You should go with executing a stored procedure.

    https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx

    Thursday, September 24, 2020 10:57 PM
  • User2041008840 posted

    Yes I tried it already but, showing error that I need to all columns of table. 
    after taking all columns the it not get group by properly not showing output as i want. 

    Linqer SQL to linq software and put my tsql query in it and convert into linq and its worked as i want to. 


    Thank you for your valuable efforts and time. 

    Thank you,
    Prathamesh Shende

    Friday, September 25, 2020 7:30 AM