Complex LINQ query - multiple table relationships

Question

I have a books database, which has an ICollection of authors. I want to return the author object based on the AuthorId using LINQ.

Book db

int BookId
string Name { get; set; }
public ICollection<Author> Authors { get; set; }

Author db
int AuthorId
string Name
ICollection<Quote> Quotes { get; set; }
ICollection<Penname> Pennames { get; set; } - Edit: Added for clarity

I have tried:

var test = _context.Book.Include(x => x.Authors).Include("Authors.Quotes")
                    .Select(y => y.Authors)

Which gave me:

EntityQueryable<ICollection<Authors>>
[0] {HashSet<Author>} [0]{Author} [1]{Author} [3]{Author} 
[1] {HashSet<Author>} [0]{Author} [1]{Author} 
[2] {HashSet<Author>} [0]{Author} [1]{Author}

I just can't figure out how to iterate though the Authors in the Authors list. Something like the below:

var id = 2

var test = _context.Book.Include(x => x.Authors).Include("Authors.Quotes")
                    .Select(y => y.Authors.Select(x => x.Author).Where(x => x.AuthorId == id))

If I ever do a major update I might use elastic...

Update @Marko Papic:

Thanks. Weirdly if I use the below to get a list of books with authors, I get the quotes and pennames lists populated as I expect

var test = _context.Book.Include(x => x.Authors)
                   .ThenInclude(x => x.Quotes)
                   .Include(x => x.Authors)
                   .ThenInclude(x => x.Pennames)

However if I use SelectMany, then the quotes and pennames end up as null

var test = _context.Book.Include(x => x.Authors)
                   .ThenInclude(x => x.Quotes)
                   .Include(x => x.Authors)
                   .ThenInclude(x => x.Pennames)
                   .SelectMany(x => x.Authors).Where(x => x.AuthorId == id);

Author myauthor
int AuthorId = 2
string Name = "Bob"
ICollection<Quote> Quotes = null
ICollection<Penname> Pennames = null

I ran dotnet ef --version and got Entity Framework Core .NET Command-line Tools 2.2.4-servicing-10062. This is with ASP.NET Core 2.2 — Hawke, Jun 26 '19 at 15:32
If you want to return the `Author` object with a particular `AuthorId`, why bother with `Books`? Just use `_context.Authors.First(a => a.AuthorId == AuthorId)`. — NetMage, Jun 26 '19 at 17:33
If what you are trying to do is filter the `Authors` collection attached to a `Book`, you can't do that. See [this question](https://stackoverflow.com/q/39636952/2557128) for example. — NetMage, Jun 26 '19 at 17:36

score 0 · Answer 1 · answered Jun 26 '19 at 14:46

0

You can use SelectMany:

var test = _context.Book.Include(x => x.Authors).ThenInclude(x => x.Quotes)
                    .SelectMany(x => x.Authors).Where(x => x.AuthorId == id);

answered Jun 26 '19 at 14:46

Marko Papic

1,846
10
23

ocuenca · Answer 2 · 2019-06-26T15:44:56.317

0

I think the includes are ignored because the result type of the query is not the same of the type of your dbset with when you start, from the documentation:

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.

I assume the relationship between Books and Authors is many to many, if that is the case then this is how I would do your query:

var query=_context.Authors.Include(a=>a.Books)
                          .Include(a=>a.Quotes)
                          .Include(a=>a.Pennames)
                          .Where(a=> a.AuthorId == id);

edited Jun 26 '19 at 15:44

answered Jun 26 '19 at 15:35

ocuenca

38,548
11
89
102

`Authors` doesn't appear to have a `Books` collection? – NetMage Jun 26 '19 at 17:32
Well, I think he is omitting that, the OP also doesn't show that `Authors` has `Pennames` and he is using it in the query. That should be just part of the model – ocuenca Jun 26 '19 at 17:36
Im confused - I added pennames to the db model above to make it clearer (it is the same as a quote). Books contains a collection of authors, which contains a collection of quotes and pennames. – Hawke Jun 27 '19 at 09:09
Do you know if the relationship between Authors and Books is many to many? If that is the case how is represented in your model? Through a junction table or directly between both entities? – ocuenca Jun 27 '19 at 13:03

Complex LINQ query - multiple table relationships

2 Answers2