tjackadams tjackadams - 1 year ago 51
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 Source

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();