• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1789
  • Last Modified:

Need help with Dynamic Linq Query

I want my users to calculate sum on their preferred field. In the code below 'var q3' is the output of a Linq query where I have queried all possible fields with grouping. These fields are Field1, Field2, ...Field10. Now in the new Linq query - "query", I want to dynamically retrieve the sum of one of the fields of my choice. This can be any of the 10 data fields in the format "Field00'. How can I do this?

If I hardcode the field name, then it works, but I want to choose a field dynamically at run time. Thus the commented out line works, but I may want to choose the other fields.

Based on some examples on the internet for related purpose, I saw a Func<> expression. However, for me it returns an error... "The type arguments for method 'System.Linq.Enumerable.Sum<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int>)' cannot be inferred from the usage. Try specifying the type arguments explicitly."

I am also using Dynamic Linq Queries, if that helps. Maybe I can do something like...
var query = q3.AsQueryable().Sum("Field1");
Func<string, decimal> dExpr = new Func<string, decimal>(f => Convert.ToDecimal("Field1"));
var query = from result in q3
            select new
            {
                GroupField = result.Key,
                //DataField = result.Sum(f => Convert.ToDecimal(f.Field1))
                DataField = result.Sum(dExpr)
            };

Open in new window

0
Shahid Thaika
Asked:
Shahid Thaika
  • 6
  • 6
2 Solutions
 
_Katka_Commented:
Hi, can't you just retrieve a field with reflection first, and then retrieve a value.

Option A:

FieldInfo field = f.GetType().GetField(string.format("field{0}", fieldNumber), BindingFlags.Public | BindingFlags.Instance));

var query = from result in q3
            select new
            {
                GroupField = result.Key,
                DataField = result.Sum(f => Convert.ToDecimal(field.GetValue(f)))
                DataField = result.Sum(dExpr)
            };

Option B:

Just do a switch statement and retrieve the value beforehand.

regards,
Kate
0
 
Shahid ThaikaSole ProprietorAuthor Commented:
Unfortunately the lamba expression and "f" only work within the Linq query. Is there another way to set the FieldInfo field variable?
0
 
_Katka_Commented:
Oh, but you know the type beforehand ? Let me know if it so, if it is:

1) you know the type beforehand, just cache it

FieldInfo field = typeof(myTypeContainingTheFields).GetField(string.format("field{0}", fieldNumber), BindingFlags.Public | BindingFlags.Instance));

2) it is different every time

<see code>

regards,
Kate
var query = from result in q3
    select new
    {
        GroupField = result.Key,
        DataField = result.Sum(f => 
            Convert.ToDecimal(
                f.GetType().
                  GetField(string.format("field{0}", fieldNumber), BindingFlags.Public | BindingFlags.Instance)).
                  GetValue(f)))
        DataField = result.Sum(dExpr)
   };

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Shahid ThaikaSole ProprietorAuthor Commented:
Hi - I think there may be some errors with your brackets. I got the following code with some trial and error, but although the code compiles and runs at runtime, that field returns a null value.

I added the following to the Watch in debugging mode...
f.GetType().GetFields()

The value returned was...
{System.Reflection.FieldInfo[0]}

Hence I guess it didn't find the required field. Although when I simply add 'f' to the watch, it shows me a tree with all the sub-fields.
                    var query = from result in q3
                                select new
                                {
                                    GroupField = result.Key,
                                    DataField = result.Sum(f => 
                                        Convert.ToDecimal(
                                            f.GetType().GetField("Field1").GetValue(f.GetType().GetField("Field1"))
                                            ))
                                    //DataField = result.Sum(f => Convert.ToDecimal(f.Field1))
                                    //DataField = result.Sum(dExpr)
                                };

Open in new window

0
 
_Katka_Commented:
Hi, you skipped the BindingFlags which are needed, try to broaden them, also you cannot use field in the get GetValue(). Try the code below, I've corrected brackets and also broaden the search for a field. It would be much easier to guess, if you've posted the code of your class. The correct binding flags are vital to retrieve a field. If your field is public use BindingFlags.Public if they're not public (private,..) use BindingFlags.NonPublic. I can't know that.

regards,
Kate


var query = from result in q3
    select new
    {
        GroupField = result.Key,
        DataField = result.Sum(f => 
            Convert.ToDecimal(
                f.GetType().
                    GetField(string.format("field{0}", fieldNumber), 
                      BindingFlags.Public | 
                      BindingFlags.Instance |
                      BindingFlags.NonPublic |
                      BindingFlags.GetField).
                    GetValue(f)))
   };

Open in new window

0
 
_Katka_Commented:
Or without a format parameter, to test it first.

regards,
Kate
var query = from result in q3
    select new
    {
        GroupField = result.Key,
        DataField = result.Sum(f => 
            Convert.ToDecimal(
                f.GetType().
                    GetField("field1", 
                      BindingFlags.Public | 
                      BindingFlags.Instance |
                      BindingFlags.NonPublic |
                      BindingFlags.GetField).
                    GetValue(f)))
   };

Open in new window

0
 
Shahid ThaikaSole ProprietorAuthor Commented:
Nope sorry I couldn't get it to work right. How can I get the correct firsthand? Using watch?

Also I posted my entire code for more info...

Also please remember the original, I just need to be able to Sum a chosen field. If I give something like the below, I get an error: No applicable aggregate method 'Sum' exists

var query = q3.AsQueryable().Select("new(Key AS GroupField, Sum(Field1) AS DataField)");


Although Count() works...

var query1 = q3.AsQueryable().Select("new(Key AS GroupField, Count() AS DataField)");
            var q1 = from result in doc.Root.Descendants("result")
                     select new
                     {
                         //For potential grouping
                         GroupField = (cmbGroupBy.SelectedIndex >= 0) ? result.Element(((KeyValuePair<string, string>)cmbGroupBy.SelectedItem).Key).Attribute("name").Value : ((KeyValuePair<string, string>)cmbGroupBy.SelectedItem).Value,

                         //Potential Fields... up to 10
                         Field1 = (tFieldList.Rows.Count > 0) ? result.Element(tFieldList.Rows[0]["attribute"].ToString()).Value : "",
                         Field2 = (tFieldList.Rows.Count > 1) ? result.Element(tFieldList.Rows[1]["attribute"].ToString()).Value : "",
                         Field3 = (tFieldList.Rows.Count > 2) ? result.Element(tFieldList.Rows[2]["attribute"].ToString()).Value : "",
                         Field4 = (tFieldList.Rows.Count > 3) ? result.Element(tFieldList.Rows[3]["attribute"].ToString()).Value : "",
                         Field5 = (tFieldList.Rows.Count > 4) ? result.Element(tFieldList.Rows[4]["attribute"].ToString()).Value : "",
                         Field6 = (tFieldList.Rows.Count > 5) ? result.Element(tFieldList.Rows[5]["attribute"].ToString()).Value : "",
                         Field7 = (tFieldList.Rows.Count > 6) ? result.Element(tFieldList.Rows[6]["attribute"].ToString()).Value : "",
                         Field8 = (tFieldList.Rows.Count > 7) ? result.Element(tFieldList.Rows[7]["attribute"].ToString()).Value : "",
                         Field9 = (tFieldList.Rows.Count > 8) ? result.Element(tFieldList.Rows[8]["attribute"].ToString()).Value : "",
                         Field10 = (tFieldList.Rows.Count > 9) ? result.Element(tFieldList.Rows[9]["attribute"].ToString()).Value : ""
                     };



var q3 = from result in q1 group result by result.GroupField into myGroup select myGroup;



                    var query = from result in q3
                                select new
                                {
                                    GroupField = result.Key,

                                    DataField = result.Sum(f =>
                                        Convert.ToDecimal(
                                            f.GetType().
                                                GetField("Field1",
                                                  BindingFlags.Public |
                                                  BindingFlags.Instance |
                                                  BindingFlags.NonPublic |
                                                  BindingFlags.GetField).
                                                GetValue(f))) 
                                };

Open in new window

0
 
Shahid ThaikaSole ProprietorAuthor Commented:
OK I tried the link below and got a solution working, but am still interested in your solution. Don't feel like not giving you points after your efforts.

http://stackoverflow.com/questions/1465700/system-linq-dynamic-select-new-into-a-listt-or-any-other-enumerable

The statement that eventually worked for me was...

IQueryable<Result> query = q3.AsQueryable().Select<Result>("new (Key as GroupField, Sum(Convert.ToDecimal(Field1)) as DataField)");

where Result is a class...
    public class Result
    {
        public string GroupField { get; set; }
        public decimal DataField { get; set; }
    }



Also another problem things may not have worked was the fact that Field1, Field2, etc. were all String variables. Hence you may notice the Convert.ToDecimal() within my Sum() in my working statement.

Nevertheless, I think your comments helped me analyze the problem and eventually get at the solution.
0
 
_Katka_Commented:
I just changed GetField to GetProperty and it should work now.

regards,
Kate
var query = from result in q3 
            select new
            {
                GroupField = result.Key,
                DataField = result.Sum(f => 
                    Convert.ToDecimal(f.
                        GetType().
                        GetProperty("Field1", // or string.Format("Field{0}", anyFieldNumber)
                            BindingFlags.Public | 
                            BindingFlags.Instance).
                        GetValue(f, null)))
            };

Open in new window

0
 
_Katka_Commented:
I'd also recommend to break this kind of line to two methods, for better code readibility and maintenance.

Field1 = (tFieldList.Rows.Count > 0) ? result.Element(tFieldList.Rows[0]["attribute"].ToString()).Value : "",

to

Field1 = (tFieldList.Rows.Count > 0) ? GetValueAt(result, tFieldList, 0) : string.Empty,

<see methods code>

regards,
Kate
Decimal GetValueAt(XContainer container, YourListType fieldList, Int32 index)
{
    String attributeValue = GetFieldListValueAt(fieldList, index);
    String stringValue = container.Element(attributeValue).Value;
    return Convert.ToDecimal(stringValue);
}

String GetFieldListValueAt(YourListType fieldList, Int32 index)
{
    return fieldList.Rows[index]["attribute"].ToString();
}

Open in new window

0
 
Shahid ThaikaSole ProprietorAuthor Commented:
Thanks for your time, the solution at post ##31000950 worked fine. I have requested this question to be closed by accepting both our solutions. Cheers!
0
 
Shahid ThaikaSole ProprietorAuthor Commented:
Hi Katka, can you please help me with a similar problem here...

http://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_25954068.html
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now