Search code examples
c#entity-frameworkdatabase-design

Should I always use navigation properties or use the .Where query?


This question is whether which approach is better or whether it's insignificant at all. So, say I have a class Item like so

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
}

and the referenced one Category

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Item> Items { get; set; }
}

The problem is that when the user clicks on a category, all items within that category should be outputted in a list, and so should do the categories of that item.

I could use something like

_context.Items.Where(c => c.Category.Id == id);

and remove the virtual member. The reason why I ask this and why I "want" to remove that member is because when I use WebApi to get Json data of the Items table for the client it outputs something like this:

{
    "Id": 1,
    "Name": "String",
    "Category": [
        {
        "Id": 1,
        "Name": "CName",
        "Items": []
        }, 
        {
        "Id": 1,
        "Name": "CName",
        "Items": []
        }
    ]
}

So the only problem with having a navigation property for the Category model is that it will also send me this "Items": [] when I request a list of items. It's not like it's affecting me or anything, so I want to know whether I should use the Navigation approach or the .Where approach. Or maybe there is some other way I should consider?


Solution

  • Always use navigation properties. EF will be able to generate a far more efficient query than you (likely, at least). Its also much easier to read.

    That said. Do not use your EF objects as data contracts. Running one through a serializer will, in the best case do what you have above, and in the common case cause a circular reference exception.

    Your contracts should be defined separately so the circular references do not exist. Use Select to convert collections from the DB type to your API type.