Filter IEnumerable Collection using Linq on ASP.NET Web API 2

Question

I need to filter my data based on a Boolean condition using Linq on ASP.NET Web Api 2 C#. But my JSON data returns null when I try filtering the data.

The data is a List of Invites, I want to filter the results based on a MemberID and if the user is attending(IsAttending)

Here is the Member Model

public class Member
{
    public int MemberID { get; set; }
    public string MemberName { get; set; }
    public bool IsAdmin { get; set; }
    public bool IsAttending { get; set; }
    public int MemberNumber { get; set; }
    public string PhoneNumber { get; set; }
    public int InviteID { get; set; }
    [JsonIgnore]
    public virtual Invite Iinvite { get; set; }
}

InviteModel

public class Invite
{
    public int InviteID { get; set; }
    public string Subject { get; set; }
    public string DateTime { get; set; }
    public virtual List<Member> Members { get; set; }
}

Original JSON data without filtering

[{
    "InviteID": 1,
    "Subject": "Hell",
    "DateTime": "March 2017",
    "Members": [{
        "MemberID": 1,
        "MemberName": "roger",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 1
    },
    {
        "MemberID": 2,
        "MemberName": "nkosi",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 1
    },
    {
        "MemberID": 3,
        "MemberName": "Mphile",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 1
    }]
},
{
    "InviteID": 2,
    "Subject": "Hell",
    "DateTime": "March 2018",
    "Members": [{
        "MemberID": 4,
        "MemberName": "roger",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 2
    },
    {
        "MemberID": 5,
        "MemberName": "nkosi",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 2
    },
    {
        "MemberID": 6,
        "MemberName": "Mphile",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 2
    }]
},
{
    "InviteID": 3,
    "Subject": "Hell",
    "DateTime": "Marchs 2017",
    "Members": [{
        "MemberID": 7,
        "MemberName": "Roger Nkosi",
        "IsAdmin": false,
        "IsAttending": true,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 3
    },
    {
        "MemberID": 8,
        "MemberName": "nkosi",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 3
    },
    {
        "MemberID": 9,
        "MemberName": "Mphile",
        "IsAdmin": false,
        "IsAttending": false,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 3
    }]
},
{
    "InviteID": 4,
    "Subject": "Hell",
    "DateTime": "Marchs 2017",
    "Members": [{
        "MemberID": 10,
        "MemberName": "Roger Nkosi",
        "IsAdmin": false,
        "IsAttending": true,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 4
    },
    {
        "MemberID": 11,
        "MemberName": "nkosi",
        "IsAdmin": false,
        "IsAttending": true,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 4
    },
    {
        "MemberID": 12,
        "MemberName": "Mphile",
        "IsAdmin": false,
        "IsAttending": true,
        "MemberNumber": 0,
        "PhoneNumber": null,
        "InviteID": 4
    }]
}]

Here is how I filter and return the data

return db.invites.Include("members").ToList()
                .Where(x => x.Members == db.members
                                .Where(xx => xx.IsAttending == true && xx.MemberID == 4)).ToList();

What is your expected result? Your LINQ query will never return any result because you are comparing collections by reference. Please add to your question an example of what will the Json look like after the filtering. — Federico Dipuma
– Federico Dipuma, Commented Jul 13, 2017 at 9:36
How can I put this? I want my JSON to be the same, but include members where the member id matches and the IsAttending == true — Roger Nkosi
– Roger Nkosi, Commented Jul 13, 2017 at 9:40
What happens if there are members with IsAttending both true and false for a specific invite? It's not clear how deep you want your result to be filtered, that's why I asked you to add your expected result inside the question. — Federico Dipuma
– Federico Dipuma, Commented Jul 13, 2017 at 9:42
If the member attribute IsAttending == false, that member wont be part of the return JSON data. — Roger Nkosi
– Roger Nkosi, Commented Jul 13, 2017 at 9:45
In the original JSON you never have IsAttending == true && MemberID == 4. — Scrobi
– Scrobi, Commented Jul 13, 2017 at 9:50

Federico Dipuma · Accepted Answer · 2017-07-13 10:23:28Z

1

What you want is not impossible, is just not achievable with a single LINQ query to the DB.

Assuming memberId contains the id of the member you want to filter on:

var invites = db.invites.Include(i => i.Members).Where(i => i.Members.Any(m => m.IsAttending && m.MemberID == memberId)).ToList(); // db query
invites = invites.Select(i =>
    new Invite
    {
        DateTime = i.DateTime,
        InviteID = i.InviteID,
        Subject = i.Subject,
        Members = i.Members.Where(m => m.IsAttending && m.MemberID == memberId).ToList()
    }).ToList(); // this removes other members (in memory)

 return invites;

edited Jul 13, 2017 at 10:23

answered Jul 13, 2017 at 10:16

Federico Dipuma

18.4k4 gold badges44 silver badges58 bronze badges

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

1 Comment

Raphaël Althaus Over a year ago

I would just include Members in the first query, to avoid n+1 requests...

Dan · Accepted Answer · 2017-07-13 09:39:36Z

0

You should use the Linq Any() operator.

It will look something more like

return db.invites.Include("members").Where(x => x.Members.Any(xx => xx.IsAttending && xx.MemberID == 4).ToList()

By the way, I removed the ToList() after db.invites because that will cause the entire members collection to enumerate. Not sure how big your database is, but that would eventually be a problem.

answered Jul 13, 2017 at 9:39

Dan

3,7331 gold badge25 silver badges18 bronze badges

1 Comment

Roger Nkosi Over a year ago

This works, but the way I need the result is, it would return the invite if the (xx => xx.IsAttending && xx.MemberID == 4) returns true, but I need the users which (xx => xx.IsAttending && xx.MemberID == 4) == false to not be included in the result.

Collectives™ on Stack Overflow

Filter IEnumerable Collection using Linq on ASP.NET Web API 2

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related