Using EF to return specific formatted JSON from ASP.NET MVC

Question

I have a model like below where PermissionGroup has 1-Many relationship with Permission and Permission and Roles have Many-Many relationship.

PermissionGroup 1-----* Permission *--------* Role

I have to be able to return JSON in the following format:

PermissionGroupId : 1,
PermissionGroupName : "Market",
Permissions : [
 {PermissionId : 1, PermissionName : "Create"},
 {PermissionId : 2, PermissionName : "Update"}
]

That will be asked for a specific RoleId.

Problem:

Since, PermissionGroup has no relation directly with Role so I cannot do a Where linq query. I could do the following but it is not returning desired result like above.

public JsonResult GetRolePermission(int roleid)
{
    var list = con.PermissionGroups.Select(pg => new
    {
        PermissionGroupId = pg.PermissionGroupId,
        PermissionGroupName = pg.PermissionGroupName,
        Permissions = pg.Permissons.Select(pe => new
        {
            PermissionId = pe.PermissionId,
            PermissionName = pe.PermissionName
        }) 
    })//Maybe do the where query here somehow like where(f => f.RoleId == roleid);

    return Json(list);
}

Any suggestion is appreciated.

Your json doesn't have any Roles member. Is it full? And what is roleid? How it is connected with PermissionGroups? — Tony
– Tony, Commented Feb 14, 2014 at 5:19
@Tony Thanks for your comment. Yes it does not and it is full JSON spec. I could not think of anything else at the time. So, I experimented with what I had in mind. RoleId is primary identifier for the Role entity. Please refer to the diagram I posted above for their relationships. — lbrahim
– lbrahim, Commented Feb 14, 2014 at 5:29

Tony · Accepted Answer · 2014-02-14 09:24:33Z

2

You can use Any method in Where? Like this:

var list = con.PermissionGroups
    .Where(pg => pg.Permissons.Any(p => p.Roles.Any(r => r.RoleId == roleId)))
    .Select(pg => new
    {
        PermissionGroupId = pg.PermissionGroupId,
        PermissionGroupName = pg.PermissionGroupName,
        Permissions = pg.Permissons.Select(pe => new
        {
            PermissionId = pe.PermissionId,
            PermissionName = pe.PermissionName
        })
    });

edited Feb 14, 2014 at 9:24

answered Feb 14, 2014 at 6:37

Tony

7,4453 gold badges28 silver badges34 bronze badges

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

1 Comment

lbrahim Over a year ago

Thanks for an answer. But I think I was not able to explain to you properly before. Please take a look at the revised question again.

lbrahim · Accepted Answer · 2014-02-14 07:59:21Z

0

I found a way to do this but highly doubt that it is efficient.

var getPermissionsForRole = context.Roles.Where(roleid => roleid.RoleId == 1).Select(p => p.Permissions).ToList();
var getAllPermissionGroup = context.PermissionGroups.ToList();

List<PermissionGroup> jsonPg = new List<PermissionGroup>();

foreach (var pg in getAllPermissionGroup)
{
    PermissionGroup newPg = new PermissionGroup();
    newPg.PermissionGroupId = pg.PermissionGroupId;
    newPg.PermissionGroupName = pg.PermissionGroupName;
    newPg.Permissons = new List<Permission>();

    foreach (var pers in getPermissionsForRole[0])
    {
        if (pers.PermissionGroup.PermissionGroupId == pg.PermissionGroupId)
        {
            Permission newPe = new Permission();
            newPe.PermissionId = pers.PermissionId;
            newPe.PermissionName = pers.PermissionName;

            newPg.Permissons.Add(newPe);
        }
    }

    if (newPg.Permissons.Count > 0)
    {
        jsonPg.Add(newPg); 
    }
}

var json = JsonConvert.SerializeObject(jsonPg);

Still open to better code.

answered Feb 14, 2014 at 7:59

lbrahim

3,83012 gold badges60 silver badges98 bronze badges

3 Comments

Tony Over a year ago

It is not that efficient because you make 2 queries which is worst than 1 query. I believe you can do it in 1 query.

lbrahim Over a year ago

Not to mention that is one ugly code. Thanks for your help. You saved me from a lot of bad practice.

Tony Over a year ago

You are welcome! I want also say that despite this code is not perfect, it is great that you are trying to do somehting, trying to find workaround (and you actually did!). It is very good side of your character.

Collectives™ on Stack Overflow

Using EF to return specific formatted JSON from ASP.NET MVC

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related