Answered by:
querying and get distinct from a datatable
Question

Hi list,
I have a datatable which I need to get some details from. The dt has an integer type column "mycolumn", which has data of 1 or other positive number. Now all I want to do is to take only those rows with positive figures, then count number of distinct one. For example if I got: 1, 2, 1, 3, 2, 3, 1, 1, 2, 3, I need to get 2, which means there are only two numbers which are not 1.
String Q= “where mycolumn<>1“
Int n= elt.select(Q).count(r => r.field<int>(“eltrow“));
first I got this error: "Cannot implicitly convert type int to bool" , then do now know how to finish the code even if I fix the error.
would you please help me on this?
thanks
Arvin
Tuesday, November 26, 2013 9:33 AM
Answers

hope this helps as start..
DataTable dt = new DataTable(); //1, 2, 1, 3, 2, 3, 1, 1, 2, 3, dt.Columns.Add("mycolumn", typeof(int)); dt.Rows.Add(1);dt.Rows.Add(2); dt.Rows.Add(1); dt.Rows.Add(3); dt.Rows.Add(2); dt.Rows.Add(3);dt.Rows.Add(1); dt.Rows.Add(1); dt.Rows.Add(2); dt.Rows.Add(3); int n = dt.AsEnumerable().Select(x => x.Field<int>("mycolumn") > 0).Distinct().Count(); Console.Write(n);
 Edited by Venkat786 Tuesday, November 26, 2013 10:52 AM
 Marked as answer by azolfaghari Tuesday, November 26, 2013 3:05 PM
Tuesday, November 26, 2013 10:51 AM
All replies

Hi Arvin,
Could you give more context? Are you using Entity Framework? What is "elt"? Try this...
public int GetPositiveCount() { using (var dataContext = new DatabaseContext()) { return dataContext.Entities.Where(p => p.mycolumn > 0).Distinct().Count(); } }
Notice that "DatabaseContext" is your database context, "Entities" are the entity collection you area trying to access and "mycolumn" is the column you want to filter values from. I think this should work as you expect.
Best regards,
Fernando Rocha
 Edited by FernandoRocha Tuesday, November 26, 2013 10:49 AM
Tuesday, November 26, 2013 10:48 AM 
hope this helps as start..
DataTable dt = new DataTable(); //1, 2, 1, 3, 2, 3, 1, 1, 2, 3, dt.Columns.Add("mycolumn", typeof(int)); dt.Rows.Add(1);dt.Rows.Add(2); dt.Rows.Add(1); dt.Rows.Add(3); dt.Rows.Add(2); dt.Rows.Add(3);dt.Rows.Add(1); dt.Rows.Add(1); dt.Rows.Add(2); dt.Rows.Add(3); int n = dt.AsEnumerable().Select(x => x.Field<int>("mycolumn") > 0).Distinct().Count(); Console.Write(n);
 Edited by Venkat786 Tuesday, November 26, 2013 10:52 AM
 Marked as answer by azolfaghari Tuesday, November 26, 2013 3:05 PM
Tuesday, November 26, 2013 10:51 AM