How to choose programaticaly the column to be queried by Linq using PropertyInfo???

Question

Hello,

I would like to control how linq querries my database programaticaly. For instance, I'd like to query the column X, column Y, or column Z, depending on some conditions.

First of all, I've created an array of all the properties inside my class called myPropertyInfo.

Type MyType = (typeOf(MyClass));
PropertyInfo[] myPropertyInfo = myType.GetProperties(
BindingFlags.Public|BindingFlags.Instance);

The myPropertyInfo array allows me to access each property details (Name, propertyType, etc) through the index*[i]*

Now, how can I use the above information to control how linq queries my DB?

Here's a sample of a querry I'd like to exploit.

var myVar = from tp in db.MyClass
            select tp.{expression};

Expression using myPropertyInfo[i] to choose which property(column) to query.

I'm not sure if that's the way of doing it, but if there's another way to do so, I'll be glad to learn.

=========

EDIT

I believe the right expression the one used by @Gabe. In fact, I'd like to make queries on the fly. Here's the reason: I've (i) a table "Organizations" (Ministries, Embassies, International Organizations, such as UN, UNPD, UNICEF, World Bank, etc, and services depending on them). I've (ii) an other table "Hierarchy" which represents the way those organizations are linked, starting by which category each one belongs to (Gouvernement, Foreign Missions, private sector, NGO, etc.)

Each column representing a level in the hierarchy, some rows will be longer while other will be shorter. many rows'columns will share the same value (For instance, 2 ministries belonging to the Government, will have "Government" as value for the column 'Level 1').

That's why, for each row (organization), I need to go level by level (i.e. column by column).

Hope I've been enough explicit this time

Thanks for helping.

Answer 1

It sounds like you want to make a Queryable on-the-fly. I haven't tried it, but this might give you a start:

var myVar = 
Queryable.Select(
    db.MyClass, 
    Expression.Property(
        Expression.Parameter(
            typeof(MyClass), // this represents the type of "tp"
            "tp"
        ),
        myPropertyInfo[i]
    )
)

Answer 2

if you're using Entity Framework, not LINQ to SQL, there is wonderful Entity Sql and you can use it as

object DynamicQuery(string fieldName, object fieldValue) {
    string eSql=string.Format("it.{0} = @param", fieldName);
    return db.Where(eSql, fieldValue).FirstOrDefault();
}

hope this helps

MSDN has the following example, you see that you can dynamicly change strings used to access ProductID field, and as far as i remember event rename it.

using (AdventureWorksEntities advWorksContext =
        new AdventureWorksEntities())
{
    try
    {
        // Use the Select method to define the projection.
        ObjectQuery<DbDataRecord> query =
            advWorksContext.Product.Select("it.ProductID, it.Name");

        // Iterate through the collection of data rows.
        foreach (DbDataRecord rec in query)
        {
            Console.WriteLine("ID {0}; Name {1}", rec[0], rec[1]);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Also you can even do the following (again from MSDN)

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    string myQuery = @"SELECT p.ProductID, p.Name FROM 
        AdventureWorksEntities.Product as p";
    try
    {
        foreach (DbDataRecord rec in
            new ObjectQuery<DbDataRecord>(myQuery, advWorksContext))
        {
            Console.WriteLine("ID {0}; Name {1}", rec[0], rec[1]);
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}