I put together a sample to write the LINQ statement. However, your data seems off. You have repeated IDs for different states, not sure if that is intentional.
var states = new List<States>
{
new States() {Id = 1, State = "Pending"},
new States() {Id = 1, State = "Delete"},
new States() {Id = 2, State = "Pending"},
new States() {Id = 2, State = "Approved"},
new States() {Id = 3, State = "Pending"}
};
var ids = states.Where(x => x.State != "Delete").ToList();
Do you want to return 2 records with id 2 and one record with id 3 and don't want to return all the record only if one of the record's state is delete?
If so , you could refer to the code below.
protected void Page_Load(object sender, EventArgs e)
{
List<States> list = new List<States>();
list.Add(new States { Id = 1, State = "Pending" });
list.Add(new States { Id = 1, State = "Delete" });
list.Add(new States { Id = 2, State = "Pending" });
list.Add(new States { Id = 2, State = "Approved" });
list.Add(new States { Id = 3, State = "Pending" });
var deletedIds= list.Where(l => l.State == "Delete").Select(l=>l.Id).Distinct().ToList();// get all the deleted ids
list = list.Where(m => !deletedIds.Contains(m.Id)).ToList();// get record whose id is not in deletedIds
GridView1.DataSource = list; //show the result
GridView1.DataBind();
}
public class States
{
public int Id { get; set; }
public string State { get; set; }
}
The result.
Best regards,
Ackerly Xu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
I put together a sample to write the LINQ statement. However, your data seems off. You have repeated IDs for different states, not sure if that is intentional.
var states = new List<States>
{
new States() {Id = 1, State = "Pending"},
new States() {Id = 1, State = "Delete"},
new States() {Id = 2, State = "Pending"},
new States() {Id = 2, State = "Approved"},
new States() {Id = 3, State = "Pending"}
};
var ids = states.Where(x => x.State != "Delete").ToList();
The ids can be repeated. It is not like a Primary Key, there is another primary key on the table.
So I just need to get ALL ids where it does not contain a Delete state.
Do you want to return 2 records with id 2 and one record with id 3 and don't want to return all the record only if one of the record's state is delete?
If so , you could refer to the code below.
protected void Page_Load(object sender, EventArgs e)
{
List<States> list = new List<States>();
list.Add(new States { Id = 1, State = "Pending" });
list.Add(new States { Id = 1, State = "Delete" });
list.Add(new States { Id = 2, State = "Pending" });
list.Add(new States { Id = 2, State = "Approved" });
list.Add(new States { Id = 3, State = "Pending" });
var deletedIds= list.Where(l => l.State == "Delete").Select(l=>l.Id).Distinct().ToList();// get all the deleted ids
list = list.Where(m => !deletedIds.Contains(m.Id)).ToList();// get record whose id is not in deletedIds
GridView1.DataSource = list; //show the result
GridView1.DataBind();
}
public class States
{
public int Id { get; set; }
public string State { get; set; }
}
Participant
1008 Points
2687 Posts
How to obtain all ids that do not contain a Deleted State
May 14, 2019 01:46 PM|tvb2727|LINK
I am trying to find the correct (all in one statement) LINQ statement that will return me all Ids where a record does NOT contain a state of Deleted.
I can't seem to figure it out.
Table is below.
It should return 2 and 3 and NOT 1 because 1 contains a state of Deleted
State
Pending
Participant
1250 Points
422 Posts
Re: How to obtain all ids that do not contain a Deleted State
May 14, 2019 04:07 PM|deepalgorithm|LINK
I put together a sample to write the LINQ statement. However, your data seems off. You have repeated IDs for different states, not sure if that is intentional.
var states = new List<States> { new States() {Id = 1, State = "Pending"}, new States() {Id = 1, State = "Delete"}, new States() {Id = 2, State = "Pending"}, new States() {Id = 2, State = "Approved"}, new States() {Id = 3, State = "Pending"} }; var ids = states.Where(x => x.State != "Delete").ToList();Contributor
3460 Points
1300 Posts
Re: How to obtain all ids that do not contain a Deleted State
May 15, 2019 09:58 AM|Ackerly Xu|LINK
Hi tvb2727,
Do you want to return 2 records with id 2 and one record with id 3 and don't want to return all the record only if one of the record's state is delete?
If so , you could refer to the code below.
protected void Page_Load(object sender, EventArgs e) { List<States> list = new List<States>(); list.Add(new States { Id = 1, State = "Pending" }); list.Add(new States { Id = 1, State = "Delete" }); list.Add(new States { Id = 2, State = "Pending" }); list.Add(new States { Id = 2, State = "Approved" }); list.Add(new States { Id = 3, State = "Pending" }); var deletedIds= list.Where(l => l.State == "Delete").Select(l=>l.Id).Distinct().ToList();// get all the deleted ids list = list.Where(m => !deletedIds.Contains(m.Id)).ToList();// get record whose id is not in deletedIdsGridView1.DataSource = list; //show the result GridView1.DataBind(); } public class States { public int Id { get; set; } public string State { get; set; } }
The result.
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Participant
1008 Points
2687 Posts
Re: How to obtain all ids that do not contain a Deleted State
May 16, 2019 08:26 PM|tvb2727|LINK
The ids can be repeated. It is not like a Primary Key, there is another primary key on the table.
So I just need to get ALL ids where it does not contain a Delete state.
Participant
1008 Points
2687 Posts
Re: How to obtain all ids that do not contain a Deleted State
May 16, 2019 08:27 PM|tvb2727|LINK
This is what I needed. Thanks!