Solved

Help me understand how lazy loading works in Entity Framework 5.0

Posted on 2014-12-16
7
263 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

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 22

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

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 32

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now