C# .NET Core 3.1 Entity Framework : include using where condition gives wrong output

Question

I have 2 entities parent/child want select them all using include.

When adding condition on child it return parents which have this condition only not all parents

My code:

Parent entity:

public class SecRole
{
    public string Name { get; set; }
    public virtual ICollection<SecRolePageAction> SecRole_SecRolePageAction { get; set; }
}

Child entity:

public class SecRolePageAction
{
    public virtual SecRole SecRole { get; set; }
    public long SecRoleID { get; set; }
    public bool IsDeleted { get; set; } = false;
}

Code:

var Q = Context.Set<SecRole>().AsNoTracking().AsQueryable();
Q = Q.Include(O => O.SecRole_SecRolePageAction)
      // Child condition below
     .Where(O => O.SecRole_SecRolePageAction.Any(P => P.IsDeleted == false)
     .ToList();

Result: it returns just parent contain child has IsDeleted = false but any parent has no child it does not return

I need to return all parents any help

asp.net core c++entity framework core

Michael Wang · Answer

@Keith.Abramo alreay gave the right answer for this question.
Here is the test result with this solution.
        public List<SecRole> getSecRole()
        {
            var SecRoles = _context.SecRoles.Include(h => h.SecRole_SecRolePageAction)
                .Where(O => O.SecRole_SecRolePageAction.Count() == 0 || O.SecRole_SecRolePageAction.Any(P => P.IsDeleted == false)).ToList(); // include address table

return SecRoles;
        }

Date Source of SecRoles:
[
  {
    "Name": "1",
    "SecRole_SecRolePageAction": [
      {
        "SecRoleID": "101",
        "IsDeleted": "true"
      }
    ]
  },
  {
    "Name": "2",
    "SecRole_SecRolePageAction": [

]
  },
  {
    "Name": "3",
    "SecRole_SecRolePageAction": [
      {
        "SecRoleID": "301",
        "IsDeleted": "false"
      },
      {
        "SecRoleID": "302",
        "IsDeleted": "true"
      }
    ]
  }
]

Screenshot of test result with Where

Keith.Abramo · Answer

It looks like you are selecting and including correctly. You may just need to modify your Where clause to check and see if your child collection is null/empty OR it has any children which have not been deleted.
Something like:
.Where(O => O.SecRole_SecRolePageAction.Count() == 0 || O.SecRole_SecRolePageAction.Any(P => P.IsDeleted == false))

C# .NET Core 3.1 Entity Framework : include using where condition gives wrong output

2 Answers

Add your own answers!

Ask a Question