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
CodePudding user response:
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 Link
s belong to a given Category
because the navigation properties reflect the relationships and foreign keys defined in the database schema.
See also
- Relationships, navigation properties, and foreign keys, Learn, Microsoft, retrieved 2022-12-4