CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
LaserBeak
LaserBeak

Reputation: 3285

How to check for multiple values in (.Where) statement in order to select same types but with varying values

        var results = _db.CatSchemas.FirstOrDefault(s => s.TypeName == "Programs")
            .Categories.Where(c => c.FilterValToCatMaps.Where
                (fm => fm.FilterValue.Value == age && fm.FilterValue.Value == ProgType).Count() > 0)
                    .Select(c => c.Category1);

If I remove && fm.FilterValue.Value == ProgType the query works but then of course only returns the results filtered by age only.

I require to select only the Categories which have FilterValToCatMaps that have FilterValue.Value equal to that specified in the parameter age and ProgType. FilterValues can only contain one Value and all related FilterValToCatMaps and respective FilterValues should be present in the required category.

The (fm => fm.FilterValue.Value == age && fm.FilterValue.Value == ProgType) statement I presume tries to check if the two parameter values are true for the same FilterValToCatMap.FilterValue and not check if other FilterValToCatMap.FilterValue related to the category contain the next parameter value.

FilterValToCatMaps - Table

LineID | FilterValueID | CatID

FilterValues - Table

LineID | Value | DisplayName | FilterID

Upvotes: 0

Views: 182

Answers (2)

Nick Butler
Nick Butler

Reputation: 24433

If you want a list of categories as the result, you should generally start with _db.Categories.

Also, because you require both FilterValues, not either or, you can split the clause into 2 Where clauses.

var results = _db.Categories
  .Where( c => c.CatSchema.TypeName == "Programs" )
  .Where( c => c.FilterValToCatMaps.Any( fm => fm.FilterValue.Value == age ) )
  .Where( c => c.FilterValToCatMaps.Any( fm => fm.FilterValue.Value == ProgType ) )

Upvotes: 1

Ivo
Ivo

Reputation: 8372

I think you can do this:

 var results = _db.CatSchemas.FirstOrDefault(s => s.TypeName == "Programs")
        .Categories.Where(c => c.FilterValToCatMaps.Any
            (fm => fm.FilterValue.Value == age && fm.FilterValue.Value == ProgType));

So it will return the categories that have FilterValToCapMaps with any that query.

BTW, fm.FilterValue.Value == age && fm.FilterValue.Value == ProgType is only true where age and ProgType are equals.

Upvotes: 1

Related Questions