2

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
Sweetie
  • 1,298
  • 6
  • 24
  • 48
  • 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 Apr 20 '16 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 Apr 20 '16 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 Apr 20 '16 at 14:03

1 Answers1

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;}
}
ocuenca
  • 38,548
  • 11
  • 89
  • 102
  • Wow! you just saved me. Thank a lot – Sweetie Apr 20 '16 at 14:31
  • 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() }); //} – Sweetie Apr 21 '16 at 06:25