CODEWITHSUNDEEP
c#sqllinq
mattinsalto
mattinsalto

Reputation: 2356

Translating SQL to LINQ with Group By and max

I'm trying to write this SQL in LINQ, but I've gotten stuck. This is the SQL:

select sc.screen_control_id screenControlId, s.screen_name screenName, c.control_name controlName, sc.width, sc.position, 
case when max(pp.permision_id) > 0 then 1 else 0 end display
from ui_screen_control sc
join ui_screen s on sc.screen_id = s.screen_id
join ui_control c on sc.control_id = c.control_id
join ui_screen_control_action sca on sc.screen_control_id = sca.screen_control_id
join auth_action_permissiongroup ap on sca.action_id = ap.action_id
join auth_permissiongroup_permission pp on ap.permissiongroup_id = pp.permissiongroup_id
join auth_user_action_permissiongroup_permission uapp on pp.permissiongroup_permission_id = uapp.permissiongroup_permission_id
where uapp.user_id = 5472
group by sc.screen_control_id, s.screen_name, c.control_name, sc.width, sc.position

I've tried this LINQ but no luck:

from sca in db.ui_screen_control_action
join ap in db.auth_action_permissiongroup on sca.action_id equals ap.action_id
from uapp in db.auth_user_action_permissiongroup_permission
where
  uapp.user_id == 5472
group new {sca.ui_screen_control, sca.ui_screen_control.ui_screen, sca.ui_screen_control.ui_control, uapp.auth_permissiongroup_permission} by new {
  sca.ui_screen_control.screen_control_id,
  sca.ui_screen_control.ui_screen.screen_name,
  sca.ui_screen_control.ui_control.control_name,
  width = (int?)sca.ui_screen_control.width,
  position = (int?)sca.ui_screen_control.position
} into g
select new {
  screenControlId = g.Key.screen_control_id,
  screenName = g.Key.screen_name,
  controlName = g.Key.control_name,
  width = (int?)g.Key.width,
  position = (int?)g.Key.position,
  display = 
  g.Max(p => p.uapp.auth_permissiongroup_permission.permision_id) > 0 ? 1 : 0
}

Thanks in advance.

Upvotes: 0

Views: 76

Answers (3)

mattinsalto
mattinsalto

Reputation: 2356

from sc in db.ui_screen_control
join s in db.ui_screen on sc.screen_id equals s.screen_id
join c in db.ui_control on sc.control_id equals c.control_id
join sca in db.ui_screen_control_action on sc.screen_control_id equals sca.screen_control_id
join uapp in db.auth_user_action_permissiongroup_permission on sca.action_id equals uapp.action_id
where uapp.user_id == user.user_id
group new
{
 uapp.auth_permissiongroup_permission.permision_id
}
by new
{
 sc.screen_control_id,
 s.screen_name,
 c.control_name,
 sc.width,
 sc.position
}
into g
select new 
{
 screenControlId = g.Key.screen_control_id,
 screenName = g.Key.screen_name,
 controlName = g.Key.control_name,
 width = g.Key.width,
 position = g.Key.position,
 display = g.Min(p => p.permision_id) > 0 ? 1 : 0,
 required = false
}

Upvotes: 0

mattmanser
mattmanser

Reputation: 5796

I don't think you need to group it, you should just be able to use Any and do something like this:

db.ui_screen_control.Select(sc => 
    new {
         screenControlId = sc.screen_control_id,
         screenName = sc.ui_screen.screen_name,
         controlName = sc.ui_control.control_name,
         sc.width,
         sc.position,
         display = sc.ui_screen_control_actions
                    .auth_action_permissiongroup
                    .auth_permissiongroup_permission
                    .auth_user_action_permissiongroup_permission
                    .Any(uapp => uapp.user_id == 5472)
    });

display would then be a boolean, if you wanted a 1/0 still use something like .Any(uapp => uapp.user_id == 5472) ? 1 : 0 instead.

Without seeing your model, I don't think you need to use (int?) and cast things, the EF should figure out nullable ints itself unless your model has been incorrectly setup when compared to the DB.

Upvotes: 1

sgmoore
sgmoore

Reputation: 16067

I think the problem is the line

from uapp in db.auth_user_action_permissiongroup_permission

This will probably do something like a cross join whereas your original sql would suggest you need something like

join pp   in db.auth_permissiongroup_permission             on ap.permissiongroup_id            equals pp.permissiongroup_id
join uapp in db.auth_user_action_permissiongroup_permission on pp.permissiongroup_permission_id equals uapp.permissiongroup_permission_id

Upvotes: 0

Related Questions