Solved

Help me understand how lazy loading works in Entity Framework 5.0

Posted on 2014-12-16
7
299 Views
Last Modified: 2014-12-29
I am building an application that is using MS Entity Framework 5.0.  When querying for data, it appears that more data is being returned than I need in some cases.  Right now my test database is small so I am not seeing a performance issue but in production there can be a million records so I want to make sure I am not retrieving data I don't need.

Here is an example of what I am seeing:

REF_Referrals definition (define by model)
    public partial class REF_Referrals
    {
        public REF_Referrals()
        {
            this.REF_ReferralStatus = new HashSet<REF_ReferralStatus>();
            this.REF_ReferralComments = new HashSet<REF_ReferralComments>();
            this.REF_LinkedReferrals = new HashSet<REF_LinkedReferrals>();
        }
    
        public int ReferralsID { get; set; }
        public string ReferralsSynopsis { get; set; }
        public int ReferralsRequesterProfileID { get; set; }
        public System.DateTime ReferralsSubmittedDate { get; set; }
        public int REF_ReferralTypeReferralTypeID { get; set; }
        public System.DateTime LastUpdateTimeStamp { get; set; }
        public string ReferralsKeyString { get; set; }
        public Nullable<System.DateTime> ReferralsSLADate { get; set; }
        public string ReferralsPriority { get; set; }
        public string ReferralsRequesterName { get; set; }
        public int ReferralsApproverID { get; set; }
        public string ReferralsApproverName { get; set; }
        public string ReferralsApproverWorkExt { get; set; }
        public string ReferralsApproverEmail { get; set; }
        public bool ReferralsIsApproved { get; set; }
    
        public virtual REF_ReferralType REF_ReferralType { get; set; }
        public virtual ICollection<REF_ReferralStatus> REF_ReferralStatus { get; set; }
        public virtual KB_Referrals KB_Referrals { get; set; }
        public virtual PA_Referrals PA_Referrals { get; set; }
        public virtual ICollection<REF_ReferralComments> REF_ReferralComments { get; set; }
        public virtual ICollection<REF_LinkedReferrals> REF_LinkedReferrals { get; set; }
    }

Open in new window


REF_ReferralType definition (created by model)
    public partial class REF_ReferralType
    {
        public REF_ReferralType()
        {
            this.ReferralTypeRequiresApproval = false;
            this.ReferralTypeActiveStatusFlag = true;
            this.REF_Referrals = new HashSet<REF_Referrals>();
            this.PA_Referrals = new HashSet<PA_Referrals>();
            this.KB_Referrals = new HashSet<KB_Referrals>();
        }
    
        public int ReferralTypeID { get; set; }
        public string ReferralTypeUniqueKey { get; set; }
        public string ReferralTypeName { get; set; }
        public string ReferralTypeRoute { get; set; }
        public string ReferralTypeAuthorizationCode { get; set; }
        public bool ReferralTypeRequiresApproval { get; set; }
        public string ReferralTypeGeneralGuidelines { get; set; }
        public string ReferralTypeMoreInfoLink { get; set; }
        public bool ReferralTypeActiveStatusFlag { get; set; }
        public System.DateTime LastUpdateTimeStamp { get; set; }
        public int REF_ReferralGroupReferralGroupID { get; set; }
        public int ReferralTypeSLA { get; set; }
        public short ReferralTypeBasePriority { get; set; }
        public string ReferralTypeToolTip { get; set; }
    
        public virtual REF_ReferralGroup REF_ReferralGroup { get; set; }
        public virtual ICollection<REF_Referrals> REF_Referrals { get; set; }
        public virtual ICollection<PA_Referrals> PA_Referrals { get; set; }
        public virtual ICollection<KB_Referrals> KB_Referrals { get; set; }
    }

Open in new window


When the following code is executed
            REF_Referrals recordToGet = _db.REF_Referrals.Find(referralid);

Open in new window

a single REF_Referrals record is returned which is what I want.  The problem is with extra data being returned that I don't want.  Returns all REF_Comments for the referral which is what I want;  all REF_ReferralStatus records are returned which is also what I want.  The problem occurs with the REF_ReferralType property in the REF_Referrals definition (public virtual REF_ReferralType REF_ReferralType { get; set; }).  

This property has the ReferralType record info which I need but it also returns the collections defined in the REF_ReferralType model.  This is where I will have a problem in production.  In my test environment, I only have 5 REF_Referral records.  In production I could have 50,000 or more REF_Referral records associated with a specific ReferralType.  I don't want to load that data nor do I need it in the context of how this data will be used.

I know I can turn Lazy Loading off but was wondering is there a way to tell it what to include or exclude when executing the .Find()?  In other words, tell it to load everything in REF_Referrals but exclude loading the collections in REF_ReferralType?

Any help in understanding how to control what is loaded with EF is greatly appreciated!
0
Comment
Question by:dyarosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Expert Comment

by:Snarf0001
ID: 40503315
I think it actually is acting as you want.
Lazy loading acts by not actively loading the records until you actually request them.

So with your recordToGet object, it has NOT loaded all of the comments when it brings the record back.
But the first time you call "recordToGet.REF_ReferralComments", EF sees that you need the objects, sends ANOTHER query to the database and brings them back.

Basically on-demand loading from the database.
0
 

Author Comment

by:dyarosh
ID: 40503424
So basically in my debugger when I inspect the object and see a Row.Count = 5 on a collection the data is only there because I inspected the object?

Is Lazy Loading on or off by default?
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 40503479
I may be wrong, but I believe it's On by default.
How do you inspect, did you open the "Results" item in the debugger?

In any case, yes, that should be how it's working.
The quickest way to test this, is make the variable outside the scope of the context using statement.
Then try to hit the property once the context is disposed.  If lazy loading is on, and it hasn't brought back the rows, you'll get an error message.

REF_Referrals recordToGet;
using(var context = new Context())
{
  recordToGet = _db.REF_Referrals.Find(referralid);
}

//this should bomb out now if all is lazy loading is on (and you have NOT looked at the property in the debugger yet)
var test = recordToGet.REF_ReferralComments.ToArray();

Open in new window

0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 34

Expert Comment

by:it_saige
ID: 40503766
Just to assist in the visualization of lazy loading.  Consider the following:
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_Q28581996
{
	class Program
	{
		private static readonly List<string> names = new List<string>() { "Alvin", "Simon", "Theodore", "Dave" };
		static void Main(string[] args)
		{
			var staticList = names;
			var lazyList = (from name in names select name);

			foreach (var name in staticList)
				Console.WriteLine(name);

			foreach (var name in lazyList)
				Console.WriteLine(name);

			Console.ReadLine();
		}
	}
}

Open in new window

Debugging reveals the following -

staticList is a well defined list of string based on a reference from the names list.Capture.JPGlasyList on the other hand is an Enumerable.WhereSelectListIterator and stipulates that expanding the Results view will enumerate the IEnumerable.Capture.JPGEntering the foreach method for staticList we see that staticList is unchanged.Capture.JPGEntering the foreach method for lazyList we see that lazyList, also, is unchanged.Capture.JPGAnd look at the enumerator property.Capture.JPGEven after we get the first value for lazyList we see that lazyList, is still unchanged.Capture.JPGBut look at the enumerator property.Capture.JPGWhat is happening is that each item is returned as you iterate over items container.

-saige-
0
 

Author Comment

by:dyarosh
ID: 40516796
Here is what the debugger shows after executing the following:
recordToGet = _db.REF_Referrals.Find(referralid);

Open in new window


Result of Debugging recordToGet
Is the following correct?

1. PA_Referrals shows {System.Data.Entity.Dynamic.Proxies.PA_Referrals...} so the record isn't loaded until I reference it.
2. REF_ReferralComments shows a record Count of 2 so those records have already been loaded.

When I click on PA_Referrals I get the following:

LazyLoading2.fw.png
Is the following correct?

1. REF_Referrals shows {System.Data.Entity.Dynamic.Proxies.REF_Referrals...} so the record isn't loaded unless I reference it.

Thank you for helping to explain this to me.
0
 
LVL 34

Accepted Solution

by:
it_saige earned 500 total points
ID: 40516829
In your case, when you use:
recordToGet = _db.REF_Referrals.Find(referralid);

Open in new window


recordToGet contains a reference to the found referral and _db.REF_Referrals is the lazy-loaded collection.

Referring back to my example:
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_Q28581996
{
	class Program
	{
		private static readonly List<string> names = new List<string>() { "Alvin", "Simon", "Theodore", "Dave" };
		static void Main(string[] args)
		{
			var staticList = names;
			var lazyList = (from name in names select name);

			foreach (var name in staticList)
				Console.WriteLine(name);

			foreach (var name in lazyList)
				Console.WriteLine(name);

			var found = lazyList.Find(x => x == "Dave");
			if (found != null)
				Console.WriteLine("Found: ", found);
			else
				Console.WriteLine("Could not find name.");

			Console.ReadLine();
		}
	}

	static class Extensions
	{
		public static T Find<T>(this IEnumerable<T> source, Func<T, bool> predicate)
		{
			foreach (T item in source)
			{
				if (predicate(item))
					return item;
			}
			return default(T);
		}
	}
}

Open in new window


Trying to find Dave -Capture.JPGMy lazyList is still just a lazy list.  But found, found represents a member of that list, in this case "Dave".  Found *is* Dave, so found is not a lazy loaded item.

-saige-
0
 

Author Closing Comment

by:dyarosh
ID: 40521713
Thank you for the explanation.  That helps to clear things up.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error building VS2105 solution from repository 1 56
Return array 3 32
aspx ascx, c# 7 38
Difference between Leaflet and MapBox? 5 42
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question