Selecting from several many-to-many relationships in EF Core programmatically

I have a set of highly complex many-to-many relationships that I want to query at the same time, with some additional complexities including compound keys and not always wanting to search all those relationships. It's structured roughly like this:

[PrimaryKey(nameof(UserId1), nameof(UserId2))]
public class User {
    public int UserID1;
    public int UserID2;
    public List<PostStar> PostStar;
    public List<Post> PostsMentioning;
    public List<Post> PostsReblogged; 
    // misc data
    public string UserName;
}

public class Post {
    public class PostID;
    public List <PostStar> InPostStar;
    public List<User> MentionsUsers;
    public List<User> RebloggedByUser;

    // other misc. data
    public int RandomValue;
}

public class PostStar {
    public Post Post;
    public User User;
    public bool MadeAsPromotional;
    // Thrown in largely so it has *some* primary key
    public int Id;
}

In my DBContext, I establish a many-to-many relationship between User and Post based on PostsMentioning to MentionsUsers, a many-to-many relationship between User and Post based on PostsReblogged to RebloggedByUsers, and an odd double-up pair of many-to-one relationships relating User to PostStar and then PostStar to Post. I don't simply call UsingEntity to establish that many-to-one-to-many because MadeAsPromotional isn't auto-set, it's too complex for that and requires some application logic, so I have an additional table specifically just for PostStar. The end result is the following set of tables officially defined in the DBContext:

• Users (primary key: compound of UserID1 and UserID2)
• Posts (primary key: PostID)
• PostStars (primary key: Id)

Plus the following auto-generated tables:

• UserToPost1 (primary key: compound of UserID1, UserID2, PostID)
• UserToPost2 (primary key: compound of UserID1, UserID2, PostID)

What I'm doing is trying to build a programmatic search function, mainly for Users. Let's say I want to find all users who are in any way associated with Posts that have a RandomValue over 5. I need to join multiple tables, across the many-to-many relationships created, including the table of PostStar and several tables that are auto-created by Entity Framework Core to support a many-to-many relationship.

To make it worse, I don't always want to search all of those. I may not want to include Posts that a User was just mentioned in. This necessitates defining the system using LINQ IQueryables, not just constructing a SQL query, as far as I know. Also, my data sets are big enough (and, potentially, recursive/interconnected enough) that I'd really rather not eager-load the whole thing. Honestly, I'm not sure where to start. Can anyone point out a flaw in my assumptions or tell me how to begin?

Here's an example of two queries I might want to programmatically construct, written in SQL because I don't know how to construct them in EF Core/LINQ (hence the question). First, for finding all users that have ever been mentioned in a promotional PostStar:

SELECT Users.UserName, Post.PostId, Post.RandomValue, PostStar.Id
FROM Users
INNER JOIN UserToPost1 ON ((Users.UserID1=UserToPost1.UserID1) AND (Users.UserID2 = UserToPost1.UserID1))
INNER JOIN Posts ON UserToPost1.PostID=Posts.PostID
INNER JOIN PostStars ON Posts.PostID = PostStars.PostID
WHERE PostStars.MadeAsPromotional="TRUE"
ORDER BY Users.UserID1, Users.UserID2;

Second, for finding all users that have ever been reblogged by a specific user in a PostStar:

SELECT DISTINCT UsersB.UserName
FROM Users UsersA
INNER JOIN PostStars ON ((UsersA.UserID1=PostStars.UserID1) AND (UsersA.UserID2=PostStars.UserID2))
INNER JOIN UserToPost2 ON PostStars.PostID=UserToPost2.PostID
INNER JOIN Users UsersB ON ((UserToPost2.UserID1=UsersB.UserID1) AND (UserToPost2.UserID2=UsersB.UserID1))
WHERE UsersB.UserID1=1 AND UsersB.UserID2=2;

You can see how those two are different enough that I can't simply substitute things in a simple SQL command. I need the ability to make complex FROM and WHERE clauses, and ideally, I want to do that via LINQ. How do I proceed?

Answer

When working with EF it's generally best to leverage IQueryable rather than worrying about abstracting EF or guarding exposing situational data within the entities themselves which it sounds slightly like what you might be considering. Either that or you want some associated data without eager loading ALL data:

If you want the reblogged users associated with posts with a value > 5, something like:

var users = await _context.Posts
   .Where(p => p.RandomValue > 5)
   .SelectMany(p => p.RebloggedByUser.User)
   .ToListAsync();

Most often when it comes to reading data though, I recommend not worrying about loading entire entities and some/all of their relatives and instead projecting using .Select() to build the minimal structure you need at the time. This can cover filtering related data, flattening, or whatever is needed.

Loading the entities themselves is reserved for things like an Update action on a single entity including relations if necessary.