C# EntityFramework 6.0 - How to use Where Statemen

Question

I have 2 classes like this:

Parent.cs

public class Parent
{
   public int Id {get;set;}
   public virtual ICollection<Child> Children { get; set; }
}

Child.cs

public class Child
{
    public int Id {get;set;}
    public ItemStatusType ItemStatusTyp { get; set; }
    public int ParentId {get;set;}

    [ForeignKey("ParentId")]
    public virtual Parent Parent { get; set; }
}

ItemStatusType.cs

public enum ItemStatusType
    {
        Active = 1,
        Deactive = 2,
        Deleted = 3
    }

What I want is to somehow retrieve always the active ones and not the deleted ones. Since I am not deleting the record physically, I'm merely updating the ItemStatusType to Deleted status.

So, when I say ParentObj.Children I only wish to retrieve the active ones without further using Where condition.

Here is so far what I've done but giving an exception on runtime that I stated afterwards:

public class ParentConfiguration : EntityTypeConfiguration<Parent>
{
     public ParentConfiguration()
     {
             HasMany(c => c.Children.Where(p => p.ItemStatusTyp != ItemStatusType.Deleted).ToList())
                .WithRequired(c => c.Parent)
                .HasForeignKey(c => c.ParentId)
                ;
     }
}

Runtime Exception:

The expression 'c => c.Children.Where(p => (Convert(p.ItemStatusTyp) != 3)).ToList()' is not a valid property expression. The expression should represent a property: C#: 't => t.MyProperty' VB.Net: 'Function(t) t.MyProperty'.

I had to use ToList after the expression, otherwise it does not compile.

What is the proper what to do what I want?

Thanks in advance,

Answer 1

You cannot use Where or any other logic in fluent property mapping - that's just configuration.

Basically you cannot solve what you need in declarative way.

There are some workarounds which you can use for first-level entities, like implement your own extension(s) MySet<T> which will return .Set<T>.Where(x => x.ItemStatusType != ItemStatusType.Deleted) and use it everywhere, but that won't solve filtering issue for child collections.

You can go hard way and prepare a separate set of entities to use for selecting data, which basically should be based on database views or stored procedures; you will have to create separate view for every entity, so you will be able to combine selecting in any relations based on these views-entities.

For inserting though you will need to have entities mapped over "real" tables. No sure if it worth it but it might in some cases.