C# Dynamic Linq/Queries

Question

UPDATE2 I started playing more with James suggestion of using reflection and got something working that will return a property value based on a string variable. I don't want to put this as an answer just yet though as I feel it is probably not the best solution. Here is the code:

DataContext dataBase = new DataContext();
List<string> listOfFields = new List<string>;
List<string> listOfUsers = new List<string>


//strFields and strNames are strings generated from listOfFields and listOfUsers
IEnumerable<myUserData> userInfo = dataBase.ExecuteQuery<myUserData>
                       ("select " + strFields + " from myUserData where user_id                          in (" + strNames + ")");    

foreach(var user in userInfo)
{
    foreach(string field in listOfFields)
    {
        string fieldValue = user.GetType().GetProperty(field).GetValue(user,null).ToString
        Console.WriteLine(fieldValue);
    }

}

---

UPDATE: I am making progress! I think. This could be a very terrible solution and resource hog, I'm not sure yet, but it's better than the blank screens I've been getting. The issue now is finding a way to just return the values and not the field name with it. Here is my code:

foreach(string userID in listOfUserIDs)
{
    //dataBase is a Datacontext
    var userInfo = dataBase.myTable
                   .Where("user_id == @0", userID)
                   .Select("New(" + strFields + ")");

    foreach(var user in userInfo)
    {
         Console.WriteLine(user);
    }
}

This outputs {first_name = JOHN, last_name = DOE} etc whatever other fields. I'm hoping I can find a way to modify my Select clause to just select the values.

---

I am a novice programmer and especially new to C# and anything that invovles databases.This is my first project working with queries and databases and I'm running into problems with successfully returning results based on a dynamic query.

I've successfully created a query that will retrieve the correct information, but I am stumped on how to access the specific fields that were retrieved.

IEnumerable<myTable> userInfo = dataBase.ExecuteQuery<myTable>
("select " + Fields + " from myTable where userID in                 
(" + userNames + ")");

EDIT: Excuse my bad naming conventions but myTable is of type: System.Collections.Generic.IEnumerable<DatabaseConnection.myTable>{System.Data.Linq.SqlClient.SqlProvider.OneTimeEnumerable<DatabaseConnection.myTable>} This is just a place holder name and has a specific name in my Database. It was inside of a Tables folder XD

myTable is of type class DatabaseConnection. Fields is a string of fields I am looking up, and userNames is a string of several user names each seperated by a comma. Examples: Fields = "firstName, lastName, idNumber" userNames = "John, Jane, Bob"

My question is, is it possible to iterate through each of the fields for each user in the object type I'm currently returning? I've only seen it done explicitly like this:

    foreach(var x in userinfo)
{
        Console.Writeline(x.firstName);
}

In my current situation this will not work, since the user may want other fields pertaining to specific usernames.

I've also tried using the Dynamic Linq Library and while I was able to successfully set up my select string, I'm not sure how to correctly go about setting up my where clause when the userID will equal several different strings. (the number of names will not always be the same)

var dataInfo = dataBase.myTable
                       .Where("userID == @0", "(" + userIDs + ")")
                       .Select("New(" + fields + ")");

I've been stuck on this for days now. Any help would be greatly appreciated. Maybe I am thinking the solution is too simple to this? I feel like I should be able to use GetProperties() or something similiar in my first bit of code.

I am also very sorry if this has been answered and my poor formatting(first post here). During my research I have not been able to find what I'm looking for, or possibly I am just not understanding the code correctly. Thanks again for any help!

EDIT: All of this is for an Excel Addin I am currently working on. I will need to get the information into a List or some form of object that will allow me to place each field result into a specific cell.

EDIT: Excuse me if I'm using the wrong terminology. I feel like my mistake may be in my Enumerable type and naming it "myTable" could be confusing. It is a table pulled out from my Server Explorer in my .dbml file in the project.

EDIT: How terribly inefficient/time consuming is it to run a query on each individual user and retrieve each requested field for that one username? I'm going to feel silly if this is something I just needed to add a simple loop to.

Answer 1

If I understand your problem correctly and you want to just iterate over all the properties of the object (without knowing what they are at compile time), you could use Reflection e.g.

foreach (var prop in userInfo.GetType().GetProperties())
{
    Console.WriteLine("Property name: " + prop.Name);
    Console.WriteLine("Property value: " + prop.GetValue(userInfo, null));
}

Answer 2

Since it appears you're just using LINQ to SQL, then just use LINQ to query the data. I am going to assume that you've created a DataContext model and that MyTable is an element in your model contains some sort of user information.

using (MyDataContext db = new MyDataContext()) {

    var results = (from m in db.MyTable
                   where m.UserId == userId
                   select m);

    // Loop over all of the rows returned by the previous query and do something with it
    foreach (var m in results) {
        // m represents a single row in the results, do something with it.
        Console.WriteLine(m.UserId);
    }

}

Alternatively, if you are expecting the query to return one row only then you can just use Single or SingleOrDefault to project the results into a single object, rather than a collection of results:

using (MyDataContext db = new MyDataContext()) {

    var singleResult = (from m in db.MyTable
                        where m.UserId == userId
                        select m).SingleOrDefault();

    // Do something with the single result
    if (singleResult != null) {
        Console.WriteLine(singleResult.UserId);
    }

}

Edit:

The previous examples handle the basics on how you query a compatible data source using LINQ. Most LINQ providers (such as LINQ to SQL like you are using) support the IQueryable interface. The short explanation is that this interface allows you to dynamically build up queries, and defers the actual execution of that query until the last possible minute when you are actually trying to access the results. Objects returned by a LINQ query (such as the variable results in the first example) return an IQueryable, where T is the type of object you've queried. It's not exactly a collection in the same sense as you might think of a List<T>, where the elements already exist or are explicitly added or removed. In the first example, results doesn't actually have any elements in it until the foreach loop attempts to iterate over the results.

To handle your filtering scenario, let's continue to build out the results query until it meets your needs, and then we'll execute it when it's good and ready.

First lets assume that your form has three fields you want to filter by: First Name, Last Name, and User ID. To keep it simple, these fields are just TextBoxes and we know to filter by a field if there is any input at all in its corresponding TextBox. Let's also assume that no validation is required, and the user will always enter the correct data into the filter.

using (MyDataContext db = new MyDataContext()) {

    // Build out your base query, which would return everything from MyTable
    var results = (from m in db.MyTable
                   select m);

    // Check each textbox for a value to filter by.  Add the filter to the base
    // query where necessary.    

    if(!string.IsNullOrEmpty(txtUserId.Text)) {
        results = results.Where(x => x.UserId == (int) txtUserId.Text);
    }

    if(!string.IsNullOrEmpty(txtFirstName.Text)) {
        results = results.Where(x => x.FirstName == txtFirstName.Text);
    }

    if(!string.IsNullOrEmpty(txtLastName.Text)) {
        results = results.Where(x => x.LastName == txtLastName.Text);
    }

    // Loop over all of the rows returned by the previous query and do something with it.  
    // THIS is the actual point that the SQL would be generated from the query you've 
    // built and executed against the DB.  The elements returned by `results` 
    // should only meet all of the filters that you the user entered for one or
    // more of the fields.  Not entering any data would have resulted in all rows being
    // returned
    foreach (var m in results) {
        // m represents a single row in the results, do something with it.
        Console.WriteLine(m.UserId);
    }

}

The above example is pretty simple, but it represents the typical filtering scenario where you would want to AND a certain number of fields together. I'm sure there are more elegant way to encapsulate building out the query filter, but the core concept will still be the same. Simply use the IQueryable<T> interface and chain your filter expressions on to it.

Doing this only allows you to chain together predicates using AND, and you can't do something like "Where a user has an ID of 6 OR has a first name of 'John'". You can build more complex expressions like these using the System.Linq.Expressions namespace to build out LINQ expressions, but those are relatively complex and not necessary in most scenarios.

Answer 3

Though i haven't tried DataContext (or .NET 4.5 at all), in 4.0 you can use the dynamic class to access fields directly:

IEnumerable<dynamic> userInfo = (from user in users
                                 where user.id equals userId
                                 select new { user.name, user.id /*, ...*/ }).ToList();

foreach (dynamic user in userInfo)
{
    Console.WriteLine(user.name);
    Console.WriteLine(user.id);
    //...
}

dynamic represents a class which is resolved at runtime, so even if, when coding, you can't see intellisense showing the fields, as long as they are correct, it will work.

Answer 4

Following your update 2:

You can probably simplify the nested foreach into some linq. Something like:

        PropertyInfo[] props = typeof(MyUserData).GetProperties();

        var results = from info in userInfo
                      from p in props
                      where listOfFields.Contains(p.Name)
                      select new
                      {
                          FieldName= p.Name,
                          UserId= info.UserId,
                          FieldValue= p.GetValue(info, null)
                      };

        foreach (var item in results)
        {
            Console.WriteLine("(userId = {0}) {1} = {2}", item.UserId, item.FieldName, item.FieldValue);
        }