Getting multiple columns using LINQ

So I have a couple questions here:

1. Are you only expecting to get one row back?  Is there ever a time you will get more than 1 row for an EmployeeID?
2. If having the result in a string list that essential?  I see you have a list as Entered, but only the last 2 values are strings, while the first is an int.
3. Are you trying to compare the lists as one?  Or were you planning on comparing them at each element?

So, from a linq syntax, as long as you Rows table has the correct information, this syntax should work.

var foundEmp = from dr in dt.Rows
		where dr.EmployeeId == "3"
		select new
		{
			dr.EmployeeId,
			dr.FirstName,
			dr.LastName
		};

Select allOpen in new window

Please note that this hard-coded the employee id into the query, it should actually be a variable that you assign, but it gets the point across.


Then you can compare the values by doing something like this:

if(foundEmp.EmployeeId == "SomeValue")...

Select allOpen in new window

That should help I hope.  If not I would need the previosu questions answered.

Hi Randy

My answers are below:
1. Are you only expecting to get one row back?  Is there ever a time you will get more than 1 row for an EmployeeID?
--> Yes. EmployeeID should be unique, so it returns only one row.

2. If having the result in a string list that essential?  I see you have a list as Entered, but only the last 2 values are strings, while the first is an int.
--> it's not essential to have string type of list. My goal is to compare entered data with existing data in sql server. If I can compare two lists like 'Entered.SequenceEqual(DBList)' and find out if it's true or not. I thought that string would be the easiest way to do that. If you have better way, please let me know.

3. Are you trying to compare the lists as one?  Or were you planning on comparing them at each element?
--> As I answered above, I just want to make sure that both lists have the same contents. so I guess that it shouldn't be matter. but if possible, I want to know both ways.

Thanks!

Also in your code,
dt.Rows in the first row gives me an error :

could not find an implementation of the query pattern for source type, 'DataRowCollection'
'Where' not found. Consider explicitly specifying the type of the range variable 'dr'

Hi Issabell;

I had to make some assumptions because you state, "I added a new employee to SQL table., Add(3, "Issy", "Holm");, And I added this to the List.", if you notice you have added a number 3 to the database. In the List<String> you added a numeric value 3 which is not valid, wrong type and in your query you compare the number 3 with the EmployeeId. So I do not know if it was to be a character 3 or number 3 and seeming you have a List<String> I assumed it was a character 3. So if that was not correct this code will have issues.

How is EmployeeId defined in the database a String or Integer data type?

List<string> Entered = new List<string>(){"3", "Issy", "Holm"};

List<String> DBList = (from DataRow dr in dt.Rows
                       where dr.Field<String>("EmployeeId") == "3"
                       from col in dr.ItemArray
                       select col.ToString()).ToList ( );

var areTheSame = Entered.SequenceEqual(DBList);

Select allOpen in new window

Hi Fernando,

Add() is a separate function in which 3 is an integer.
I then manually added the values to List<string>.

Also dt.field in 4th line gives me an error, saying " 'Data Table' does not contain a definition for 'Field' and best extension method overload 'DataRowExtensions.Field<string>(DataRow, string)' requires a receiver of type 'DataRow'

It is dr.Field<...> and not dt.Field<...>. Also because it is a int in db the line should be,

where dr.Field<Int>("EmployeeId") == 3

Select allOpen in new window

thanks Fernado!!!

Hi Fernando,

Your code puts all the values into that row. However, I need only 3 columns to be in the list
Those are EmployeeId, FirstName, and LastName column in the sql table.
The table itself includes more that these 3 columns, so these two lists will be always different if I use your code. Can I extract particular columns using your code?

Thanks Fernando,

This works beautifully except one thing.

This doesn't return the result in the order of index I specified in indexToReturn.

Linst<int> indexToReturn = new List<int.{1, 3, 2};

With this indexToReturn, DBList returns in the order of {1,2,3}, not of {1,3,2}

That is not what indexToReturn is doing. indexToReturn is filtering out those columns that are not to be returned in the results.

so when I was looping through DBList, I am getting EmployeeId, LastName,FirstName since it follows the order of the actual table.
So, areTheSame will always be false. And this is expected in this code I guess.

any idea how to fix this last issue?
Any advice would be appreciated.

I think that's fine since I can manually enter the values for Entered.

Thanks for all you help Fernando!

You are the BEST!

Not a problem Issabell, glad to help.