Convert this SQL Server query to Linq with total

Question

I have this SQL Server query and want to convert this to Linq:

SELECT
    [FMonth],
    [Locationn],
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 1) AS Jan,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 2) AS Feb,
    (SELECT SUM(Sale) FROM[Fuels] WHERE FMonth = 3) AS March,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 4) AS April,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 5) AS May,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 6) AS Jun,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 7) AS Jul,
    (SELECT SUM(Sale) FROM [Fuels] WHERE FMonth = 8) AS  Aug 
FROM 
    [COSAuthNew].[dbo].[Fuels]

The result of this above query is in pic perfect but I need a total of all row in Total column:

OutPut

Data is stored like:

CompanyName | Month | Sale
H&S         |  Jan  | 1200
H&S         |  Feb  | 5000 
H&S1        |  March| 8000

But I want to display like:

CompanyName| Jan |  Feb | March | Total
H&S        |1200 | 5000  | 7000 | 13200
H&S1       |2200 | 6000  | 8000 | 16200

and show same data on View

Why don't you filter by month _and location_? Anyway, please do some web search on "LINQ + pivot" and you'll notice it's not a happy marriage. — Gert Arnold, Oct 21 '18 at 17:12
There are a lot of web resources where you can create a paid job and ask PRO DEV to help you. If you tried something and didn't work - we can try to help you with the specific issue but not with the solution. I believe that you have to use GROUP and PIVOT with the SQL and something different in the LINQ — SerjG, Oct 21 '18 at 17:57
Thank So much @GertArnold i got a point if i face problem then i will inform you. — Faizan Hamza, Oct 21 '18 at 18:05
You could try my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) though it doesn't exactly cover this use case. — NetMage, Oct 22 '18 at 21:04
Your query doesn't make sense to me in SQL - why are you retrieving the month sums over and over again for each row in `Fuels`? — NetMage, Oct 22 '18 at 21:16
basically i have a table Fuel with 3 column "Company name" "Month" and "Sale" i want to show like ComnayName | Jan | Feb | March | Total H&S |1200| 5000| 7000| 13200 like this — Faizan Hamza, Oct 23 '18 at 11:41
And what does the `FMonth` column represent in your SQL query output? How does it relate to the rest of the row? — NetMage, Oct 23 '18 at 20:20

score 0 · Answer 1 · answered Oct 23 '18 at 21:47

As you haven't provided any dbcontext or entity structure, the linq query will look like below for Jan-June sales and total. You need to group the rows by companyname (Location) and then use conditional values in aggregate function SUM.

var result = fules.GroupBy(g => g.CompanyName)
             .Select(s=> new {
                CompanyName = s.Key,
                Jan = s.Sum(x=> x.Month == 1? x.Sale : 0),
                Feb = s.Sum(x => x.Month == 2 ? x.Sale : 0),
                Mar = s.Sum(x => x.Month == 3 ? x.Sale : 0),
                Apr = s.Sum(x => x.Month == 4 ? x.Sale : 0),
                May = s.Sum(x => x.Month == 5 ? x.Sale : 0),
                June = s.Sum(x => x.Month == 6 ? x.Sale : 0),
                Total = s.Sum(x=> x.Sale)
            });

that should be another question i guess, this answer is for the subject in the question. — Vinit, Oct 25 '18 at 00:34

Convert this SQL Server query to Linq with total

1 Answers1