[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3507
  • Last Modified:

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
0
Shahid Thaika
Asked:
Shahid Thaika
  • 2
1 Solution
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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