Syntax needed: Select from untyped datatable, cast into strongly-typed

I have a basic datatable which I'm trying to query, but I'd like the results in a specific strongly-typed format I'm using.  

The strongly-typed objects already work fine but I'm missing something on the syntax of the query to get results into that format.


//SessionHandler.EditFiles is a (poorly normalized) basic datatable with a string column "Department".  
//It is populated at this point.
//DSResources.DepartmentsRow is the strongly-typed row format I wish to use.

IEnumerable<DSResources.DepartmentsRow> results =
(
  from E in SessionHandler.EditFiles.AsEnumerable()
  select new 
    {
     Name = E["Department"].ToString(),
     ID = 0
    }
 ) as IEnumerable<DSResources.DepartmentsRow>;

//hmm,  no errors, can we iterate through the results?
foreach (DSResources.DepartmentsRow dept in results)
{
//kablamo.  nope.
//We get an invocation error as this occurs during a databind()
//The underlying problem is that "results" is null.
//...Why?
}

Open in new window

LVL 3
tknudsen-qecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tknudsen-qecAuthor Commented:
I should point out that sticking with anonymous types works fine, but, of course, that's not what I want to do.

var results =
(
  from E in SessionHandler.EditFiles.AsEnumerable()
  select new 
    {
     Name = E["Department"].ToString(),
     ID = 0
    }
 );

//hmm,  no errors, can we iterate through the results?
foreach (var dept in results)
{
//Yep.  Too bad we're stubborn.
}

Open in new window

0
Todd GerbertIT ConsultantCommented:
I think the "select new { ... }" on line 8 in your original question is returning an anonymous type, you might try "select new DSResources.DepartmentsRow(name, id)"
0
Todd GerbertIT ConsultantCommented:
To elaborate...you're attempting to cast an IEnumerable<AnonymousType> into an IEnumerable<DSResources.DepartmentsRow>, with the "as" operator, which can't be done so the "as" operator returns null.

IEnumerable<DSResources.DepartmentsRow> results =
  from E in SessionHandler.EditFiles.AsEnumerable()
  select new DSResources.DepartmentRow(E["Department"], 0);

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

tknudsen-qecAuthor Commented:

I think we're getting closer.

You're right that its an anonymous type attempting to convert to DepartmentsRow via "as".  I'm glad to know that's the reason its not working, however the syntax you've provided doesn't work.  

DepartmentsRow doesn't support 2 parameters (it only supports an inaccessible DataRowBuilder parameter)

Is there any other way to perform the conversion? Or alternately can we somehow use the NewDepartmentsRow method that would create the row cleanly?

Does it help to clarify that DepartmentsRow is defined automatically by a .xsd Dataset?
0
Todd GerbertIT ConsultantCommented:
Just change select new DSResources.DepartmentRow(E["Department"], 0); to whatever the correct constructor is for DepartmentRow; if it doesn't take any parameters don't provide any, use type initialization instead
select new DSResources.DepartmentRow() { Name = E["Department"], ID = 0 }
0
Todd GerbertIT ConsultantCommented:
Of course, that assumes the DepartmentRow class exposes two public properties, "Name" and "ID."
0
tknudsen-qecAuthor Commented:
Just the one "inaccessible due to isolation level" parameter.
See image.

Btw I appreciate your assistance, you're clearly far more versed with the topic than I.

DepartmentsRow.GIF
0
Todd GerbertIT ConsultantCommented:
Sorry, I was thinking of another method that visual studio adds to the auto-generated xsd datasets.
If you want these results in a DataSet, then you need to create the dataset and populate it. To my knowledge the LINQ queries will only return an IEnumerable<T>, List<T> or Array<T> - they won't return a DSResources.  The code below will populate a DSResources dataset.
 

DSResources theDataSet = new DSResources();
foreach(DataRow row in SessionHandler.EditFiles)
    theDataSet.AddDepartmentRow((string)row["Department"]);

foreach(DSResources.DepartmentRow deptRow in theDataSet)
    // do yer stuff

Open in new window

0
Todd GerbertIT ConsultantCommented:
If you wanted to perform some filtering on EditFiles, for example to exclude records in the DataTable with a blank department:
DSResources theDataSet = new DSResources(); 
foreach(DataRow row in (from DataRow dr in SessionHandler.EditFiles
                        where dr["Department"] != String.Empty
                        select dr).ToArray())
    theDataSet.AddDepartmentRow((string)row["Department"]); 
 
foreach(DSResources.DepartmentRow deptRow in theDataSet) 
    // do yer stuff

// Or
DSResources theDataSet = new DSResources(); 
foreach(DataRow row in SessionHandler.EditFiles.Where(DataRow x => x["Department"] != String.Empty).ToArray())
    theDataSet.AddDepartmentRow((string)row["Department"]); 
 
foreach(DSResources.DepartmentRow deptRow in theDataSet) 
    // do yer stuff

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tknudsen-qecAuthor Commented:
Found a URL that seems to suggest that what I want to do cannot be done without workarounds.
http://www.code-magazine.com/articleprint.aspx?quickid=0712052&printmode=true

"When projecting the results of a LINQ to DataSet query, the fact that the values must be projected into a type other than DataRow can be limiting. This must be done because DataRows cannot be created that do not tie to an existing DataTable. This means that the DataRow column values are copied to the new instance based on the specified selector and hence any changes to the new instance are not reflected in the original DataRow. "
Which I don't completely understand but I'm abandoning this line and going with a looping "hack" instead.

Thanks for your assistance tgerbert.

tk
0
tknudsen-qecAuthor Commented:
I'm still thinking there must be a way to do it, (going to look at outer joining the datatable to a strongly-typed datatable and see what I can get) but I've wasted enough of your time on this one :)
0
Todd GerbertIT ConsultantCommented:
The LINQ query would have been implemented as a loop by the compiler anyway, so you're not losing any functionality, I wouldn't call it a "hack."
0
Todd GerbertIT ConsultantCommented:
Perhaps this is what you want...
In this case there is a DataTable with a String column named "Department"; the TypedDataSet is defined in TypedDataSet.xsd, and contains an ID (int) column and a Department (string) column. The XSD defines the default value of the ID column as 0.

static void Main(string[] args)
{
	// Initialize the basic datatable, to simulate SessionHandler.EditFiles
	DataTable untypedTable = new DataTable();
	untypedTable.Columns.Add("Department", typeof(string));
	untypedTable.Rows.Add("Human Resources");
	untypedTable.Rows.Add("Sales");
	untypedTable.Rows.Add("Management");


	// Instantiate the strongly typed dataset
	TypedDataSet tds = new TypedDataSet();
	// Load data from data table to strongly-typed dataset
	tds.Tables[0].Load(new DataTableReader(untypedTable), LoadOption.OverwriteChanges);

	Console.WriteLine("tds.Tables[0] contains {0} rows.", tds.Tables[0].Rows.Count);

	Console.ReadKey();
}

Open in new window

0
tknudsen-qecAuthor Commented:
I'm not too concerned about losing efficiency so much as losing a  learning opportunity.

What really kills me is that this below works perfectly fine.  Note that we are indeed returning an IEnumerable of DSResources objects:

                /* instantiate / populate myTyped as DSResources.DepartmentsTable */
        ...
        ...
        /* Query it */
         IEnumerable<DSResources.DepartmentsRow> results2 =
       (           from d in myTyped.AsEnumerable<DSResources.DepartmentsRow>()           select d        );


Because, from what I understand, we're no longer dealing with anonymous types. I can iterate through results2 just fine using

foreach (DSResources.DepartmentsRow row in  result2s) {...}

But I cannot project (right use of the word?) the results into DSResources.DepartmentsRow    if I'm not querying that type. I cannot take a sequence of anonymous values, type them, and cast the sequence into a DataRow (or DepartmentsRow).
 
 Re. your suggestion above, I think it would help if I was just trying to migrate data from one shape to another, whereas in fact my "real" query will be doing a bit more than that (filtering, joining, grouping, all that happy stuff).
 
0
Todd GerbertIT ConsultantCommented:
Here's two other examples.

DataTable untypedTable = new DataTable();
untypedTable.Columns.Add("Department", typeof(string));
untypedTable.Rows.Add("human resources");
untypedTable.Rows.Add("sales");
untypedTable.Rows.Add("management");

DSResources dsr = new DSResources();

var deptRows = (from dr in untypedTable.AsEnumerable()
               where dr["Department"].ToString().Length < 11
               orderby dr["Department"].ToString()
               select dsr.Departments.AddDepartmentsRow(0, dr["Department"].ToString())).ToList();

// Or... //

DataTable untypedTable = new DataTable();
untypedTable.Columns.Add("Department", typeof(string));
untypedTable.Rows.Add("human resources");
untypedTable.Rows.Add("sales");
untypedTable.Rows.Add("management");

DSResources dsr = new DSResources();

untypedTable.Columns["Department"].ColumnName = "Name";

var deptRows = from dr in untypedTable.AsEnumerable()
               where dr["Name"].ToString().Length < 11
               orderby dr["Name"].ToString()
               select dr;
dsr.Departments.Load(new DataTableReader(deptRows.CopyToDataTable()));

Open in new window

0
tknudsen-qecAuthor Commented:
For a minute there I thought you were a genius.  You got around the datarowbuilder restriction by firing the method that creates the row properly.

Unfortunately, your implementation of DSResources has AddDepartmentsRow as a static method.  The one I have in my DSResources XSD is not static (it requires an object reference)

If I create a DSresources.DepartmentsDataTable object and use that to AddDepartmentsRow, it almost sortof works (It adds rows to both the object and the result set but neither are populated properly)

Don't worry about it, the looping "hack" works fine, and as you said, the deferred execution plan means I'd be looping anyway so its not like I'm hurting efficiency-wise.


IEnumerable<DSResources.DepartmentsRow> deptRows = (from dr in edit.AsEnumerable()
where dr["Department"].ToString().Length < 11
orderby dr["Department"].ToString()
select DSResources.DepartmentsDataTable.AddDepartmentsRow(dr["Department"].ToString())).AsEnumerable<DSResources.DepartmentsRow>();
/* can't compile: no object reference */


DSResources.DepartmentsDataTable DT = new DSResources.DepartmentsDataTable();

IEnumerable<DSResources.DepartmentsRow> deptRows = 
(
  from dr in edit.AsEnumerable()
  select DT.AddDepartmentsRow(dr["Department"].ToString())
)
.AsEnumerable<DSResources.DepartmentsRow>();

/* Compiles but doesn't populate department strings into the expected "Name" field properly. Wonder if I could map it. */

Open in new window

0
tknudsen-qecAuthor Commented:
This works. I had no idea I could do this.

IEnumerable deptRows =
(
   from dr in myTable.AsEnumerable()
 select new    DSResources.DepartmentsDataTable().AddDepartmentsRow(dr.Field("Department"))
);

Of course it only works because I only need the department field in this case, but whatever, it works.  

0
Todd GerbertIT ConsultantCommented:
select new    DSResources.DepartmentsDataTable().AddDepartmentsRow(dr.Field<string>("Department"))
Each iteration of this query, when executed, will result in a new, separate, DepartmentsDataTable with one row in it being created, which is not good for memory consumption.
You want to create an instance of the XSD DataSet (in my example the dataset is named DSResources, and the instance is dsr); You want the LINQ query to populate that instance; you also want the LINQ to execute once and once only, and return a List<T> or Array of DataRows using ToList() or ToArray() (otherwise, everytime you enumerate the query it will be executed and will continue to add rows to the DataSet, if you have a List<T> or Array<T> then you can enum them as many times as you want).
Consider this

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			ArrayList list = new ArrayList();
			int[] ar = { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

			IEnumerable<int> query = from n in ar
									 select list.Add(n);
									 
			// list.Count equals 0
			foreach (int i in query)
				Console.WriteLine(i.ToString());
				
			// list.Count equals 9
			foreach (int i in query)
				Console.WriteLine(i.ToString());

			// list.Count now equals 18, because the first foreach executed the query
			// calling list.Add(n) for each int in the array, then the second foreach
			// RE-executed the query, calling list.Add(n) another 9 times.
			Console.WriteLine("list.Count: " + list.Count);

			Console.ReadKey();
		}
	}
}

Open in new window

0
tknudsen-qecAuthor Commented:
Thanks for the clarification and suggestion.

I wonder how that would differ from your own AddDepartmentsRow suggestion?

Ultimately this was a learning exercise only because for completely unrelated reasons I needed to go back to a loop anyway.

Ironically in my application we're rarely dealing with more than a couple dozen rows for these particular queries.  Efficiency is a joke here; I really just wanted to see how it could be done "properly."
0
Todd GerbertIT ConsultantCommented:
It's an important to understand that the LINQ statement returns a query, and that query is not actually executed until it's items are enumerated, and that the query is re-executed for every subsequent enumeration of it's items.
Below I have a strongly-typed dataset whose class name is DSResources, the instance of that class is the variable dsr.  In the example below I separated the query formation and query execution into two separate steps. When the query is executed it refers to the instance dsr's "Departments" property, and calls that property's AddDepartmentsRow method - that method adds the row to the dsr instance of the DataSet, and also returns the row to the select clause of the LINQ statement - thus it has the effect of adding the rows to the dataset, and returning a List<T> of just the newly added rows (dsr could have already had a 1000 rows in it, but if we use this LINQ statement to add an additional 10 rows then the dataset dsr will contain all 1010 rows, but rows will only contain the 10 just added).
There are no static method references here.

DSResources dsr = new DSResources(); 

var query = from dr in untypedTable.AsEnumerable() 
            where dr["Department"].ToString().Length < 11 
            orderby dr["Department"].ToString() 
            select dsr.Departments.AddDepartmentsRow(0, dr["Department"].ToString()); 
List<DSResources.DepartmentsRow> rows = query.ToList();

foreach (DSResources.DepartmentRow r in rows)
    Console.WriteLine(r.Name);

foreach (DSResources.DepartmentRow r in rows)
    Console.WriteLine(r.Name);

Open in new window

0
Todd GerbertIT ConsultantCommented:
So this:
var query = from dr in untypedTable.AsEnumerable()  
            where dr["Department"].ToString().Length < 11  
            orderby dr["Department"].ToString()  
            select dsr.Departments.AddDepartmentsRow(0, dr["Department"].ToString());  
List<DSResources.DepartmentsRow> rows = query.ToList();

Could be re-written as:
var rows = (from dr in untypedTable.AsEnumerable()  
            where dr["Department"].ToString().Length < 11  
            orderby dr["Department"].ToString()  
            select dsr.Departments.AddDepartmentsRow(0, dr["Department"].ToString())).ToList();


0
Todd GerbertIT ConsultantCommented:
This method depends on you being able to rename the "Department" column in the DataTable to "Name" so that it matches the column name in the strongly-typed DSResources DataSet.
DSResources dsr = new DSResources(); 
 
untypedTable.Columns["Department"].ColumnName = "Name"; 
 
var deptRows = from dr in untypedTable.AsEnumerable() 
               where dr["Name"].ToString().Length < 11 
               orderby dr["Name"].ToString() 
               select dr; 
dsr.Departments.Load(new DataTableReader(deptRows.CopyToDataTable()));

Open in new window

0
tknudsen-qecAuthor Commented:
I see what you're saying.  I had tried the object-approach earlier (creating my version of your dsr), and I said it wasn't populating correctly;error on my part, it was populating fine.

I abandoned it partly because I decided it was probably poor form (which you seem to deny and I'll trust you on that) and partly because of the column-naming restriction you mentioned; I couldn't comprehend how the columns would possibly map properly.

Valuable learning experience indeed.

You've gone far above and beyond the call of duty completing my education after the fact here.  If there's a moderator that can change the score on this question to an (A) that would be appropriate and appreciated.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.