Why does my Entity Framework query return a record twice?

I have two tables, one with Contacts (people) and another with Addresses. Gregory Alderson has one Contact entry and two Address entries.

This is the code:

that returns two a records for Gregory Alderson:

If I leave LazyLoadingEnable set to ‘true’, it does the same thing but both records contain both addre开发者_如何学JAVAsses:

The book I’m learning from (Programming Entity Framework 2nd edition – good book BTW) explained that LazyLoading is disabled so the Count method does not impact the results, but so far has not explained why it would do so.

Can someone explain to me why I get two records with LazyLoading turned off, and two records (both with both addresses) with LazyLoading turned on?

---

A good way to get a better understanding of what's going on is run up Query Analyzer and watch what SQL statements are executed against the db or better yet get a copy of Ayende's EF Profiler.

Essentially with eager loading you need to be more explicit on what related entities you want returned. This is done using the Include method on the context object. Without lazy loading enabled you're making a single hit against the db and then evaluating only against the locally held data rather than making another request to the db for further data used in the Count().

---

The issue here seems to be due to what you're selecting. Specifically:

select new {a, a.Contact}

Contact is actually a navigation property of a in this case. When you select a, you're selecting everything on a, including Contact. Also selecting a.Contact means you get contact twice.

With lazy loading enabled you don't have to select it. If you select a and then simply use a.Contact somewhere else in your code, EF will go load it for you. The "lazy" in lazy loading is that it's not loaded unless you actually try to use it. With lazy loading on, you just need this:

select a

With lazy loading off, that doesn't happen. But you still don't want to select it. Instead you'd use Include:

from a in context.Addresses.Include("Contact") select a

That tells EF that you always want it to load the Contact navigation property and to do so immediately. It'll be available right away and will still be available if you dispose of the context (which isn't the case with lazy loading).

I suspect the problem here is that by selecting all of a AND a property of a, you're getting a weird side effect.

---

The OP's question, summed up in the last two paragraphs, was:

The book I’m learning from (Programming Entity Framework 2nd edition – good book BTW) explained that LazyLoading is disabled so the Count method does not impact the results, but so far has not explained why it would do so.

Can someone explain to me why I get two records with LazyLoading turned off, and two records (both with both addresses) with LazyLoading turned on?

The part about the effects of LazyLoading on Count() was explained by Daz. But neither Daz nor Tridus explained why the OP was getting two Contact records for Gregory Anderson in the output regardless of LazyLoading. That is the question that I will answer here.

The problem is that the iteration was essentially happening over Addresses. That is, the outer foreach loop was executing once for each Address in Canada. So because Gregory Anderson has two addresses in Canada, the loop is executed twice for him.

Note that if Gregory Anderson also had an address in the US, the loop would still be executed twice, but all three addresses would be printed out, not just the addresses in Canada.

If the intention was actually to list each Contact once and then list each Address for that Contact, a more appropriate design would be the following:

var contacts = context.Contacts
    .Where(c => c.Addresses.Any(a => a.CountryRegion == "Canada"));
foreach (var c in contacts)
{
    Console.WriteLine("{0} {1}, Addresses: {2}",
        c.FirstName.Trim(),
        c.LastName.Trim(),
        c.Addresses.Count());
    foreach (var a in c.Addresses)
    {
        Console.WriteLine("...{0}, {1}\n", a.Street1.Trim(), a.City.Trim());
    }
}

(I tried to keep the code as close to identical as I could, but I couldn't think of how to write the query using query syntax, so I used LINQ syntax because I'm much more familiar with it...)

This code will result in a distinct list of Contacts being returned from the database and then each Contact will be output one time along with each of the child Addresses.

Hope that helps someone who might be dealing with this and didn't find the other answers helpful on this aspect.