How do i populate a POCO (child) IList property from a Linq2Sql query?

Question

Hi folks,

I have a two classes:

public class Question
{
    public IList<Answer> Answers { get; set; }
}

public class Answer
{ .. }

In my Linq2Sql designer, there's two L2S objects on designer, with the correct 0<->many arrow between them. Kewl.

I'm not sure how i can retrieve these questions/answers in a single call and populate my POCO objects ..

this is what i've got ... can someone fill in the blanks?

public IQueryable<Question> GetQuestions()
{
    return from q in _db.Questions
        select new Question
            {
                Title = q.Title,
                Answers = ????????   // <-- HALP! :)
            };
}

thoughts?

Update : War of the POCO

Thanks for the replies but it's not 100% there yet.

Firstly, i'm returning a POCO class, not the Linq2Sql context class. This is why i'm doing...

select new Question { .. };

that class is POCO, not the linq2sql.

Secondly, I like the answers that point to doing Answers = q.Answers.ToList() but this also will not work because it's trying to set a Linq2Sql class to a POCO class.

Answer 1

If your LINQ to SQL classes have a relationship between them, then an "Answers" property should have been generated on the "Question" LINQ to SQL class. So you should be able to simply do this:

return from q in _db.Questions
       select new Question
       {
           Title = q.Title,
           Answers = q.Answers.ToList(),
       }

You may be able to omit the call to ToList() - I'm not sure what type LINQ to SQL uses for generated related rows (I believe it's IQueryable<T>).

To force the Answers property to be populated up front, rather than firing off another query for each question, you can use the DataLoadOptions class. Essentially you tell LINQ to SQL to load answers any time you query questions (using the LoadWith method - see the MSDN documentation).

Edit

You're right - since your Question.Answers property is a list of your own POCO, assigning it with q.Answers isn't the way to go. This might work better:

return from q in _db.Questions
       select new Question
       {
           Title = q.Title,
           Answers = (from a in q.Answers
                     select new Answer { ... }).ToList(),
       }

Answer 2

Associated data is usually handled via the EntitySet<T> class (the property is is created for you if you use the dbml-generated code).

Associations can be lazy or early loaded; by default, lazy (i.e. it is loaded when first needed) - but to force it to load early using DataLoadOptions/LoadWith() - see here. To duplicate the example from the MSDN page:

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = dlo;

var londonCustomers =
    from cust in db.Customers
    where cust.City == "London"
    select cust;

foreach (var custObj in londonCustomers)
{
    Console.WriteLine(custObj.CustomerID);
}

Here, the LoadWith means that when a Customer is loaded, their Orders are fetched at the same time. This is useful if you know which associated data you are going to need. Without this, it would perform another TSQL query *per Customer** to get this data (but only if/when you asked for it).

Answer 3

if the Question type you are returning is the same type as the in _db.Questions you can just write

return (from q in _db.Questions
        select q).ToList();

Answer 4

It is even easier. Assuming your database schema is normalized and contains the correct relationships, just toggle to the deferred loading to false:

private DBDataContext mDB = new DBDataContext(ConfigurationManager.ConnectionStrings["KeyFromWebConfig"].ConnectionString);

mDB.DeferredLoadingEnabled = false

Now any linq query using mDB will fetch child objects too. Of course, you may want to toggle this on and off as needed to minimize performance impact.