CODEWITHSUNDEEP
c#linqentity-framework
Farhad-Taran
Farhad-Taran

Reputation: 6512

how to filter entity type framework object by its child object value properties?

I have an entity framework object called batch, this object has a 1 to many relationship to items.

so 1 batch has many items. and each item has many issues.

I want to filter the for batch items that have a certain issue code (x.code == issueNo). I have written the following but Im getting this error:

        items = batch.Select(b => b.Items
                     .Where(i => i.ItemOrganisations
                     .Select(o => o
                     .Issues.Select(x => x.Code == issueNo))));

Error 1:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<System.Collections.Generic.IEnumerable<bool>>' to 'bool'

Error 2:

Cannot convert lambda expression to delegate type 'System.Func<Ebiquity.Reputation.Neptune.Model.Item,bool>' because some of the return types in the block are not implicitly convertible to the delegate return type

Upvotes: 2

Views: 1357

Answers (5)

Dax Fohl
Dax Fohl

Reputation: 10781

You're getting lost in lambdas. Your LINQ chains are all embedded in each other, making it harder to reason about. I'd recommend some helper functions here:

static bool HasIssueWithCode(this ItemOrganization org, int issueNo)
{
    return org.Issues.Any(issue => issue.Code == issueNo);
}

static bool HasIssueWithCode(this Item items, int issueNo)
{
    return items.ItemOrganizations.Any(org => org.HasIssueWithCode(issueNo));
}

Then your answer is simply and obviously

var items = batch.Items.Where(item => item.HasIssueWithCode(issueNo));

If you inline these functions, the result is the exact same as manji's (so give manji credit for the correct answer), but I think it's a bit easier to read.

Upvotes: 0

Dax Fohl
Dax Fohl

Reputation: 10781

If I understand correctly, you're trying to select through multiple layers of enumerables. In those cases you need SelectMany which flattens out the layers, not Select. LINQ's syntax sugar is made specifically to make SelectMany easier to reason about:

var items = from item in batch.Items
            from org in item.ItemOrganizations
            from issue in org.Issues
            where issue.Code == issueNo
            select item;

The compiler translates that into something like this:

var items = batch.Items
    .SelectMany(item => item.ItemOrganizations, (item, org) => new {item, org})
    .SelectMany(@t => @t.org.Issues, (@t, issue) => new {@t, issue})
    .Where(@t => @t.issue.Code == issueNo)
    .Select(@t => @[email protected]);

You can always wrap this in a Distinct if you need to avoid duplicate items:

var items = (from item in batch.Items
            from org in item.ItemOrganizations
            from issue in org.Issues
            where issue.Code == issueNo
            select item).Distinct();

Upvotes: 2

Nick
Nick

Reputation: 4212

items =  from b in batch.Include("Items")
         where b.Items.Any(x=>x.Code==issueNo)
         select b;

Upvotes: 0

evanmcdonnal
evanmcdonnal

Reputation: 48076

It's hard to tell what you're trying to do based on your code but I think you're looking for something like this;

var issue = batch.Select(b => b.Items).Select(i => i.Issues).Where(x => x.Code == issueNo).Select(x => x).FirstOrDefault();

The above query will return the first issue where the Issues Code property is equal to issueNo. If no such issue exists it will return null.

One problem (the cause of your first error) in your query is that you're using select like it's a where clause at the end of your query. Select is used to project an argument, when you do Select(x => x.Code == issueNo) what you're doing is projecting x.Code to a bool, the value returned by that select is the result of x.Code == issueNo, it seems like you want that condition in a where clause and then you want to return the issue which satisfies it which is what my query is doing.

Upvotes: 0

manji
manji

Reputation: 47968

Select extension method needs a lambda expression that returns a boolean, but the inner o.Issues.Select returns an IEnumerable of boolean to the outer Select(o => o which result in the exception you're getting.

Try using Any instead which verifies that at least one element verifies the condition:

    items = batch.Select(
               b => b.Items.Where(
                             i => i.ItemOrganisations.Any(
                                     o => o.Issues.Any(x => x.Code == issueNo)
                                  )
                    )
            );

Upvotes: 3

Related Questions