Linq slow getting 2 fields from Entity with lots of foreign keys RRS feed

  • Question

  • I'm using the query below:

                    List<SiteDALDescription> siteList = (from s in dc.T_Site

                                                         where s.SiteLocked == false

                                                         orderby s.SiteDescription ascending

                                                         select new SiteDALDescription { ID = s.SiteID, Description = s.SiteDescription }).ToList();

    SiteDALDescription is a class consisting of just an int ID and string Description.  dc is my strongly typed datacontext.  We have >500 tables in the database, so I used SQLMetal to generate the entities.  If I run the above query on a table with no foreign keys, its really fast.  If I run it on a table with lots of foreign keys (hence lots of entitysets are generated for it) then the query is too slow for me to use in my UI.  I only want ID and Description back from one table.  Can I stop it filling all the entitysets?
    I've tried compiling the query, removing the tolist and iterating through the query, making it an anonymous type.  But no matter what I do its still slow on particular tables.  Can anyone help?

    • Moved by Jackie-SunModerator Wednesday, July 6, 2011 5:55 AM (From:ADO.NET Managed Providers)
    Monday, May 9, 2011 10:21 AM


  • Hello DevJon,


    Welcome to the MSDN Forum.

    According to your description, I think you could try to close Deferred Loading. And it turns on by default. In order to turn it off, you can set DeferredLoadingEnabled property to false. Like this:

    BlogDataContext ctx = new BlogDataContext(...);


    ctx.DeferredLoadingEnabled = false;


    var query = from b in ctx.Blogs

                select b;


    You can also Use DataLoadOptions to Control Deferred Loading. Here is an article from codeproject about using DataLoadOptions:

    I hope this can help you.


    Have a nice day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 10, 2011 8:18 AM