LINQ variable to list of string without using column names?

Question

In an C# ASP.Net MVC project, I'm trying to make a List<string> from a LINQ variable.

Now this might be a pretty basic thing, but I just cannot get that to work without using the actual column names for the data in that variable. The thing is that in the interests of trying to make the program as dynamic as possible, I'm leaving it up to a stored procedure to get the data out. There can be any amount of any which way named columns depending on where the data is fetched from. All I care about is taking all of their values into a List<string>, so that I can compare user-input values with them in program.

Pointing to the columns by their names in the code means I'd have to make dozens of overloaded methods that all just basically do the same thing. Below is false non-functioning code. But it should open up the idea of what I mean.

// call for stored procedure
var courses = db.spFetchCourseInformation().ToList();

// if the data fails a check on a single row, it will not pass the check
bool passed = true;

foreach (var i in courses)
{
   // each row should be cast into a list of string, which can then be validated
   // on a row-by-row basis
   List courseRow = new List();
   courseRow = courses[i]; // yes, obviously this is wrong syntax
   int matches = 0;
   foreach (string k in courseRow)
   {
      if (validator.checkMatch(courseRow[k].ToString()))
      {
         matches++;
      }                    
   }
   if (matches == 0)
   {
      passed = false;
      break;
   }
}

Now below is an example of how I currently have to do it because I need to use the names for the columns

for (int i = 0; i < courses.Count; i++)
{
   int matches = 0;

   if (validator.checkMatch(courses[i].Name))
      matches++;

   if (validator.checkMatch(courses[i].RandomOtherColumn))
      matches++;

   if (validator.checkMatch(courses[i].RandomThirdColumn))
      matches++;

   if (validator.checkMatch(courses[i].RandomFourthColumn))
      matches++;

   /* etc...

      * etc...
      * you get the point
      * and one of these for each and every possible variation from the stored procedure, NOT good practice
      * */

Thanks for help!

Answer 1

I'm not 100% sure what problem you are trying to solve (matching user data to a particular record in the DB?), but I'm pretty sure you're going about this in slightly the wrong fashion by putting the data in a List. I

t should be possible to get your user input in an IDictionary with the key being used for the column name, and the object as the input data field.

Then when you get the data from the SP, you can get the data back in a DataReader (a la http://msmvps.com/blogs/deborahk/archive/2009/07/09/dal-access-a-datareader-using-a-stored-procedure.aspx).

DataReaders are indexed on column name, so if you run through the keys in the input data IDictionary, you can check the DataReader to see if it has matching data.

using (SqlDataReader reader = Dac.ExecuteDataReader("CustomerRetrieveAll", null))  
{ 
   while (reader.Read()) 
   { 
      foreach(var key in userInputDictionary.AllKeys)
      {
        var data = reader[key];
        if (data != userInputDictionary[key]) continue;
      }
   } 
}

Still not sure about the problem you are solving but, I hope this helps!

Answer 2

A little creative reflection should do the trick.

var courses = db.spFetchCourseInformation()
var values = courses.SelectMany(c => c.GetType().GetProperties()  // gets the properties for your object
                    .Select(property => property.GetValue(c, null))); // gets the value of each property
List<string> stringValues = new List<string>(
                values.Select(v => v == null ? string.Empty : v.ToString()) // some of those values will likely be null
                .Distinct()); // remove duplicates

Answer 3

Same user, different name now that I registered. Can't comment or edit so I'll put my reply here.

@mcintyre321: The problem I'm trying to solve is that the project is created to be as dynamic as possible with future updates in mind. Currently the stored procedure accesses one table. In the future, the procedure will - depending on the type of user passed into it as parameter -, fetch the data from different tables entirely.

But regardless of which table is used in the procedure, the end goal for the code remains the same: to see if any of the rows have a value matching the value in the validator.

I'll get back to testing your answers later, couldn't get the desired results with a quick try but that doesn't say anythign yet. :) Thanks for the replies!