Solved

dynamic LINQ string... c# part 2

Posted on 2014-11-17
16
278 Views
Last Modified: 2016-02-15
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
0
Comment
Question by:Mike Eghtebas
  • 8
  • 7
16 Comments
 
LVL 11

Expert Comment

by:louisfr
ID: 40447668
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40447674
What is a "dynamic LINQ string"?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447692
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
 
LVL 11

Expert Comment

by:louisfr
ID: 40447722
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447736
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
 
LVL 11

Expert Comment

by:louisfr
ID: 40447746
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447758
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447783
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 11

Expert Comment

by:louisfr
ID: 40447792
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447805
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
 
LVL 11

Expert Comment

by:louisfr
ID: 40447810
You tried to use the expression with a field name which isn't in the Search table.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447813
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
 
LVL 11

Expert Comment

by:louisfr
ID: 40447816
Is there a "First_Name" property in the "Search" table?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40447821
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
 
LVL 11

Accepted Solution

by:
louisfr earned 500 total points
ID: 40447831
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
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40447992
Amazing solution. Thank you very much.

Mike
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now