Populate JOIN into a list in one database query

Question

I am trying to get the records from the 'many' table of a one-to-many relationship and add them as a list to the relevant record from the 'one' table.

I am also trying to do this in a single database request.

Code derived from http://stackoverflow.com/questions/1580199/linq-to-sql-populate-join-result-into-a-list almost achieves the intended result, but makes one database request per entry in the 'one' table which is unacceptable. That failing code is here:

        var res = from variable in _dc.GetTable<VARIABLE>()
                  select new { x = variable, y = variable.VARIABLE_VALUEs };

However if I do a similar query but loop through all the results, then only a single database request is made. This code achieves all goals:

        var res = from variable in _dc.GetTable<VARIABLE>()
                  select variable;

        List<GDO.Variable> output = new List<GDO.Variable>();
        foreach (var v2 in res)
        {
            List<GDO.VariableValue> values = new List<GDO.VariableValue>();
            foreach (var vv in v2.VARIABLE_VALUEs)
            {
                values.Add(VariableValue.EntityToGDO(vv));
            }
            output.Add(EntityToGDO(v2));
            output[output.Count - 1].VariableValues = values;
        }

However the latter code is ugly as hell, and it really feels like something that should be do-able in a single linq query.

So, how can this be done in a single linq query that makes only a single database query?

In both cases the table is set to preload using the following code: _dc = _db.CreateLinqDataContext();

        var loadOptions = new DataLoadOptions();
        loadOptions.LoadWith<VARIABLE>(v => v.VARIABLE_VALUEs);
        _dc.LoadOptions = loadOptions;

I am using .NET 3.5, and the database back-end was generated using SqlMetal.

Answer 1

This link may help

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Look under join operators. You'll probably have to change from using extension syntax other syntax too. Like this,

var = from obj in dc.Table
      from obj2 in dc.Table2
      where condition
      select