Distinct rows from DataSet using LINQ

[Lacutas]

Hi all,

I am having some problems using LINQ to access Distinct records from a Dataset. I have looked around and believe it should be as simple as added

.Distinct()

to my LINQ query below, though intellisense doesn't even give that option!

My DataSet has multiple tables, all populated with data. I have a table called DemographicCriteria which has the fields listed below.

• CriteriaID
• DemographicCode
• InputID
• ExpressionID
• Value

CriteriaID is an autonumber, InputID and ExpressionID are foreign keys from other tables. What I am trying to do is get the distinct DemographicCodes from this table.

I am using the following code:

DataAccess.RawDataDS ds = DataAccess.RawDataDSManagement.RawData;
DataTable demoCriteria = ds.Tables["DemographicCriteria"];
 
IEnumerable<DataRow> query =
                (from row in demoCriteria.AsEnumerable()
                 select row.Field<string>("DemographicCode"));
 
foreach (DataRow row in query)
{
    Console.WriteLine(row.Field<string>("DemographicCode"));
}

RawDataDSManagement is my main class for accessing the DataSet and I know that this is retrieving the data correctly as I have tied a DataGridView to this to check. The rest of it doesn't seem to be working though!

Does anyone have any ideas?

[Plater]

I am unfamiliar with LINQ, but in SQL you use "SELECT DISTINCT" instead of "SELECT" (you have to know what you are "distincting" though, and sometimes you do not get what you thought because you are selecting too many columns...that will probably make more sense once you try it out some)

Try changing:

IEnumerable<DataRow> query =
                (from row in demoCriteria.AsEnumerable()
                 select row.Field<string>("DemographicCode"));
 

to maybe:

IEnumerable<DataRow> query =
                (from row in demoCriteria.AsEnumerable()
                 select distinct row.Field<string>("DemographicCode"));
 

?

[Lacutas]

I am unfamiliar with LINQ, but in SQL you use "SELECT DISTINCT" instead of "SELECT" (you have to know what you are "distincting" though, and sometimes you do not get what you thought because you are selecting too many columns...that will probably make more sense once you try it out some)

Try changing:

IEnumerable<DataRow> query =
                (from row in demoCriteria.AsEnumerable()
                 select row.Field<string>("DemographicCode"));
 

to maybe:

IEnumerable<DataRow> query =
                (from row in demoCriteria.AsEnumerable()
                 select distinct row.Field<string>("DemographicCode"));
 

?

Thanks for your quick response. I have tried that though C# doesn't seem to accept distinct as a keyword and throws a compile time error.

[Lacutas]

Solution

It was actually a silly mistake on my part (though didn't find reference to it anywhere!). I forgot a using statement, in the end code looked like below.

using System.Linq;
 
DataAccess.RawDataDS ds = DataAccess.RawDataDSManagement.RawData;
DataTable demoCriteria = ds.Tables["DemographicCriteria"];
 
IEnumerable<string> query = (from row in demoCriteria.AsEnumerable()
             select row.Field<string>("DemographicCode")).Distinct();
 
foreach (string row in query)
{
    Console.WriteLine(row);
}

Obviously you replace the code in the foreach loop with something that does something useful!

Thanks for your help anyway guys!

[kyleroche]

Are you able to return more than one column w/ that query? I'm using about the same query... tring to figure out how to return three columns and put the distinct operator on one of them... any ideas?