DynamicQuery: How to select a column with linq query that takes parameters

Question

Hello,

We want to set up a directory of all the organizations working with us. They are incredibly diverse (government, embassy, private companies, and organizations depending on them ). So, I've resolved to create 2 tables. Table 1 will treat all the organizations equally, i.e. it'll collect all the basic information (name, address, phone number, etc.). Table 2 will establish the hierarchy among all the organizations. For instance, Program for illiterate adults depends on the National Institute for Social Security which depends on the Labor Ministry.

In the Hierarchy table, each column represents a level. So, for the example above, (i)Labor Ministry - Level1(column1), (ii)National Institute for Social Security - Level2(column2), (iii)Program for illiterate adults - Level3(column3).

To attach an organization to an hierarchy, the user needs to go level by level(i.e. column by column). So, there will be at least 3 situations:

• If an adequate hierarchy exists for an organization(for instance, level1: US Embassy), that organization can be added (For instance, level2: USAID).--> US Embassy/USAID, and so on.
• How about if one or more levels are missing? - then they need to be added
• How about if the hierarchy need to be modified? -- not every thing need to be modified.

I do not have any choice but working by level (i.e. column by column). I does not make sense to have all the levels in one form as the user need to navigate hierarchies to find the right one to attach an organization.

Let's say, I have those queries in my repository (just that you get the idea).

Query1

var orgHierarchy = (from orgH in db.Hierarchy
                   select orgH.Level1).FirstOrDefault;

Query2

var orgHierarchy = (from orgH in db.Hierarchy
                   select orgH.Level2).FirstOrDefault;

Query3, Query4, etc.

The above queries are the same except for the property queried (level1, level2, level3, etc.)

Question: Is there a general way of writing the above queries in one? So that the user can track an hierarchy level by level to attach an organization.

In other words, not knowing in advance which column to query, I still need to be able to do so depending on some conditions. For instance, an organization X depends on Y. Knowing that Y is somewhere on the 3rd level, I'll go to the 4th level, linking X to Y.

I need to select (not manually) a column with only one query that takes parameters.

======================= EDIT

As I just said to @Mark Byers, all I want is just to be able to query a column not knowing in advance which one. Check this out:

How about this

Public Hierarchy GetHierarchy(string name)
{
  var myHierarchy = from hierarc in db.Hierarchy
                    where (hierarc.Level1 == name)
                    select hierarc;
  retuen myHierarchy;  
}

Above, the query depends on name which is a variable. It mighbe Planning Ministry, Embassy, Local Phone, etc.

Can I write the same query, but this time instead of looking to much a value in the DB, I impose my query to select a particular column.

var myVar = from orgH in db.Hierarchy
            where (orgH.Level1 == "Government")
            select orgH.where(level == myVariable);
return myVar;

I don't pretend that select orgH.where(level == myVariable) is even close to be valid. But that is what I want: to be able to select a column depending on a variable (i.e. the value is not known in advance like with name).

Thanks for helping

Answer 1

Your database is not normalized so you should start by changing the heirarchy table to, for example:

OrganizationId Parent
1              NULL
2              1
3              1
4              3

To query this you might need to use recursive queries. This is difficult (but not impossible) using LINQ, so you might instead prefer to create a parameterized stored procedure using a recursive CTE and put the query there.

Answer 2

How about using DynamicQueryable?

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx