tags:

views:

68

answers:

1

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

+2  A: 

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.

Craig Stuntz
Yes, when I executed it and looked at SQL profiler it executed a sub query for each record. I am going to double check my query and see if I have something that should not be there.
Jason Heine
Okay, with one sub query it works, but when i have 2, it queries the database 100's of times. I updated my post with the actual query. If i run them individually one at a time, it works, however when I have both in there, the Attributes will always query 100's of times. Thoughts?
Jason Heine
Then use Craig's Patented LINQ Debugging Algorithm™: Take the case which works and the case which does not and transform one into the other, piece by tiny piece. This will allow you to narrow down the problem. Perhaps it's the `ToList()`? Try it without that? Perhaps it's two subqueries? Try an isolated test case on Northwind. Sooner or later you'll find the precise issue.
Craig Stuntz
I don't think it is the ToList(), and I need the ToList() because I am filling a custom object. If I use each sub query by themselves, it works, however, once I put them both in there, this is when it happens. I will keep looking and see if I can figure it out... thanks...
Jason Heine