Duplicate Output with Entity Framework and LINQ and Foreach Loop

Overview

I have a website I am writing for keeping personal bookmarks. And they are kept in categories. I can print out the links just fine when doing it one way. But when I throw a linq query in the mix, somehow I get duplicates of each link.

Scenarios

First way without duplicates

Now I removed the html code from the loops because it is unimportant.

var categoryContext = new LinkDatabaseEntities();
var linkContext = new LinkDatabaseEntities();
foreach (Category category in categoryContext.Categories)
{
    foreach (Link link in linkContext.Links)
    {
        if (link.CatID.Equals(category.ID))
        {
            Print Out HTML Code
        }
   }
}

This code works perfectly. I don't get any duplicates.

Output:

Category 1
- Link 1
- Link 2
Category 2
- Link 1
- Link 2

Code that outputs duplicates

Now, the information in the database isn't going to be that big, so I really don't need this for efficiency, I just want to understand what is happening.

var categoryContext = new LinkDatabaseEntities();
var linkContext = new LinkDatabaseEntities();
foreach (Category category in categoryContext.Categories)
{
    var links = from link in linkContext.Links
                where link.catID == category.ID
                select link;

    foreach (Link link in links)
    {
        Print Out HTML Code
    }
}

This way has duplicates of every link. I don't understand why this is happening.

Output:

Category 1
- Link 1
- Link 1
- Link 2
- Link 2
Category 2
- Link 1
- Link 1
- Link 2
- Link 2

If someone could explain why this is happening, I would greatly appreciate it. Thanks

Solution

OP:

This way has duplicates of every link. I don't understand why this is happening.

It's happening because you did not use a join, if you don't it mulitplies out the items in A by the count of items in B. A similar issue and solution can be found here.

Navigation properties

This assumes you have correctly defined relationships and foreign keys in your database schema and have corresponding information in your EF code/EDMX.

Considering you are using an ORM, an easier way that doesn't lead to duplicates is to take advantage of the EF Navigation Properties.

Change the following:

var categoryContext = new LinkDatabaseEntities();
var linkContext = new LinkDatabaseEntities();
foreach (Category category in categoryContext.Categories)
{
    var links = from link in linkContext.Links
                where link.catID == category.ID
                select link;

    foreach (Link link in links)
    {
        Print Out HTML Code
    }
}

...to:

var categoryContext = new LinkDatabaseEntities();
var linkContext = new LinkDatabaseEntities();
foreach (var category in categoryContext.Categories)
{
    foreach (var link in category.Links) // <---- notice the use of the nav property
    {
        // the above category.Links contains only the links applicable
        // for a given category AND NOT all the links found in say a LINKS table

        (Print Out HTML Code)
    }
}

The above works by taking advantage that EF already knows which Links belong to a given Category because the navigation properties reflect the relationships and foreign keys defined in the database schema.