Take the 2-minute tour ×

I'm having trouble with a relationship in Entity Framework, and I'm positive that most of the problem stems from my inability to come up with a good search phrase for google. However .. here is the issue ...

I have a set of 5 tables in an existing database. These tables have a relationship shown in the picture below.

Table Relationships

In my code (MVC4 with Razor, but should be obvious what's happening regardless of language), I am doing the following:

<p>Store: @Model.StoreName</p>
@foreach (var storeDepartment in Model.StoreDepartments)
    <p>Department: @storeDepartment.Department.DepartmentName</p>
        @foreach (var storeDepartmentItem in storeDepartment.Department.StoreDepartmentItems)

The problem is that I'm getting item records from other stores (as well as some items twice), where the DepartmentID is the same as the current Store. (Please don't ask why we're using the same DepartmentID for multiple stores ... I cannot answer that).

To explain the above, let's say Store ABC has a Department ID: 1, Name: Shirts. In this Department/Store there is an Item ID: 1, Name: Green Shirt.

Store DEF has a Department ID: 1, Name: Shirts. In this Department/Store there is an Item ID: 1, Name: Green Shirt, as well as an Item ID: 2, Name: Red Shirt.

When I run this in the code above for store ABC, it would show:

Store: ABC
Department: Shirts
- Green Shirt
- Green Shirt
- Red Shirt

Is there a way in Entity Framework to have the records from StoreDepartmentItems retrieved from the Department to consider the current StoreID?

Thanks in advance, Evan

EDIT: Just to be clear, I can avoid the duplication by changing the line:

@foreach (var storeDepartmentItem in 


@foreach (var storeDepartmentItem in 
    storeDepartment.Department.StoreDepartmentItems.Where(a=>a.StoreID == Model.StoreID))

However, that seems like I am putting more pressure on the view developers, which is not my preference.

share|improve this question
How are you mapping/retrieving the data? –  JTMon May 24 '13 at 19:05
@JTMon I am not sure what you are asking. The code I am using is in the message above. –  ESDictor May 28 '13 at 16:06
add comment

1 Answer

Delete the StoreId from your StoreDepartmentItems Although your diagram doesnt show which are the primary keys It seems to me that you dont need it because the DepartmentId maps to the StoreDepartment which also has the StoreId.

Your problem could be that one StoreDepartmentItem can relate to 2 different stores with your current structure

share|improve this answer
Unfortunately, I cannot change the data dictionary. I need to code to what is there for compatibility reasons. –  ESDictor May 28 '13 at 16:04
Then since your StoreDepartmentItems can relate to 2 different sourceIDs you need to find out which one you are meant to use –  kirsten g May 28 '13 at 21:57
I have a store object. I get the departements from StoreDepartments (store.StoreDepartments). From that I get a single department object. That department object was retrieved indirectly from the store object. When I get the items for that department, shouldn't it still be linked to the store object that started the whole thing? –  ESDictor May 29 '13 at 13:44
add comment

Your Answer


