-1

I have a web page where the user can restrict results based on three different multi select choices (please see attached picture).

A bit of background. Documents have many sectors, companies and analysts assigned to them. My EF objects have navigation properties which means I don't need to write explicit JOINS to get the data I need.

My problem is I can't construct the LINQ query I need to get the results I need. Using SQL it would be simple and I could easily use a combination of JOINS and WHERE IN ('Blah', 'Another blah'). In my controller I have the following code:

public JsonResult FilterResearch(FilterResearchModel filterResearch)
{
        var db = new Context();

        // home model
        var model = new HomeModel();

        var selectedSectors = from s in db.Sectors
                              where filterResearch.SelectedSectors.Contains(s.Name)
                              select s;

        var selectedCompanies = from c in db.Companies
                              where filterResearch.SelectedCompanies.Contains(c.Name)
                              select c;

        var selectedAnalysts = from a in db.Analysts
                              where filterResearch.SelectedAnalysts.Contains(a.Name)
                              select a;

        var filteredResults = from d in db.Documents
                      where selectedSectors.Contains(d.Sectors)
                      select d;

FilterResearch.Selected"Something" are string arrays. My "filteredResults" query is what should contain the filtered documents I plan to return.

EDIT Some people have commented on the fact I'm not being clear. I'm trying to filter my documents based on 3 string arrays. Each of these string arrays are navigation properties in the "document" object. On the client the user has three multi select controls so each array can have more than one element. Now they can choose any of the three and choose as many options as they wish.

THIS IS THE PROBLEM When I compile it I get the following error: "cannot convert from 'System.Linq.IQueryable' to 'System.Linq.ParallelQuery>"

EDIT AGAIN Picture of error included. It occurs on "where selectedSectors.Contains(d.Sectors)"enter image description here

I have checked:

with little luck. Is there a way where I can just say "filter the documents based on the companies AND sectors AND analysts?

Community
  • 1
  • 1
Arminder Dahul
  • 194
  • 5
  • 23
  • 3
    You haven't shown us what you're actually doing with these queries, which is where your problem clearly lies. – Servy May 18 '15 at 16:11
  • Are you looking for `.Where(o => o.Companies.Contains("...") && o.Sectors.Contains("...") || o.Analyst.Contains("..."))`? Not sure what exactly your conveying. – Greg May 18 '15 at 16:12
  • @Servy Really? The "filteredResult" query which resulted in an error means I didn't show what I'm "actually doing"? – Arminder Dahul May 18 '15 at 19:04
  • @Greg The problem with your statement is that I need to pass in multiple strings into .Contains(). It's a many to many relationship for all objects in my scenario. – Arminder Dahul May 18 '15 at 19:06
  • @ArminderDahul That is correct. None of the code that you've should is *capable* of resulting in the error shown; the error is the result of some code that you haven't shown, so, since we can't see it, we can't tell you what you did wrong when writing it. – Servy May 18 '15 at 19:07
  • @Servy So the picture helped clarify? – Arminder Dahul May 18 '15 at 19:36
  • @ArminderDahul Like I said, the code that you've shown isn't sufficient to reproduce the error. The image isn't changing that. You don't have a reproducible example. – Servy May 18 '15 at 19:47
  • @ArminderDahul What is the datatype of filterResearch.SelectedSectors? `Array`, `IList`, `List`, `IEnumerable`, or `IQueryable`? At this point I'm guessing it's either an IQueryable, or the Sectors table doesn't have a property called `Name` – Robert McKee May 18 '15 at 20:06
  • @Servy My codebase is too large to include here. I could have created an entire class library for you but I would have thought the line that caused the error would be enough. Clearly not. – Arminder Dahul May 18 '15 at 21:48
  • @RobertMcKee The type is IQueryable. This object has the SectorId and Name properties. – Arminder Dahul May 18 '15 at 21:49
  • @ArminderDahul That would be your problem. Your code would work if it was an `IEnumerable`, but not `IQueryable`. Updated my answer below to work with `IQueryable`. There is likely a shorter way, but I prefer it as is for maintainability. – Robert McKee May 19 '15 at 04:31

1 Answers1

2

Maybe I'm misunderstanding this (and I don't know your full object model) but wouldn't the following work...

    var filteredResults = from d in db.Documents
                         where d.Sectors.Exists(sect => filterResearch.SelectedSectors.Contains(sect.Name))
                            && d.Companies.Exists(comp => filterResearch.SelectedCompanies.Contains(comp.Name))
                            && d.Analysts.Exists(anyst => filterResearch.SelectedAnalysts.Contains(anyst.Name))
                        select d;

Edit: Just to add, I haven't tried this in Visual Studio so it may not compile. Maybe you'd need to use a function other than Exists - Any perhaps - but I'm sure you get the gist of what I'm thinking.

amcdermott
  • 1,565
  • 15
  • 23