Converting a SQL subqueries to Linq query

Question

I am trying to convert this sql into Linq query but I am getting no success. Could you help with the same.

SELECT  G.Id,G.UserGroupName, G.Status, G.IsDeleted ,(SELECT COUNT(*) FROM UserGroupMapping U WHERE U.UserGroupId=G.Id) [UserCount]   
,(SELECT COUNT(*) FROM UserGroupRoleMapping R WHERE R.UserGroupId=G.Id) [RolesCount]  
FROM UserGroup G

Do you have a one to many relationship between UserGroup and UserGroupRoleMapping, right? Could you show your entities and what have you tried so far? — ocuenca
– ocuenca, Commented Apr 20, 2016 at 13:59
LINQ is not a replacement for SQL. EF is an ORM, it deals with entities, not tables. Why do you want to convert this statement? You can't use the result as an object, which means it probably shouldn't be converted. If you have proper relations between your UserGroup and Mapping tables, you could ask for the Count() of the Roles and Groups relations. If you just want to display a report though, SQL is the simplest and fastest choice — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 20, 2016 at 13:59
I have proper relations between entities but how can I get Count() of the Roles and Groups relations ? any sort of example that can help me — Sweetie
– Sweetie, Commented Apr 20, 2016 at 14:03

ocuenca · Accepted Answer · 2016-04-20 14:16:32Z

2

If you have a one to many relationship between UserGroup and UserGroupRoleMapping and you have represented that relationship properly in your EF model, then you can do a query like this:

var query=context.UserGroups.Select(ug=>new{
                                             Id=usg.Id,
                                             UserGroupName=ug.UserGroupName, 
                                             Status=ug.Status,
                                             IsDeleted=ug.IsDeleted,
                                             RolesCount=ug.UserGroupRoles.Count() 
                                           });

I'm assuming you have a collection navigation property in your UserGroup entity:

public class UserGroup
{
   //...
   public virtual ICollection<UserGroupRoleMapping> UserGroupRoles{get;set;}
}

edited Apr 20, 2016 at 14:16

answered Apr 20, 2016 at 14:04

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sweetie Over a year ago

Wow! you just saved me. Thank a lot

Sweetie Over a year ago

If you can help with one more confusion that i have, it would really be great. If instead of the above code, I use below i.e I first bring in results in userGroupList and then convert it into the type I want after counting from another tables as you suggested. Is it right way? //foreach (var obj in userGroupList) //{ // objList.Add(new UserGroupRoleModel { TotalUsers = obj.UserGroupMappings.Count(), TotalRoles = obj.UserGroupRoleMappings.Count() }); //}

Collectives™ on Stack Overflow

Converting a SQL subqueries to Linq query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related