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?

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?

• c#
• sql
• entity-framework-core

• An actual example of what you query you'd like would help, it's rather vague at the moment. Why can't you just use .Include like db.Posts.Where(p => p.RandomValue > 5).Include(p => p.InPostStar).Include(p => p.MentionsUser).Include(p => p.RebloggedByUser) – Charlieface Commented Mar 26 at 20:53
• So why is a SQL solution not viable here? When you've made your model too complex, you will usually need a few optimized SQL queries for things like this. But it would help to see all of the tables and FK linking fields involved – Chris Schaller Commented Mar 26 at 21:59
• A SQL solution is not viable here because the search is intended to be programmatic, and thus needs to be editable on the fly. For example, depending on what the user inputs, I might need to search for all Users who have reblogged Posts that are in PostStars that have MadeAsPromotional=true; then I might need to search for all Users who have reblogged Posts that are in any PostStar associated with a specific User. I don't want to write out every possible search (would take WAY too long) and I don't think that using SQL will work around all the if statements required. – Jacob Commented Mar 27 at 15:33
• There's no such thing as a "primary compound key". Compound keys come about from "joining" multiple "other" keys; primary and / or secondary. The compound key implies "meaning"; whereas the best primary keys, are unique nonsense keys. An ER diagram at this point might make some sense. The User class (in this case) is a "relation", and not an "entity"; the "Lists of relations" make little sense compared to "lists of USER ID'S) – Gerry Schmitz Commented Mar 31 at 16:51
• This question is similar to: Dynamic filtering in EF core. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Chin. Udara Commented Apr 2 at 6:27

2 Answers 2

You mentioned that in your DbContext, you have something like:

DbSet<User> Users
DbSet<Post> Posts
DbSet<PostStar> PostStars`

EF Core automatically generates the tables (e.g. UserToPost1, UserToPost2) for your many-to-many relationships:

I assume:

modelBuilder.Entity<User>()
    .HasMany(u => u.PostsMentioning)
    .WithMany(p => p.MentionsUsers);


modelBuilder.Entity<User>()
    .HasMany(u => u.PostsReblogged)
    .WithMany(p => p.RebloggedByUser);

PostStar has MadeAsPromotional (also Id primary key). It is modeled as two one‐to‐many relationships (User -> PostStar) and (Post -> PostStar).

As long as User, Post, and PostStar have correct navigation properties, EF Core knows how to join them on query translation.

When you see yourself writing JOIN on auto‐gen tables, EF enables usage of navigation properties ( u.PostsMentioning, p.InPostStar, etc.).

That is typically the cleanest approach.

A. IQuerayble

Because your search is focused around users, you typically start with context.Users.AsQueryable()

IQueryable<User> query = context.Users;

Then you can use filters if you decide to include or exclude certain relationships.

For example:

• If you only care about users who have a PostStar that is promotional and has some property, you do:

query = query
.Where(u => u.PostStar.Any(ps => ps.MadeAsPromotional));

If you only care about users who appear in a PostsMentioning where RandomValue > 5, you do:

query = query
.Where(u => u.PostsMentioning.Any(p => p.RandomValue > 5));

If you want an OR across multiple relationships, i.e. “Users associated wit post RandomValue > 5"

you simply combine with ||, e.g.:

IQueryable<User> query = context.Users
    .Where(u =>
        u.PostStar.Any(ps => ps.Post.RandomValue > 5)
        || u.PostsMentioning.Any(p => p.RandomValue > 5)
        || u.PostsReblogged.Any(p => p.RandomValue > 5)
    );

EF Core "sees" the navigation properties, figures out it needs to join to the many‐to‐many tables and the PostStars table, and generates the SQL query. (and queries.)

Dynamic search

If your search criteria is dynamic ("sometimes filter on mentions, sometimes on reblogs, sometimes on PostStars, ... "), you can compose the Where clauses:

IQueryable<User> query = context.Users;

if (includeMentions)
{
    query = query.Where(u => u.PostsMentioning.Any(p => p.RandomValue > someValue));
}

if (includeReblogs)
{
    query = query.Where(u => u.PostsReblogged.Any(p => p.RandomValue > someValue));
}

if (includePromotionalStars)
{
    query = query.Where(u => u.PostStar.Any(ps => ps.MadeAsPromotional));
}

Your SQL in LINQ

Your 1st SQL was:

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=1
ORDER BY Users.UserID1, Users.UserID2;

Because you have navigations:

• User.PostsMentioning -> many-to-many link to Post
• Post.InPostStar -> many-to-one link to PostStar

Flatten it out w. method syntax/query syntax.

One way is query syntax with multiple from clauses:

var result =
    from u in context.Users
    from p in u.PostsMentioning
    from ps in p.InPostStar
    where ps.MadeAsPromotional
    orderby u.UserID1, u.UserID2
    select new
    {
        UserName = u.UserName,
        PostId = p.PostID,
        RandomValue = p.RandomValue,
        PostStarId = ps.Id
    };

var list = result.ToList();

EF translates from clauses into necessary JOIN statements. We just used the navigation property u.PostsMentioning. EF automatically joins through table and Posts.

Your 2nd example query:

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.UserID2
WHERE UsersB.UserID1=1 AND UsersB.UserID2=2;

• Find "reblogger" user:

var reblogger = context.Users
.SingleOrDefault(u => u.UserID1 == 1 && u.UserID2 == 2);

• Use navigation to find all posts that reblogger has made: (if you loaded reblogger. But if not loaded yet, a direct query is possible)

var rebloggedPostsQuery = context.Posts
    .Where(p => p.RebloggedByUser.Any(u => 
        u.UserID1 == reblogger.UserID1 && 
        u.UserID2 == reblogger.UserID2
    ));

• With these posts, figure out which users are linked via PostStar or any other relationship.

Eg., if you mean "Find all users who have any PostStar on those same posts":

var usersAQuery =
    from p in rebloggedPostsQuery
    from ps in p.InPostStar
    select ps.User;

var usersA = usersAQuery.Distinct();

You can do something like:

var query =
    from p in context.Posts
    from ps in p.InPostStar
    where p.RebloggedByUser.Any(u => u.UserID1 == 1 && u.UserID2 == 2)
    select ps.User;

var users = query
    .Distinct()
    .Select(u => u.UserName)
    .ToList();

Depending on what "reblogged by a specific user in a PostStar" means, you move the navigations around (maybe you want User.PostStar -> .Post.RebloggedByUser, etc.).

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.