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

Answer 1

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;}
}