3

I have the following schema:

public class Post {
    public string Id {get;set;}
    public string Content {get;set;}
    public AppUser AppUser {get;set;}
    public List<PostTag> PostTags {get;set;}
}

public class Tag {
    public string Id {get;set;}
    public string Name {get;set;}
    public List<PostTag> PostTags {get;set;}
}

public class PostTag
{
    public string PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}

With the following db relationships:

builder.Entity<PostTag>()
    .HasKey(x => new { x.PostId, x.TagId });

builder.Entity<PostTag>()
    .HasOne(st => st.Post)
    .WithMany(s => s.PostTags)
    .HasForeignKey(st => st.PostId);

builder.Entity<PostTag>()
    .HasOne(st => st.Tag)
    .WithMany(s => s.PostTags)
    .HasForeignKey(st => st.TagId);

I'm writing a query to get all the Posts that are linked to a specific Tag, based on the provided TagId.

First I get all the posts using:

var postsQuery = _ctx.PostTag
                    .Include(st => st.Post)
                    .Where(st => st.TagId == {provided TagId})
                    .Select(st => st.Post);

Since I want to include some further data to each post, I do:

var postsWithExtraData = postsQuery
                            .Include(s => s.AppUser)
                            .Include(s => s.PostTags)
                            .ThenInclude(st => st.Tag)
                            .OrderByDescending(s => s.TotalVoteCount)
                            .ToListAsync();

But the query breaks on the first .Include with this error:

EF Core “InvalidOperationException: Include has been used on non entity queryable”

Why is this happening and how can I fix it?

EDIT: Potential solution that I got to work:

var posts = _ctx.PostTag
                    .Include(st => st.Post)
                    .ThenInclude(s => s.AppUser)
                    .Include(st => st.Post)
                    .ThenInclude(s => s.PostTags)
                    .ThenInclude(st => st.Tag)
                    .Where(st => st.TagId == request.TagId)
                    .Select(st => st.Post)
                    .ToList();

Would love to know if this is a good approach or not.

7
  • Where does AppUser come from? It's not in any of the code you show. Commented Jul 14, 2020 at 5:35
  • Whoops, missed it. It's part of Post. I've updated my original post. Commented Jul 14, 2020 at 5:37
  • postsQuery is a IEnumerable<Post> - at that point, you're no longer in LINQ-to-Entities (you're not querying the database), so you can't call the .Include method. Commented Jul 14, 2020 at 6:24
  • It's being shown as IQuerable<Post> for me. Commented Jul 14, 2020 at 6:29
  • 1
    Correct, but the point still stands - once you've called the .Select() method, you can't use Include anymore - Select tells EF to translate the LINQ expression to SQL. I wish this had more details, but basically: learn.microsoft.com/en-us/ef/core/querying/client-eval Commented Jul 14, 2020 at 6:36

2 Answers 2

4

If you change the query so that it no longer returns instances of the entity type that the query began with, then the include operators are ignored.

So your code is giving error. You can do like below where you can use Includes in your first query and use OrderByDescending ToListAsync to next.

var postsQuery = _ctx.PostTag
                .Include(st => st.Post)
                    .ThenInclude(s => s.AppUser)
                .Include(st => st.Post)
                    .ThenInclude(s => s.PostTags)
                        .ThenInclude(st => st.Tag)
                .Where(st => st.TagId == {provided TagId})
                .Select(st => st.Post);
                
var postsWithExtraData = postsQuery                            
                        .OrderByDescending(s => s.TotalVoteCount)
                        .ToListAsync();
Sign up to request clarification or add additional context in comments.

3 Comments

This is pretty much what I did.
no longer returns instances of the entity type that the query began with - so in my original example this would've been PostTag?
Please check this fiddle dotnetfiddle.net/0Lp6XD. The statement I've mentioned in answer was from Loading Related Data but it is updated and no longer available. And as per fiddle it is working now so Microsoft might have updated its working for new newer versions.
0

First, you should write Include and ThenInclude and then write your Condition

var postsWithExtraData = _ctx.PostTag
                            .Include(st => st.Post)
                            .Include(s => s.AppUser)
                            .Include(s => s.PostTags)
                            .ThenInclude(st => st.Tag)
                            .Where(st => st.TagId == {provided TagId})
                            .OrderByDescending(s => s.TotalVoteCount)
                            .ToListAsync();

4 Comments

What's in your postsQuery?
You can't do .Include(s => s.AppUser) because s is StoryTag
I've taken your query and improved it. Looks like it's working now. See the original post.
My goal was not to write the correct query I just wanted to explain the cause of the problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.