How to create a LINQ query based on this SQL?

Question

Hello, i try to convert this kind of sql query into linq in visual basic, but i get stuck on how to make a percent.. I also don't know how to use linqpad for creating this linq Please help.

SELECT CASE RIGHT(PICName, 3) 
         WHEN '(P)' THEN 'Problem' 
         WHEN '(R)' THEN 'Request' 
         ELSE 'Other' 
         END AS [Requests/Problems], 
       COUNT(RIGHT(PICName, 3)) AS Amount, 
       CONVERT(decimal(18, 2), 
                CONVERT(Decimal(18, 2), COUNT(RIGHT(PICName, 3))) / 
                CONVERT(Decimal(18, 2), 
                         (SELECT COUNT(RIGHT(PICName, 3)) FROM Ticket)) 
                   * 100) AS [% Amount]
FROM Ticket
GROUP BY RIGHT(PICName, 3)

i need to export the result to a datagrid it's like:

Requests/Problems | Amount | % Amount
------------------------------------------------------
Problem | 20 | 20.00        
Request | 45 | 45.00
Other | 35 | 35.00

Thank You.

Answer 1

You could give this tool a try - http://www.sqltolinq.com/ - I've never used it, but seen it recommended on here before.

Answer 2

Let's start by stripping this down to the important parts:

SELECT RIGHT(PICName, 3), 
       COUNT(RIGHT(PICName, 3)) AS Amount, 
       COUNT(RIGHT(PICName, 3)) / (SELECT COUNT(RIGHT(PICName, 3)) FROM Ticket)) 
                   * 100) AS [% Amount]
FROM Ticket
GROUP BY RIGHT(PICName, 3)

From there, it looks easy:

 int count = db.Tickets.Count();
 var query = from t in db.Tickets
             group by t.PicName.Substring(0,3) in g
             select new 
             {
                Requests_Problems = g.Key == "(P)" ? "Problem" : 
                                    g.Key == "(R)" ? "Request" : "Other",
                Amount = g.Count,
                PercAmount = (g.Count * 100.0) / count
             }

Answer 3

Hi all

actually I don't know what to start. I'm new in using LINQ. Ticket table consists of ID, assigndate, startdate, deadline, PICname (PIC as in 'person in charge', contains the name of people who is in charge for the ticket). In the PICname field, there is also '(P)' (problem) or '(R)' (request) to classify the ticket into problem and request. These are some of the field in the Ticket table. Ticket is like queue for the officer to handle the problem reported from a user.

Sorry, I don't get what you called EF.. but i think, ya this is linq to sql.

I've tried LINQer (from http://www.sqltolinq.com/) before i asked this question, but it cannot convert "COUNT" into LINQ. it's not free anyway

Thanks James, but i don't think i could convert it into a visual basic code. Maybe, you can explain what for is Key ? and in group by t.PicName.Substring(0,3) in g, is that really in and not into? I'm sorry cos I'm truly a beginner in LINQ but I have to use LINQ in this application..

Hey, do anyone of you know where to start understanding LINQ to SQL for beginners like me?