Solved

dynamic LINQ string... c# part 2

Posted on 2014-11-17
16
296 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 33

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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