tjackadams tjackadams - 6 months ago 9
SQL Question

SQL Query to Linq to Entities - C#

I have been trying to convert this SQL statement into a linq as i am trying to move the functionality into a program.

Here is the SQL statement

SELECT cust.sg_group_name AS customer,
(SELECT Sum(du.used_space)
FROM sg_groups AS clnt
LEFT JOIN client_disk_usage AS du
ON clnt.sg_group_id = du.sg_group_id
AND clnt.group_role_id = 3
WHERE clnt.parent_group_id = cust.sg_group_id
AND du.day_of_month = 15
AND du.month_of_year = 05
AND du.used_space_year = 2016) AS disk_usage
FROM sg_groups AS cust
WHERE cust.group_role_id = 2
ORDER BY cust.sg_group_name


Essentially the output is just a list with two columns

customer disk_usage
Customer1 136401537652
Customer2 42208008210


If possible i just want to convert this to a linq statement. I have tried putting the query into LinqPad, but it doesn't seem to want to convert from SQL to Linq (just comes up with a blank white page). I have had a crack at the query myself, but i either get something that doesn't work altogether, or an incorrect number of results.

If anyone has any suggestions that would be great!

Answer

Apologies for the delayed response. I've marked @Anil answer up as this is the one that helped me find the answer. You solution did work @Sathish but it can be accomplished in a single command. Here is my final solution. Many thanks for your help!

storeGridUsage = (
                from cust in db.sg_groups
                from client in db.sg_groups
                join du in db.client_disk_usage on client.SG_GROUP_ID equals du.SG_GROUP_ID
                where client.GROUP_ROLE_ID == 3
                where client.PARENT_GROUP_ID == cust.SG_GROUP_ID && du.DAY_OF_MONTH == day && du.MONTH_OF_YEAR == month && du.USED_SPACE_YEAR == year
                where cust.GROUP_ROLE_ID == 2
                orderby cust.SG_GROUP_NAME
                group new {cust, du} by cust.SG_GROUP_NAME
                into g
                select new StoreGridUsage
                {
                    CustomerName = g.Key,
                    DiskUsageInBytes = g.Sum(o => o.du.USED_SPACE)
                }).ToList();