Return Distinct Rows in the Web API

Question

We are currently querying the Oracle DB and returning the result in the JSON format.The Query results are returning the more duplicate rows. The Controller is something like below

public HttpResponseMessage Getdetails([FromUri] string[] Column)
    {
     List<string> selectionStrings = new List<string>();
     string connStr = ConfigurationManager.ConnectionStrings["PConnection"].ConnectionString;
     using (OracleConnection dbconn = new OracleConnection(connStr))
     {
      DataSet userDataset = new DataSet();
      var colDict = new Dictionary<string, string>()
      {
       {"CATEGORY", "STCD_PRIO_CATEGORY_DESCR.DESCR"},
       {"SESSION_NUMBER", "STCD_PRIO_CATEGORY_DESCR.SESSION_NUM"},
       {"SESSION_START_DATE","Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE)"},
       {"SESSION_START_TIME","STCD_PRIO_CATEGORY_DESCR.START_DATE"}}};
            foreach (string col in Column)
            {
                string selector = colDict[col];
                selectionStrings.Add(string.Format("{0} AS {1}", selector, col));
            }
            string selectString = string.Join(",", selectionStrings);

            var strQuery =   string.Format(@"SELECT {0}
                             FROM 
                             STCD_PRIO_CATEGORY_DESCR", selectString);
     }}}

So if I just add Distinct in the Select statement as

 var strQuery =   string.Format(@"SELECT DISTINCT {0}
                             FROM 
                             STCD_PRIO_CATEGORY_DESCR", selectString);

is that should be enough? Or should we be putting DISTINCT before all the Column names

yes DISTINCT is just used once directly after SELECT and before you specify the columns — Matt, Aug 12 '16 at 20:07
@Matt I tried using the DISTICT but the no.of Rows returned is just the same — trx, Aug 12 '16 at 20:59
If you are getting the same number of records with distinct in it it means that you are already only getting DISTINCT records. If you expect less records you may be including a column that is making the records unique which you don't really need or want and is causing the # of records to be higher than expected — Matt, Aug 12 '16 at 21:02

score -1 · Answer 1 · edited May 23 '17 at 12:14

-1

Use distinct or if you are still having trouble you can move the data result into a list and use linq to select only the unique rows.

var result = myList.GroupBy(test => test.id)
                   .Select(grp => grp.First())
                   .ToList();

taken from: Select distinct using linq

edited May 23 '17 at 12:14

Community

1
1

answered Aug 12 '16 at 20:48

Ginjo

26
9

1

Can we do this without using Linq and by directly giving them in the Query – trx Aug 12 '16 at 21:00
this will load more data than necessary – Matt Aug 12 '16 at 21:01
It will, not an ideal solution but more so a work around. – Ginjo Aug 15 '16 at 13:16

Return Distinct Rows in the Web API

1 Answers1