Query which returns results only if all (non-primitive) values from a compared enumerable are contained in the target enumerable

Question

We have a collection of entities of type called Unit, collection of entities of type UnitProp and a collection of entities of type called Prop defined like this (simplified):

class Unit
{
    int ID;
    ICollection<UnitProp> UnitProps;
}

class UnitProp
{
    int ID;
    int UnitID;
    Unit Unit;
    int PropID;
    Prop Prop;
}

class Prop
{
    int ID;
    string Description; 
}

We have a List of Props (called RequiredProps) we need to query the collection with. We want to return the collection of Units which satisfy the condition of having all Props specified in RequiredProps.

I wrote the query like this:

var result = ctx.Units
    .Where(x => RequiredProps.AsEnumerable()
        .Except(x.UnitProps.Select(y => y.Prop))
        .Count() == 0)
    .ToList();

Of course, this is Linq to Entities so the query will throw an exception: Unable to create a constant value of type 'Prop'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I also tried this:

var result = ctx.Units
    .Where(x => RequiredProps.Select(y => y.ID)
        .Except(x.UnitProps
            .Select(y => y.Prop)
            .Select(y => y.ID))
        .Count() == 0)
    .ToList();

...but it yielded the same exception.

Suggestions?

Raphaël Althaus · Accepted Answer · 2012-06-14T12:31:24.990

2

var requiredIds = RequiredProps.Select(y => y.ID);

var result = ctx.Units
                .Where(m => !requiredIds
                    .Except(m.UnitProps.Select(x => x.Prop.ID)))
                    .Any())
                 .ToList();

edited Jun 14 '12 at 12:31

answered Jun 14 '12 at 12:26

Raphaël Althaus

59,727
6
96
122

I couldn't edit the answer because the edit must contain at least 6 chars, instead of `m.UnitProps.Select(y => y.ID)` there should be `m.UnitProps.Select(y => y.PropID)` and thank you for your answer! – Dejan Janjušević Jun 14 '12 at 12:34
yep, I found this at the same time, it's edited (PropID or Prop.ID, think it's the same). – Raphaël Althaus Jun 14 '12 at 12:35

Query which returns results only if all (non-primitive) values from a compared enumerable are contained in the target enumerable

1 Answers1