Returning partial rows with Linq

[David]

How do I specify what columns to include in a Linq to Dataset query?

I have a dataset with four rows: A key (int) field, a Customer(string)
field, a Facility(string) field, and a station(string) field. Basically,
this dataset contains the results of a query of stations. A customer can
have several facilities. A facility can have several stations. The
combination of customer, facility, and station uniquely identifies a station.

What I want is, for a given customer, query the table and get a list of
facilities. My first try was:

IEnumerable<DataRow> facilitiesquery = (from r in
dsPlantInventory.Tables[0].AsEnumerable()
where
r.Field<string>("Customer").Equals(custname)
select
r).Distinct<DataRow>(System.Data.DataRowComparer.Default);

This almost worked, but of course, the Distinct clause returned distinct
rows, not distinct facility entries. What I really want is to a query that
returns a set of DataRow objects. Each datarow would have one column, which
is "facility" and all duplicates would be removed.

In SQL, it would look like: "Select Distinct Facility from StationTable
Where Customer='custname' //replace 'custname' with the actual customer
name that was passed in.

I tried to replace the final r with r.Field<string>("Facility") , but of
course I ended up with a list of strings, not a list of DataRows, and my
Distinct clause failed.

My Linq book unfortunately has no examples where the Linq query in that form
returns a set of data rows that contain only a subset of fields from the
original table.

 

[Frans Bouma [C# MVP]]

How do I specify what columns to include in a Linq to Dataset query?

I have a dataset with four rows: A key (int) field, a Customer(string)
field, a Facility(string) field, and a station(string) field. Basically,
this dataset contains the results of a query of stations. A customer can
have several facilities. A facility can have several stations. The
combination of customer, facility, and station uniquely identifies a station.

What I want is, for a given customer, query the table and get a list of
facilities. My first try was:

IEnumerable<DataRow> facilitiesquery = (from r in
dsPlantInventory.Tables[0].AsEnumerable()
where
r.Field<string>("Customer").Equals(custname)
select
r).Distinct<DataRow>(System.Data.DataRowComparer.Default);

This almost worked, but of course, the Distinct clause returned distinct
rows, not distinct facility entries. What I really want is to a query that
returns a set of DataRow objects. Each datarow would have one column, which
is "facility" and all duplicates would be removed.

In SQL, it would look like: "Select Distinct Facility from StationTable
Where Customer='custname' //replace 'custname' with the actual customer
name that was passed in.

I tried to replace the final r with r.Field<string>("Facility") , but of
course I ended up with a list of strings, not a list of DataRows, and my
Distinct clause failed.

My Linq book unfortunately has no examples where the Linq query in that form
returns a set of data rows that contain only a subset of fields from the
original table.

A DataRow isn't something which can live on its own, it is always part
of a DataTable. This means that if you create a linq query like you did
above, you are simply traversing DataRow objects in a datatable and if
you want to return DataRow objects, they too have to be part of a
DataTable. As you want to do distinct on a subset of the columns, you
have to make sure the other columns are empty.

If you are just interested in the data, you could considering creating
a list of anonymous types with the fields over which you want to
distinct, then add that data again to a NEW datatable. If you're
interested in enumerating over the original datatable, you are out of
luck, I'm afraid.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[David]

Thanks. I was confused. I didn't realize that the rows returned from the
query were actually pointers to the original table. I thought that new rows
were being created, which were copies of the old rows, such as might happen
with a SQL query directly to the database.

It looks like what I will have to do is:

run the query

Copy the results to a datatable

Edit the datatable to include only the rows I'm interested in.

Now run a new query against the new datatable, with the DISTINCT clause.

Copy those results to a new, final datatable.


Which means it might just be easier to do this with something other than Linq.

How do I specify what columns to include in a Linq to Dataset query?

I have a dataset with four rows: A key (int) field, a Customer(string)
field, a Facility(string) field, and a station(string) field. Basically,
this dataset contains the results of a query of stations. A customer can
have several facilities. A facility can have several stations. The
combination of customer, facility, and station uniquely identifies a station.

What I want is, for a given customer, query the table and get a list of
facilities. My first try was:

IEnumerable<DataRow> facilitiesquery = (from r in
dsPlantInventory.Tables[0].AsEnumerable()
where
r.Field<string>("Customer").Equals(custname)
select
r).Distinct<DataRow>(System.Data.DataRowComparer.Default);

This almost worked, but of course, the Distinct clause returned distinct
rows, not distinct facility entries. What I really want is to a query that
returns a set of DataRow objects. Each datarow would have one column, which
is "facility" and all duplicates would be removed.

In SQL, it would look like: "Select Distinct Facility from StationTable
Where Customer='custname' //replace 'custname' with the actual customer
name that was passed in.

I tried to replace the final r with r.Field<string>("Facility") , but of
course I ended up with a list of strings, not a list of DataRows, and my
Distinct clause failed.

My Linq book unfortunately has no examples where the Linq query in that form
returns a set of data rows that contain only a subset of fields from the
original table.

A DataRow isn't something which can live on its own, it is always part
of a DataTable. This means that if you create a linq query like you did
above, you are simply traversing DataRow objects in a datatable and if
you want to return DataRow objects, they too have to be part of a
DataTable. As you want to do distinct on a subset of the columns, you
have to make sure the other columns are empty.

If you are just interested in the data, you could considering creating
a list of anonymous types with the fields over which you want to
distinct, then add that data again to a NEW datatable. If you're
interested in enumerating over the original datatable, you are out of
luck, I'm afraid.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)