C# - LINQ to EF - filter multiple child collections

Question

Simplified Data Model:

Customer
--------------
CustomerId (int)
IsActive (bool)
... (a bunch of other fields)
Applications (List<Application>)

Application
--------------
ApplicationId (int)
IsActive (bool)
... (a bunch of other fields)
Member (List<Member>)

Member
--------------
MemberId (int)
IsActive (bool)
... (a bunch of other fields)

Given a Customer object (searchEntity), I want to be able to find if any other records in the DB have specific fields that match the given object. The query that I've created that I thought would return the correct result is:

foreach (var memberEntity in searchEntity.MEMBER_T.Where(m => m.ISACTIVE == 1).ToList())
{
    bool result = Entities.CUSTOMER_T.Where(c =>
        c.CUSTOMERID != searchEntity.CUSTOMERID && c.ISACTIVE == 1 && c.APPLICATION_T.Where(a =>
            a.ISACTIVE == 1 && a.APPLICATIONSTATUSID == 4 && a.MEMBER_T.Where(m =>
                m.ISACTIVE == 1 && m.FIRSTNAME == memberEntity.FIRSTNAME && m.LASTNAME == memberEntity.LASTNAME
            ).Any()
        ).Any()
    ).Any();
}

This should return true if the searchEntity has a member that matches First and Last Name of a Member in the DB that belongs to an Application with ApplicationStatus == 4, and that application belongs to a Customer with CustomerId not equal to the searchEntity's CustomerId.

The first issue I came across was Oracle Error ORA-00904: "Extent1"."CUSTOMERID": invalid identifier. I noticed if I modified my query to exclude the a.MEMBER_T.WHERE(...) that it would execute without any errors - except that's the most crucial part of that query.

Searching StackOverflow produced some suggestions (LINQ - filter child collection, LINQ - filter collection inside collection) to use .Select to create a new object and assign the filtered child collection to the new object, as below:

bool result = Entities.CUSTOMER_T.Where(c =>
    c.CUSTOMERID != searchEntity.CUSTOMERID && c.ISACTIVE == 1
).Select(c => new Database.CUSTOMER_T
{
    CUSTOMERID = c.CUSTOMERID,
    APPLICATION_T = c.APPLICATION_T.Where(a =>
        a.ISACTIVE == 1 && a.APPLICATIONSTATUSID == 4
    ).Select(a => new Database.APPLICATION_T
    {
        APPLICATIONID = a.APPLICATIONID,
        ADDITIONALASSISTANCE_T = a.MEMBER_T.Where(m =>
            m.ISACTIVE == 1
            && m.FIRSTNAME == memberEntity.FIRSTNAME
            && m.LASTNAME == memberEntity.LASTNAME
        )
    }
    ).Where(a => a.ADDITIONALASSISTANCE_T.Any())
}
).Where(c => c.APPLICATION_T.Any()).Any();

This resulted in the error The entity or complex type 'Entities.CUSTOMER_T' cannot be constructed in a LINQ to Entities query. I tried to fix this using a DTO (The entity cannot be constructed in a LINQ to Entities query), but I received the same ORA-00904 error as above.

I'm not sure what else to do at this point. Any suggestions on things to try would be greatly appreciated. Also, I'm new to posting questions here so if I missed something let me know and I'll update my question.

Instead of using `Where(`pred`).Any()` what happens if you just use `Any(`pred`)` ? — NetMage, May 15 '18 at 22:59
@NetMage The only place I use `Where(`pred`).Any()` is on the last line - I updated to use `Any(`pred`)`, but it still returns the `"CUSTOMERID": invalid identifier` error. — estoner, May 18 '18 at 14:00
Your first example has three `.Where().Any()` and your last example has at least two??? — NetMage, May 18 '18 at 18:42
The second example (the one I changed) only has one instance of `Where(`pred`).Any()`. There are other instances of `Where(`pred`.Any())` (notice parentheses placement), which is a little different, at least to my current understanding of the functionality of `.Where()` and `.Any()` — estoner, May 18 '18 at 18:54

C# - LINQ to EF - filter multiple child collections

0 Answers0