TransWikia.com

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

Stack Overflow Asked on January 20, 2021

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

2 Answers

@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

enter image description here

Answered by Michael Wang on January 20, 2021

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))

Answered by Keith.Abramo on January 20, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP