dynamic LINQ string... c# part 2

In an attempt to create a dynamic LINQ string, to replace field names with fieldName dynamically set by:
string filedName = lb.ID.Substring(3);

so far I have the following code with 3 errors:

1      'System.Func<System.Data.Linq.Table<Search>,string>' is a 'type' but is used like a 'variable' at line 12:
Func<Table<Search>, string> getField = Expression.Lambda < Func<Table<Search>, string>(Expression.Property(o, fieldName), o);

3.      Argument 1: cannot convert from 'Search' to 'System.Data.Linq.Table<Search>' at line 15:
orderby getField(output)

2.      Delegate 'System.Func<System.Data.Linq.Table<Search>,string>' has some invalid arguments. also at line 15:
orderby getField(output)      
   private static object GetPropertyValue(object obj, string property)
    {
        System.Reflection.PropertyInfo propertyInfo = obj.GetType().GetProperty(property);
        return (string)propertyInfo.GetValue(obj, null);
    }
    private void LoadListBoxes(ListBox lb)
    {
           lb.Items.Clear();
           string fieldName = lb.ID.Substring(3);

           ParameterExpression o = Expression.Parameter(typeof(Table<Search>));
           Func<Table<Search>, string> getField = Expression.Lambda < Func<Table<Search>, string>(Expression.Property(o, fieldName), o);

ListItem[] outputs = (from output in wscgsContext.Searches
                      orderby getField(output)
                      group output by getField(output) into theGroup
                      select new ListItem(getField(theGroup.FirstOrDefault())).ToArray());
           lb.Items.AddRange(outputs);

Open in new window

     
FYI: Using F12 on Searches takes me to:
public System.Data.Linq.Table<Search> Searches
So, in my code above I have used Table<Search> as type.

Also, holding cursor on 'Searches' shows me: Table <Search> WSCGSoftwareDataContext.Searches
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

louisfrCommented:
Table<Search> is the table. The items should be of type Search.
ParameterExpression o = Expression.Parameter(typeof(Search));
Func<Search, string> getField = Expression.Lambda < Func<Search, string>(Expression.Property(o, fieldName), o);

Open in new window

0
ste5anSenior DeveloperCommented:
What is a "dynamic LINQ string"?
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
louisfr,

I got the following two errors:

Error      1      'System.Func<Search,string>' is a 'type' but is used like a 'variable'       at line 2:
Func<Search, string> getField = Expression.Lambda < Func<Search, string>(Expression.Property(o, fieldName), o);

Error      2      'System.Web.UI.WebControls.ListItem' does not contain a definition for 'ToArray' and no extension method 'ToArray' accepting a first argument of type 'System.Web.UI.WebControls.ListItem' could be found (are you missing a using directive or an assembly reference?) at line 7:
select new ListItem(getField(theGroup.FirstOrDefault())).ToArray());

           ParameterExpression o = Expression.Parameter(typeof(Search));
           Func<Search, string> getField = Expression.Lambda < Func<Search, string>(Expression.Property(o, fieldName), o);

ListItem[] outputs = (from output in wscgsContext.Searches
                      orderby getField(output)
                      group output by getField(output) into theGroup
                      select new ListItem(getField(theGroup.FirstOrDefault())).ToArray());
           lb.Items.AddRange(outputs);

Open in new window

-------------------------
Ste5an,
The following worked to handle field named City. This is what I started with but it has been evolved the code I have in my original question.
           ListItem[] outputs4 = (from output in wscgsContext.Searches
                                  orderby output.City
                                  group output by output.City into theGroup
                                  select new ListItem(theGroup.FirstOrDefault().City)).ToArray();
           lb.Items.AddRange(outputs4);

Open in new window

re:> What is a "dynamic LINQ string"?
Take this code (which handles specifically field City) and make it work for any field name created by:
 string fieldName = lb.ID.Substring(3);
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

louisfrCommented:
There's a missing > and a call to compile to create the delegate from the expression.
ParameterExpression o = Expression.Parameter(typeof(Search));
Func<Search, string> getField = Expression.Lambda<Func<Search, string>>(Expression.Property(o, fieldName), o).Compile();

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
louisfr,

This fixed one error and revised the second error stating:
Error      1      'System.Web.UI.WebControls.ListItem' does not contain a definition for 'ToArray' and no extension method 'ToArray' accepting a first argument of type 'System.Web.UI.WebControls.ListItem' could be found (are you missing a using directive or an assembly reference?)

at:

select  new ListItem(getField(theGroup.FirstOrDefault())).ToArray());
0
louisfrCommented:
Check the parentheses. There is one too much after ToArray() that should be before it.
select  new ListItem(getField(theGroup.FirstOrDefault()))).ToArray();

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
runtime error at the end of this line:

select  new ListItem(getField(theGroup.FirstOrDefault()))).ToArray();

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
0
louisfrCommented:
That means we can't use delegates. And we cannot use reflection and GetProperty. We have to use expressions without compiling them. Easy for the orderby and group. Let's see...
ParameterExpression o = Expression.Parameter(typeof(Table<Search>));
           var getField = Expression.Lambda<Func<Search, string>>(Expression.Property(o, fieldName), o);
ListItem[] outputs = (wscgsContext.Searches
                     .OrderBy(getField)
                     .GroupBy(getField)
                     .Select(theGroup=>theGroup.FirstOrDefault())
                     .Select(getField)
                     .Select(field => new ListItem(field))).ToArray();

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
1. I had to add ) in the line below:
.Select(field => new ListItem(field))).ToArray();

2. Runtime error: Instance property 'First_Name' is not defined for type 'System.Data.Linq.Table`1[Search]' at the end of line.
 var getField = Expression.Lambda<Func<Search, string>>(Expression.Property(o, fieldName), o);
0
louisfrCommented:
You tried to use the expression with a field name which isn't in the Search table.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
louisfr,

Basically, I am looking for a solution that woks. The link I gave you (http://tomasp.net/blog/dynamic-linq-queries.aspx/), seems to suggest this is possible.

I am starting to do its exercises to eventually make this happen. I will post the result here later.

Wish me luck,

Mike
0
louisfrCommented:
Is there a "First_Name" property in the "Search" table?
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Yes, it is the first one.

        Dictionary<string, bool> listBoxes =   new Dictionary<string, bool>();
        listBoxes.Add("First_Name", true);
        listBoxes.Add("Last_Name", true);
        listBoxes.Add("Address", true);
        listBoxes.Add("City", true);
        listBoxes.Add("State", true);
        listBoxes.Add("Zip_Code", true);
        listBoxes.Add("Phone", true);
        listBoxes.Add("Email", true);
        listBoxes.Add("Order_Date", true);
        listBoxes.Add("Title", true);
        listBoxes.Add("Unit_Price", true);
        listBoxes.Add("Quantity", true);

BTW, any help at http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_28562832.html will be also appreciated.

Mike
0
louisfrCommented:
My bad. A copy&paste went wrong. The expression parameter type should be Search, not Table<Search>.
ParameterExpression o = Expression.Parameter(typeof(Search));
           var getField = Expression.Lambda<Func<Search, string>>(Expression.Property(o, fieldName), o);
ListItem[] outputs = (wscgsContext.Searches
                     .OrderBy(getField)
                     .GroupBy(getField)
                     .Select(theGroup=>theGroup.FirstOrDefault())
                     .Select(getField)
                     .Select(field => new ListItem(field))).ToArray();

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Amazing solution. Thank you very much.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.