LINQ to SQL - Custom Object with List<T> Property

Question

Hello there,

What I am attempting to do is the following:

I have a custom object OTest and OSubTest:

public class OTest
{
   public int TestId {get; set;}
   public List<OSubTest> SubTests {get; set;}
}

public class OSubTest
{
   public int SubTestId {get; set;}
}

Now, I will run a LINQ to SQL query which will look like this:

var query = from t in Tests 
select new OTest
{
   TestId = t.TestId
};

The question is, how can I get the "Subtests" as part of the query and as a list property in my main OTest object without the LINQ running 100's of sub queries off the main query for the data.

If I did it like this then it will execute a query for each record that is returned, which is not very practical:

var query = from t in Tests 
select new OTest
{
   TestId = t.TestId,
   SubTests = (from st in SubTests where st.TestId = t.TestId select new OSubTest{SubTestId = st.SubTestId}).ToList()
};

Any thoughts on how I can get my List property to fill with a single query? Perhaps with left joins?

Any help would be appreciated.

If you need any clarification please ask me.

EDIT

Okay, taking a look at this, it does work when I have 1 sub List, but as soon as I add a 2nd one the 2nd one queries the database 100's of times. Here is my exact query:

public List<OFile> GetFiles(int fileGroupId)
        {
            var query = (from f in CoreContext.Files
                         join fgf in CoreContext.FileGroupFiles on f.FileId equals fgf.FileId
                         where fgf.FileGroupId == fileGroupId
                         select new OFile
                                    {
                                        ActiveFile = f.ActiveFile,
                                        FileExtension = f.FileType,
                                        FileGuid = f.FileIdentifier,
                                        FileName = f.FileName,
                                        FilePath = f.FilePath,
                                        FileSize = f.FileSize,
                                        FileTitle = f.FileTitle,
                                        RevisionId = f.RevisionId,
                                        ParentFileId = f.ParentFileId,
                                        FileId = f.FileId,
                                        Attributes = f.FileAttributeFiles.Where(id => id.FileId == f.FileId).Select(
                                            id => new OFileAttribute
                                                      {
                                                          FileAttributeId = id.FileAttributeId,
                                                          Value = id.Value
                                                      }).ToList(),
                                        Tags = f.FileTags.Where(id => id.FileId == f.FileId).Select(
                                            id => new OFileTag
                                                      {
                                                          FileTagId = id.FileTagId,
                                                          TagName = id.TagName,
                                                      }).ToList(),


                                    });



            return query.ToList();
        }

The Attributes will query the database 100's of times and does not show up as a JOIN.

The FileAttributeFiles table has a FK to an Attributes table.

Thanks

Answer 1

If I did it like this then it will execute a query for each record that is returned, which is not very practical

Um, really? Have you tried it? I'm not sure what Tests and SubTests are supposed to be, but generally speaking, LINQ to SQL executes queries of this form:

using (var c = new MyDataContext())
{
    var q = from t in c.Tests
            select new 
                   {
                       TestId = t.TestId,
                       SubTests = from st in t.SubTests 
                                  where st.TestId = t.TestId 
                                  select new 
                                  {
                                      SubTestId = st.SubTestId
                                  }
                    };
}

...in one query. Projecting onto POCOs instead of anonymous types works, too. I don't know if using ToList() changes anything, because I don't use that, personally; it "just works" without it.