Q:

Better Database Query to Retrieve Nested Values

I am trying to retrieve some data from my database using LINQ to Entities. (I am ultimately using the data to create a Chart using the new ASP.NET MVC3 ChartHelper.)
As part of this, the user passes in two strings which are the names of the fields he is interested in for the X-Axis and Y-Axis data. As an example, "Cost" vs. "Time". I then have to build up the data set so I can pass it to the ChartHelper. However, retrieving the data seems rather clunky, and I am hoping there is a better way to do it. Here's what I am doing right now. (objectiveX and objectiveY are strings that represent the type name.)
List<double> xValues = new List<double>();
List<double> yValues = new List<double>();

// First get the data needed to build the chart.
foreach (Solution solution in this.Solutions)
{
    IEnumerable<double> xVal = from x in solution.SolutionValues 
                               where x.TypeName == objectiveX 
                               select x.Value;
    IEnumerable<double> yVal = from y in solution.SolutionValues
                               where y.TypeName == objectiveY 
                               select y.Value;

    xValues.AddRange(xVal);
    yValues.AddRange(yVal);
}

Is there a better way to do this? Each "AddRange" is really only adding one value. I would rather just get all the values in one fell swoop from a single select. I know it's possible, but I can't seem to get it right.

A:

It isn't clear from your question what the types of, e.g., solution.SolutionValues really are. Presuming it's IQueryable<SolutionValue>, which is the only way your question makes sense to me, you can do one SQL query and then split it up in memory:
var q = (from sv in solution.SolutionValues
         where sv.TypeName == objectiveX 
             || sv.TypeName == objectiveY 
         select new 
         {
             TypeName = sv.TypeName,
             Value = sv.Value
         }).ToList();

IEnumerable<double> xVal = from x in q
                           where x.TypeName == objectiveX 
                           select x.Value;
IEnumerable<double> yVal = from y in q
                           where y.TypeName == objectiveY 
                           select y.Value;

If you're asking how to rewrite the query that populates this.Solutions, however, I can't help, since that's not in your question.

