Avoid unnecessary include statements in SQL database query for ASP.NET Core

Question

There are some times where I have a user and would like to check whether they are in the organization that owns the item they are trying to access. Let's say the tables in my SQL server look like this:

• Table: Organization
• Table: User
  • Column: OrganizationID (foreign key to Organization table)
• Table: Item
  • Column: OrganizationID (foreign key to Organization table)

Edit: however, my models do not have OrganizationID directly but rather the organization, as below:

public class Item{
    public int ItemID { get; set; }
    public string ItemSomeProperty { get; set; }
    public virtual Organization Organization { get; set;}
}

And similar as above for User.

So anyways, in my Controller right now I am doing something to the effect of:

user = await _context.User.Include(c => c.Organization).Where(c => c.UserID == thisUserID).FirstOrDefaultAsync();


itemList = await _context.Items
    .Include(c => c.Organization)
    .Select(Some stuff)
    .Where(c => c.Organization == user.Organization)
    .ToListAsync();

Is there a way to avoid these unnecessary includes and just compare the actual IDs? I don't need any other info about the organization so I don't want an extra Include. I would also like to avoid doing extra migrations to add something like "public int OrganizationID" to each model; plus I'd need to awkwardly change the name of that so that it doesn't conflict with the existing FK in the table (which is also called OrganizationID).

In my mind, the ID is already present when I look at the tables with SQL Server Management Studio, since that's literally what the foreign key is - I just need to grab the value of the foreign key instead of having it assume I want the entire Organization object. But I am not a SQL expert.

Edited for clarity and a bit more detail on what the models look like.

Answer 1

In your model design, User and Item all have a property OrganizationID, So if you don't want to use include(xxx), You can use OrganizationID as a query condition.

//select User's fk

var userfk = _context.User
.Where(c => c.UserID == thisUserID)
.Select(c => c.OrganizationID)
.FirstOrDefaultAsync();

//if item and user has the same fk,so they are in the same Organization

List<string> itemList = _context.Items
.Where(c => c.OrganizationID == userfk)
.Select(c => c.ItemName)
.ToListAsync();