Click here to Skip to main content
16,017,238 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have a scenario. My table consists of three cols. For every entry there must be two rows with type id different.

GrpId    type id       entryID
------------------------------
10         1           1
-1         2           1
11         1           2
12         2           2
10         1           3
-1         2           3
10         1           4
-1         2           4
23         1           5
-1         2           5
24         1           6
-1         2           6
10         1           7
-1         2           7
10         1           8
-1         2           8


I need a single linq query
I have two conditions
when type id should be 1 and grpid should be 10 get entry id
result should be for that entry id i need grp id -1 and type id 2

Result should be:

GrpId    type id       entryID
------------------------------
-1         2           1
-1         2           3
-1         2           4
-1         2           7
-1         2           8


What I have tried:

I tried group by and some where but didn't work
Posted
Updated 14-Oct-16 9:27am
v2
Comments
Member 12794125 14-Oct-16 16:06pm    
Thank you Maciej Los.
Is it possible to do that in one single query

1 solution

Take a look at example:

C#
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("GrpId", typeof(int)));
dt.Columns.Add(new DataColumn("type id", typeof(int)));
dt.Columns.Add(new DataColumn("entryID", typeof(int)));

dt.Rows.Add(new object[]{10, 1, 1});
dt.Rows.Add(new object[]{-1, 2, 1});
dt.Rows.Add(new object[]{11, 1, 2});
dt.Rows.Add(new object[]{12, 2, 2});
dt.Rows.Add(new object[]{10, 1, 3});
dt.Rows.Add(new object[]{-1, 2, 3});
dt.Rows.Add(new object[]{10, 1, 4});
dt.Rows.Add(new object[]{-1, 2, 4});
dt.Rows.Add(new object[]{23, 1, 5});
dt.Rows.Add(new object[]{-1, 2, 5});
dt.Rows.Add(new object[]{24, 1, 6});
dt.Rows.Add(new object[]{-1, 2, 6});
dt.Rows.Add(new object[]{10, 1, 7});
dt.Rows.Add(new object[]{-1, 2, 7});
dt.Rows.Add(new object[]{10, 1, 8});
dt.Rows.Add(new object[]{-1, 2, 8});

//get entries
var entries = dt.AsEnumerable()
	.Where(x=>x.Field<int>("GrpId")==10)
	.Select(x=>x.Field<int>("entryID"))
	.ToList();
//get result
var result = dt.AsEnumerable()
	.Where(x=> x.Field<int>("GrpId")==-1 && 
			x.Field<int>("type id")==2 && 
			entries.Any(y=>y==x.Field<int>("entryID")))
	.ToList();


Good luck!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900