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

LVL 9
Shahid ThaikaSole ProprietorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

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

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

_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

_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

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

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.

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

_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

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!
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
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
.NET Programming

From novice to tech pro — start learning today.