Select dynamic fields/columns in Linq, including a Sum field

I working with MS Dynamics CRM SDK and WPF. I need to pull data that is returned as XML and pass that to a WPF Chart.

What I have done so far is to have the user choose a list of columns and action (Sum/Count) he wants reported, and I am saving it in a DataTable. For every row in the DataTable, I need to retrieve an appropriate field/column from the results.xml file.

I have chosen to use Linq and my project works when I hard code the query at design time. However, what I really need is to build/specify the fields/columns of the Linq query at run time. If I can figure this out, this part of the project is complete. I have gone through some generic examples for Dynamic Linq queries on the internet, but I can't seem to fit it in my project.

If you feel I can solve my problem without using Linq, then I'd be interested in that as well. I have also attached my current working code to give you an idea of what I am doing.
private void cmdReport_Click(object sender, RoutedEventArgs e)
        {
            int ix = 0;
            //MyCRMDashboard.crmService.Fetch returns results.xml file data in string format
            //Conver .fetch() data in XDoc (Linq)
            string strResults = MyCRMDashboard.crmService.Fetch(fetchXml);
            StringReader sr = new StringReader(strResults);
            XmlTextReader xtr = new XmlTextReader(sr);
            XDocument doc = XDocument.Load(xtr);

            try
            {
                 //The below query returns group names and sum of quantity
                //tChartList is a DataTable that contains rows for every field I need to have in the Linq query
                //Since this is hard coded, I only included the first row... index 0
                var query = from result in doc.Root.Descendants("result")
                            group result by (string)result.Element(((KeyValuePair<string, string>)cmbAttribute.SelectedItem).Key).Attribute("name").Value into g
                            select new
                            {
                                Group = g.Key,
                                Field = g.Sum(i => (decimal)i.Element(tChartList.Rows[0]["attribute"].ToString()))
                            };
                 
                //YOU DON'T NEED TO SEE ANY FURTHER
                //Below will work irrespective of the above coding

                //ArrayList for adding Column Series to the chart
                //For each field, create an Array list to save data later
                ix = tChartList.Rows.Count;
                ArrayList[] aSeries = new ArrayList[ix];
                for (int i = 0; i < ix; i++)
                {
                    aSeries[i] = new ArrayList();
                }
                


                
                foreach (var result in query)
                {
                    //for each group and series, populate the value
                    for (int i = 0; i < ix; i++)
                    {
                        aSeries[i].Add(new KeyValuePair<string, decimal>(result.Group, result.Field));
                    }
                    
                }

                //Add each series to the chart
                for (int i = 0; i < ix; i++)
                {
                    ColumnSeries cs = new ColumnSeries();
                    cs.Title = tChartList.Rows[i]["attribute"].ToString();
                    cs.IndependentValueBinding = new Binding("Key");
                    cs.DependentValueBinding = new Binding("Value");
                    cs.ItemsSource = aSeries[i].ToArray(typeof(KeyValuePair<string, decimal>));
                    chtAFR.Series.Add(cs);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Open in new window

fetch.xml
results.xml
LVL 9
Shahid ThaikaSole ProprietorAsked:
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.

SriVaddadiCommented:
You can provide variable as <%=VariableName%>

Lets say fieldName is your variable which holds "results"

give <%=fieldName%> instead of hard coded "results" and try
0
Shahid ThaikaSole ProprietorAuthor Commented:
I am not sure what you mean, but the number of columns I want to retrieve is dynamic. Some times, I will retrieve 2 columns, other times 3 or 4. Also, I may need to Sum certain fields and count the others.
0
Shahid ThaikaSole ProprietorAuthor Commented:
OK, I had done a workaround much earlier, but was hoping someone would give a better solution... apparently not. Since I know how to retrieve a single column, I moved the series adding part to a seperate sub routine and for each row in the data table, I called the AddSeries() sub-routine... job done!
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
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.