Mapping a weak entity in M:M Relationship

Question

I have a mapper function:

public void Mapper(List<Item> items, List<List> lists, List<ListItem> listItems)
    {
        using (var command = Context.CreateCommand())
        {
            command.CommandText = @"SELECT ListItem.ListId, ListItem.ItemId, Item.ItemId AS ItemItemId, List.ListId AS ListListId 
                                        FROM Item INNER JOIN
                                        ListItem ON Item.ItemId = ListItem.ItemId INNER JOIN
                                        List ON ListItem.ListId = List.ListId";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    int listid = (int)reader["ListId"];
                    int itemid = (int)reader["ItemId"];
                    int listlistid = (int)reader["ListId"];
                    int itemitemid = (int)reader["ItemItemId"];

                    foreach (var listitem in listItems)
                    {
                        foreach (var item in items)
                        {
                            if (item.ItemId == itemitemid && itemitemid == itemid)
                            {
                                listitem.Item = item;
                            }
                        }

                        foreach (var list in lists)
                        {
                            if (list.ListId == listlistid && listlistid == listid)
                            {
                                listitem.List = list;
                            }
                        }
                    }
                }
            }
        }
    }

My goal is that the 3 parameters of lists of entities, that I have got from my database, but I need to map the references between "List and Listitem" and "Item and ListItem".

My problem is that it always seem to map the references to the LAST Item and List in its tables, for an example, if I was my last list in its Table was "Fridge" and the last Item in its table was "Milk", all listitems will have a reference to those.

Can anyone help? And tell me if I need to provide more code in comments please. :)

EDIT: ListItem Implementation

public class ListItem
{
public virtual int Amount
{
    get;
    set;
}

public virtual int Volume
{
    get;
    set;
}

public virtual string Unit
{
    get;
    set;
}

public virtual List List
{
    get;
    set;
}

public virtual Item Item
{
    get;
    set;
}

}

Answer 1

Here, I've changed your code for you so you can see exactly what I mean by adding the condition.

Look out for the lines where we're doing a continue.

public void Mapper(List<Item> items, List<List> lists, List<ListItem> listItems)
{
    using (var command = Context.CreateCommand())
    {
        command.CommandText = @"SELECT ListItem.ListId, ListItem.ItemId, Item.ItemId AS ItemItemId, List.ListId AS ListListId 
                                    FROM Item INNER JOIN
                                    ListItem ON Item.ItemId = ListItem.ItemId INNER JOIN
                                    List ON ListItem.ListId = List.ListId";
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                int listid = (int)reader["ListId"];
                int itemid = (int)reader["ItemId"];
                int listlistid = (int)reader["ListId"];
                int itemitemid = (int)reader["ItemItemId"];

                foreach (var listitem in listItems)
                {
                    // This is the condition you're missing.
                    if (listitem.ListId != listid &&
                        listitem.ItemId != itemid)
                    {
                        continue;
                    }

                    foreach (var item in items)
                    {
                        if (item.ItemId == itemitemid && itemitemid == itemid)
                        {
                            listitem.Item = item;
                        }
                    }

                    foreach (var list in lists)
                    {
                        if (list.ListId == listlistid && listlistid == listid)
                        {
                            listitem.List = list;
                        }
                    }
                }
            }
        }
    }
}

Answer 2

The reason why all elements from listItems end up having their Item assigned to the last element in items and their List assigned to the last element in lists is because every element in listItems (see foreach) is ultimately being assigned, unconditionally, for every ListItem in the database (see outer-most while).

To explain more simply, the code you have is the logical equivalent of this pseudo-code.

foreach (db_element in db)
{
    foreach (list_element in list)
    {
        list_element.property = ...;
    }
}

Assuming 3 elements in db and 5 elements in list there would be 15 assignments (3 x 5 = 15), instead of just 3 as you may have intended.

Instead, this is how you can implement a condition for matching the appropriate element from list

foreach (db_element in db)
{
    foreach (list_element in list)
    {
        if (list_element.ItemId == db_element.ItemId &&
            list_element.ListId == db_element.ListId)
        {
             list_element.property = ...;
        }
        else
        {
             // wrong one! skip.
        }
    }
}