none
Lazy Loading inquiry RRS feed

  • Question

  • Assuming that ApplicationsGroupsLK & UsersGroupsLK child tables are properly linked in the EntityFramework model, I assume the following is the correct syntax to get the child data when performing a LINQ query against the data in my business object class.

    Can someone verify if this is the correct syntax?

    public static List<Groups> GetGroups()
        {
            try
            {
                using (wmswebEntities DbContext = new wmswebEntities())
                {
                    DbContext.Configuration.ProxyCreationEnabled = false;
                    DbContext.Configuration.LazyLoadingEnabled = true;
                    DbContext.Database.Connection.Open();
    
                    List<Groups> myGroups = new List<Groups>();
    
                     var myGroups = from p in DbContext.Groups
                                where p.ActiveFlag = true
                                select new
                                {
                                    p.Groups.ApplicationName,
                                    p.Groups.GroupName,
                                    p.Groups.GroupRank,
                                    p.Groups.ActiveFlag,
                                    p.Groups.DateAdded,
                                    p.Groups.AddedBy,
                                    p.Groups.LastUpdated,
                                    p.Groups.LastUpdatedBy,
                                    p.Groups.ApplicationsGroupsLK.ApplicationID,
                                    p.Groups.UsersGroupsLK.UserNumber
                                };
    
                    return myGroups;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    


    Bill Yeager

    Saturday, February 16, 2013 1:02 AM

Answers

  • Hi Bill;

    Looking at the model class Group and the results of the query I would not create the select clause as you are doing. The reason being is that the difference between the query fields being returned and the Group class is GroupNumber. So if you were to change the select as follows, myGroups = (from p ... select p).ToList();, it would properly fill the Group class having all the fields of the table. As you have it now you have a syntax error in that either you return all columns of the table or you return an anonymous type. In order to return an anonymous type you need to have the keyword new after the select keyword. Now anonymous type are NOT tracked and therefore will NOT lazy load any related data in code.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Tuesday, February 26, 2013 9:10 AM
    Saturday, February 16, 2013 1:51 PM
  • Fernando, thanks so much for your LINQ insight. I've learned a lot from your explanation...

    I updated the edmx model and mapped the pertinent fields I need into my Groups class (see boldfaced items). Based on your input, the following should work.

    Please let me know if the below defintions are correct....

    namespace CMSEFModel
    {
        using System;
        using System.Collections.Generic;
        
        public partial class Group
        {
            public Group()
            {
                this.ApplicationsGroupsLKs = new HashSet<ApplicationsGroupsLK>();
                this.GroupApplicationConfigurationsLKs = new HashSet<GroupApplicationConfigurationsLK>();
                this.UsersGroupsLKs = new HashSet<UsersGroupsLK>();
            }
        
            public int GroupNumber { get; set; }
            public string GroupName { get; set; }
            public int GroupRank { get; set; }
            public bool ActiveFlag { get; set; }
            public System.DateTime DateAdded { get; set; }
            public string AddedBy { get; set; }
            public System.DateTime LastUpdated { get; set; }
            public string LastUpdatedBy { get; set; }
            public int ApplicationID { get; set; }
            public string ApplicationName { get; set; }
            public int UserNumber { get; set; }
            public string UserID { get; set; }
        
            public virtual ICollection<ApplicationsGroupsLK> ApplicationsGroupsLKs { get; set; }
            public virtual ICollection<GroupApplicationConfigurationsLK> GroupApplicationConfigurationsLKs { get; set; }
            public virtual ICollection<UsersGroupsLK> UsersGroupsLKs { get; set; }
        }
    }
    public static List<Groups> GetGroups()
            {
                try
                {
                    using (wmswebEntities DbContext = new wmswebEntities())
                    {
                        DbContext.Configuration.ProxyCreationEnabled = true;
                        DbContext.Configuration.LazyLoadingEnabled = true;
    
                        IEnumerable<Groups> myGroups = new IEnumerable<Groups>();
    
                         myGroups = (from p in DbContext.Groups
                                    where p.ActiveFlag = true
                                    select
                                        p.Groups.GroupoNumber,
                                        p.Groups.GroupName,
                                        p.Groups.GroupRank,
                                        p.Groups.ActiveFlag,
                                        p.Groups.DateAdded,
                                        p.Groups.AddedBy,
                                        p.Groups.LastUpdated,
                                        p.Groups.LastUpdatedBy,
                                        p.Groups.ApplicationsGroupsLK.ApplicationID,
                                        p.Groups.ApplicationsGroupsLK.ApplicationName,
                                        p.Groups.UsersGroupsLK.UserNumber,
                                        p.Groups.UsersGroupsLK.UserID).ToList();
                                    
    
                        return myGroups;
                    }


    Bill Yeager

    • Marked as answer by Alexander Sun Tuesday, February 26, 2013 9:10 AM
    Sunday, February 17, 2013 4:42 PM

All replies

  • Hi Bill;

    Please see the comments in the code.

    public static List<Groups> GetGroups()
    {
        try
        {
            using (wmswebEntities DbContext = new wmswebEntities())
            {
                // Turning off proxy creation will disable entity tracking
                // as well will NOT do lazy loading
                DbContext.Configuration.ProxyCreationEnabled = false;
                // Will not lazy load because proxy creation is off
                DbContext.Configuration.LazyLoadingEnabled = true;
                // You should allow EF to open and close the connection as needed
                DbContext.Database.Connection.Open();
    
                List<Groups> myGroups = new List<Groups>();
    
                 // myGroups is defined as a List<Groups> but the results of the query
                 // will be returning a IQueryable<Anonymous> type and so it will not 
                 // Compile.
                 var myGroups = from p in DbContext.Groups
                            where p.ActiveFlag = true
                            // The use of the key work new without the use of a class type
                            // will create objects of anonymous type and NOT Groups. If you
                            // were to change the query to, (from p ... select p).ToList();,
                            // The the query would return a List<Groups>
                            select new
                            {
                                p.Groups.ApplicationName,
                                p.Groups.GroupName,
                                p.Groups.GroupRank,
                                p.Groups.ActiveFlag,
                                p.Groups.DateAdded,
                                p.Groups.AddedBy,
                                p.Groups.LastUpdated,
                                p.Groups.LastUpdatedBy,
                                p.Groups.ApplicationsGroupsLK.ApplicationID,
                                p.Groups.UsersGroupsLK.UserNumber
                            };
    
                return myGroups;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, February 16, 2013 4:45 AM
  • Fernando, thanks so much for replying...

    I understand what you're saying about the query; thanks...

    Would the modification below be correct?

    public static List<Groups> GetGroups()
            {
                try
                {
                    using (wmswebEntities DbContext = new wmswebEntities())
                    {
                        DbContext.Configuration.ProxyCreationEnabled = true;
                        DbContext.Configuration.LazyLoadingEnabled = true;
    
                        IEnumerable<Groups> myGroups = new IEnumerable<Groups>();
    
                         myGroups = (from p in DbContext.Groups
                                    where p.ActiveFlag = true
                                    select
                                        p.Groups.GroupName,
                                        p.Groups.GroupRank,
                                        p.Groups.ActiveFlag,
                                        p.Groups.DateAdded,
                                        p.Groups.AddedBy,
                                        p.Groups.LastUpdated,
                                        p.Groups.LastUpdatedBy,
                                        p.Groups.ApplicationsGroupsLK.ApplicationID,
                                        p.Groups.UsersGroupsLK.UserNumber).ToList());
    
                        return myGroups;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    If I have an EF generated class like below, and I execute the above query, will my class get populated correctly with the fields from GroupNumber through UserNumber?

    Or do I need a separate class only including those specific fields without the HashSet & Virtual fields?

    namespace CMSEFModel
    {
        using System;
        using System.Collections.Generic;
        
        public partial class Group
        {
            public Group()
            {
                this.ApplicationsGroupsLKs = new HashSet<ApplicationsGroupsLK>();
                this.GroupApplicationConfigurationsLKs = new HashSet<GroupApplicationConfigurationsLK>();
                this.UsersGroupsLKs = new HashSet<UsersGroupsLK>();
            }
        
            public int GroupNumber { get; set; }
            public string GroupName { get; set; }
            public int GroupRank { get; set; }
            public bool ActiveFlag { get; set; }
            public System.DateTime DateAdded { get; set; }
            public string AddedBy { get; set; }
            public System.DateTime LastUpdated { get; set; }
            public string LastUpdatedBy { get; set; }
            // ApplicationsGroupsLK - Lazy Loading population
            public int ApplicationID { get; set; }
            // UsersGroupsLK - Lazy Loading population
            public string UserNumber { get; set; }
        
            public virtual ICollection<ApplicationsGroupsLK> ApplicationsGroupsLKs { get; set; }
            public virtual ICollection<GroupApplicationConfigurationsLK> GroupApplicationConfigurationsLKs { get; set; }
            public virtual ICollection<UsersGroupsLK> UsersGroupsLKs { get; set; }
        }
    }


    Bill Yeager


    • Edited by Bill_Yeager Saturday, February 16, 2013 6:23 AM
    Saturday, February 16, 2013 6:22 AM
  • Hi Bill;

    Looking at the model class Group and the results of the query I would not create the select clause as you are doing. The reason being is that the difference between the query fields being returned and the Group class is GroupNumber. So if you were to change the select as follows, myGroups = (from p ... select p).ToList();, it would properly fill the Group class having all the fields of the table. As you have it now you have a syntax error in that either you return all columns of the table or you return an anonymous type. In order to return an anonymous type you need to have the keyword new after the select keyword. Now anonymous type are NOT tracked and therefore will NOT lazy load any related data in code.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Tuesday, February 26, 2013 9:10 AM
    Saturday, February 16, 2013 1:51 PM
  • Fernando, thanks so much for your LINQ insight. I've learned a lot from your explanation...

    I updated the edmx model and mapped the pertinent fields I need into my Groups class (see boldfaced items). Based on your input, the following should work.

    Please let me know if the below defintions are correct....

    namespace CMSEFModel
    {
        using System;
        using System.Collections.Generic;
        
        public partial class Group
        {
            public Group()
            {
                this.ApplicationsGroupsLKs = new HashSet<ApplicationsGroupsLK>();
                this.GroupApplicationConfigurationsLKs = new HashSet<GroupApplicationConfigurationsLK>();
                this.UsersGroupsLKs = new HashSet<UsersGroupsLK>();
            }
        
            public int GroupNumber { get; set; }
            public string GroupName { get; set; }
            public int GroupRank { get; set; }
            public bool ActiveFlag { get; set; }
            public System.DateTime DateAdded { get; set; }
            public string AddedBy { get; set; }
            public System.DateTime LastUpdated { get; set; }
            public string LastUpdatedBy { get; set; }
            public int ApplicationID { get; set; }
            public string ApplicationName { get; set; }
            public int UserNumber { get; set; }
            public string UserID { get; set; }
        
            public virtual ICollection<ApplicationsGroupsLK> ApplicationsGroupsLKs { get; set; }
            public virtual ICollection<GroupApplicationConfigurationsLK> GroupApplicationConfigurationsLKs { get; set; }
            public virtual ICollection<UsersGroupsLK> UsersGroupsLKs { get; set; }
        }
    }
    public static List<Groups> GetGroups()
            {
                try
                {
                    using (wmswebEntities DbContext = new wmswebEntities())
                    {
                        DbContext.Configuration.ProxyCreationEnabled = true;
                        DbContext.Configuration.LazyLoadingEnabled = true;
    
                        IEnumerable<Groups> myGroups = new IEnumerable<Groups>();
    
                         myGroups = (from p in DbContext.Groups
                                    where p.ActiveFlag = true
                                    select
                                        p.Groups.GroupoNumber,
                                        p.Groups.GroupName,
                                        p.Groups.GroupRank,
                                        p.Groups.ActiveFlag,
                                        p.Groups.DateAdded,
                                        p.Groups.AddedBy,
                                        p.Groups.LastUpdated,
                                        p.Groups.LastUpdatedBy,
                                        p.Groups.ApplicationsGroupsLK.ApplicationID,
                                        p.Groups.ApplicationsGroupsLK.ApplicationName,
                                        p.Groups.UsersGroupsLK.UserNumber,
                                        p.Groups.UsersGroupsLK.UserID).ToList();
                                    
    
                        return myGroups;
                    }


    Bill Yeager

    • Marked as answer by Alexander Sun Tuesday, February 26, 2013 9:10 AM
    Sunday, February 17, 2013 4:42 PM