clevergrant clevergrant - 4 years ago 61
C# Question

MVC 5 C# Search a Many-to-Many Relational Database

I have a database with academic publications, which all have multiple authors. I have a page that displays a paged partial view of these publications, with multiple filtering options, designed to use multiple filters simultaneously.

I've been able to implement all of the other filtering options just fine, but the one that's giving me trouble is the filter by author(s), where users can type in an author's name and it will return any publications from that author.

It's a code-first database, and the publications model contains this:

public virtual ICollection<Author> Authors { get; set; }


And likewise, in the Author model:

public virtual ICollection<Publication> Publications { get; set; }


Right now, I have the controller returning a partial view like so:

Models.Author[] auth = db.Authors.Where(p => p.FullName.ToLower()
.Contains(searchAuthor).ToLower())).ToArray();

return PartialView("_PublicationList", await db.Publications
.Where(p => p.Authors.ToList().Contains(auth[0])).ToListAsync());


where "searchAuthor" is the value in the text box from the view (I'm leaving out the other filters for simplicity).

As you can see, this only searches the first author that matches. If someone types "Michael", and there are more than one Michael who have contributed, it will only show search results for the first Michael.

So, how should I go about this? I've been searching Google for days, and I haven't found a solution for returning a partial view with multiple publications and multiple authors. Anyone have a solution?

Answer Source

That answer was close, and it led me to find out that I could just use .Select() to find each author by whether their names contained the string. So:

... .Where(p => p.Authors.Select(a => a.name).Contains(searchAuthor)) ...

Unfortunately, for some reason, this only returns those publications where the author's name matches EXACTLY, rather than just contains the string searchAuthor... So, if anybody has any advice for fixing that, feel free to share!

EDIT: Okay, no, I was wrong. The reason that was happening was because I was using .Select() instead of .Any(), as you said. Changing it fixed everything. So the final code is:

... .Where(p => p.Authors.Any(a => a.name.Contains(searchAuthor))) ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download