prevent child tables from being populated in a linq query

Question

Hi,

Apologies if this has been posted elsewhere but I can't seem to find any info regarding this.

I'm trying to load an object using the LinqtoSql data context.

I have a Page and Template table. There is a relation between the two: Page.TemplateId to Template.TemplateID

The data context autocreated the Page and Template classes.

My code is:

public class PageRepository
{
    Table<Page> _table = (new HospoEngineDataContext()).GetTable<Page>();

    public IQueryable<Page> Pages
    {
        get { return _table; }
    }

    public Page GetPageByName(string name)
    {
        Page page = (from p in _table where p.Name == name select p).FirstOrDefault();

        return page;
    }
}

When I run GetPageByName(), it'll populate the returned Page object fine.

However it seems it also loads the Page.Template sub object too.

Is there any way I can prevent this from happening?

EDIT: I've just installed SqlServerQueryVisualizer and created a new query so I can see what sql is being generated by adding this code to the above class:

public IQueryable<Page> Test()
    {
        var pages = (from p in _context.Pages where p.Name == "Home Page Name" select p);

        return pages;

    }

It turns out that the sql being generated is:

{SELECT [t0].[PageId], [t0].[TemplateId], [t0].[Name], [t0].[Title], [t1].[TemplateId] AS [TemplateId2], [t1].[Name] AS [Name2], [t1].[TemplateFile] FROM [dbo].[Pages] AS [t0] INNER JOIN [dbo].[Templates] AS [t1] ON [t1].[TemplateId] = [t0].[TemplateId] WHERE [t0].[Name] = @p0 }

Which doesnt seem to be only loading the Pages table.

Answer 1

I am reasonablly sure (attach a profiler to your database to confirm) that LINQtoSQL will populate the object on demand. If you don't query the child object, it wont populate.

Answer 2

Highlight the template member in the Linq to Sql designer surface, then change the property DelayLoaded to true. You could validate that it works correctly by watching the code execute from SQL Profiler

Answer 3

By default, Linq-to-Sql uses deferred execution. This means that the Page.Template object doesn't get loaded until you access it for the first time, though it looks like it's immediately loaded. This article explains this well. Notice that, if you are debugging with visual studio, when you open the Template property on Page, it will get loaded just then.

Answer 4

Its very likely that it isn't.

It surely gets populated when you check that property - either in code or in the debugger (if that's what you are doing).

There are some properties involved that could cause it, but I would discard what I say above first (attach a profiler to your database).

If it isn't the above, check the DelayLoaded property & also make sure the DataLoad options of the datacontext aren't being explicitely set to eager load Template.