title: Getting Distinct Values from linq
body: <p>I have ad-hoc query which is used generate Gridview.DataSource=DataTable,and I'm trying to get distinct(No duplicates) results from based on the ID(ActionID) but the duplicates still exist.Using the distinct keyword in the select linq statement seems have no effect.(Still struggling with linq).Is there another way to do this?</p>
  
  <pre><code>    DataTable dtSerachResults = new DataTable(); 
  string qryStr = @"SELECT Distinct [ActionID]
                                      , [Title],convert(varchar(11),[DateRaised],109)as  DateRaised
                                  , convert(varchar(11)
                                  ,[TargetCompletionDate] ,109) as TargetCompletionDate 
  
                                  ,dbo.fn_GetPersonelName(fk_PersonnelID) as Responsible
                                  ,dbo.fn_GetStatusNameByID(fk_StatusID)   as [Status]
                                  ,dbo.fn_GetClientNameByID(fk_CustomerID)as Client
                                  ,dbo.fn_GetLocationByLocationID(fk_LocationID) as Location
                                  ,dbo.fn_GetClientNameByID(fk_CustomerID) as Client
                                  ,dbo.fn_GetSourceNameByID(fk_SourceID)as [Source]
                                  , dbo.fn_GetPersonelName(fk_RaisedBy) as RaisedBy
                                  ,dbo.fn_GetPersonelName(dbo.fn_GetActionItemPrimaryResponsibleEmployeeID([ActionID]))as [Primary]
                                 ,DATEDIFF(DD,DateRaised,GETDATE()) as Diff
                                  ,IsApproved=
                                  case IsApproved 
                                      when 1 then 'Approved'
                                      when 0 then 'Rejected'
                                      ELSE  'Waiting'
                                  End 
                          FROM [ActionTrackerTable] att
                          inner join [dbo].[ActionResponsibilitiesTable] art on art.fk_ActionID=att.[ActionID] ";
  
                  //title
                  if (!string.IsNullOrEmpty(ActionTracker.Title))
                  {
                      qryStr += " and Title like '" + ActionTracker.Title + "%'";
                  }
  
                  //dateraised
                  if (ActionTracker.DateRaised &gt; DateTime.MinValue)
                  {
                      qryStr += " and DateRaised &gt;= '" + ActionTracker.DateRaised.ToString() + "'";
                  }
                  //targetgedate
                  if (ActionTracker.TargetCompletionDate &gt; DateTime.MinValue)
                  {
                      qryStr += " and TargetCompletionDate&gt;= '" + ActionTracker.TargetCompletionDate.ToString() + "'";
                  }
  
                  //Location
                  if (ActionTracker.Location != null)
                  {
                      qryStr += " and   fk_LocationID= " + ActionTracker.Location.LocationID.ToString() + "";
                  }
                  //client
                  if (ActionTracker.Customer != null)
                  {
                      qryStr += " and   fk_CustomerID= " + ActionTracker.Customer.CustomerID.ToString() + "";
                  }
                  //source
                  if (ActionTracker.Source != null)
                  {
                      qryStr += " and   fk_SourceID= " + ActionTracker.Source.SourceID.ToString() + "";
                  }
                  //approval status
                  if (ActionTracker.SearchApprovalStr == "Approved")
                  {
                      qryStr += " and   IsApproved= " + 1.ToString();
                  }
                  if (ActionTracker.SearchApprovalStr == "Rejected")
                  {
                      qryStr += " and   IsApproved= " + 0.ToString();
                  }
  
                  if (ActionTracker.SearchApprovalStr == "Waiting")
                  {
                      qryStr += " and   IsApproved is null";
                  }
                  //status
                  if (ActionTracker.Status !=null)
                  {
                      if (ActionTracker.Status.StatusID&gt;0)
                      {
                          qryStr += " and   [fk_StatusID]= "+ActionTracker.Status.StatusID.ToString();
                      }
                  }
  
                  //ref num
                  if (ActionTracker.ActionTrackerID &gt; 0)
                  {
                      qryStr += " and   ActionID = " + ActionTracker.ActionTrackerID.ToString();
                  }
  
                  if ( (strSearchTerm)=="All" ||(strSearchTerm)=="Select Employee"  )
                  {                                )";
  
                      //rrun qry
                       //add the order str
                       qryStr += " order by ActionID desc ";
                       System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection();
                       using (sqlConn = ConnectionClass.CreateConnection.publicGetConn())
                       {
                           sqlConn.ConnectionString = ConnectionClass.CreateConnection.getConnectionString();
                           sqlConn.Open();
                           if (sqlConn.State == System.Data.ConnectionState.Open)
                           {
                               using (System.Data.SqlClient.SqlCommand sqlCMD = new System.Data.SqlClient.SqlCommand())
                               {
                                   sqlCMD.CommandText = qryStr;
                                   sqlCMD.CommandType = System.Data.CommandType.Text;
                                   sqlCMD.Connection = sqlConn;
                                   //parameters
                                   //no 1 is reponsbile
                                   //sqlCMD.Parameters.AddWithValue("@responsibleEmployeeID", responsibleEmployeeID);
  
                                   //return value
  
                                   System.Data.SqlClient.SqlDataReader rdr = sqlCMD.ExecuteReader();
                                   //get dataset
                                   DataSet DS = new DataSet();
                                   //load dataset in table
                                   DataTable dt = new DataTable();
                                   dt.Load(rdr);
  
                                   DS.Tables.Add(dt);
                  //get Distinct rows based on the Thier rows 
  
  
   var q2 = (from dr in dt.AsEnumerable() select dr).Distinct().CopyToDataTable();
                                       dtSerachResults = (from dr in dt.AsEnumerable() select dr).Distinct().CopyToDataTable();
  
  
                  }
  
  
              }
  </code></pre>