CODEWITHSUNDEEP
entity-frameworklinqsql-server-2008linq-to-sqlsql-to-linq-conversion
Sweetie
Sweetie

Reputation: 1368

Converting a SQL subqueries to Linq query

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

Upvotes: 2

Views: 52

Answers (1)

ocuenca
ocuenca

Reputation: 39326

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

Upvotes: 2

Related Questions