Adding condition to mapping for child collection in Entity Framework 6 similar to NHibernate's "where"

c# entity-framework entity-framework-6 nhibernate

Question

In NHibernate, there is a where mapping that allows you to specify a condition on a property mapping that affects how it is pulled from the database. For example, if I wanted to implement a soft delete and exclude all deleted items from a set, I could map it like so:

FluentNHibernate

// in class ParentMap : ClassMap
HasMany(x => x.Children).Where("IsDeleted = 0");

Hbm.Xml

<class name="Parent" table="[Parents]">
    <bag cascade="all" lazy="true" name="Children" where="IsDeleted = 0">
        <!-- rest of map here -->
    </bag>
</class>

Is there anything similar in Entity Framework 6?

The closest thing I found was a library called EntityFramework.Filters, which allows you to add global filters for properties, but it doesn't seem to work when that property is a collection.


To give a better example of why a mapping like this is necessary, let's say I have a class that has a collection of objects that have a recursive child entity relationship (i.e., a collection of objects of the same type). They follow this basic structure:

public class ReportOutline
{
    public long Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }    
    public virtual ICollection<OutlineItem> OutlineItems { get; set; }
}

public class OutlineItem
{
    public long Id { get; set; }
    public string Name { get; set; }

    public long ReportOutlineId { get; set; }
    public long? ParentOutlineItemId { get; set; }    

    public virtual ReportOutline ReportOutline { get; set; }
    public virtual OutlineItem ParentOutlineItem { get; set; }    

    public virtual ICollection<OutlineItem> OutlineItems { get; set; }
}

And these are mapped with the EF fluent API like this:

modelBuilder.Entity<ReportOutline>()
    .HasKey(o => o.Id)
    .HasMany(o => o.OutlineItems)
    .WithRequired(i => i.ReportOutline)
    .HasForeignKey(i => i.OutlineId);

modelBuilder.Entity<OutlineItem>()
    .HasKey(p => p.Id)
    .HasMany(p => p.OutlineItems)
    .WithOptional(c => c.ParentOutlineItem)
    .HasForeignKey(c => c.ParentOutlineItemId);

This produces the correct database structure, and my records look fine. Here's an example of what the OutlineItems table would look like with two items on a ReportOutline, if one had two child items (four altogether):

Id    Name           ReportOutlineId    ParentOutlineItemId
1     Introduction   1                  NULL
2     Pets           1                  NULL
3     Cats           1                  2
4     Dogs           1                  2

When the ReportOutline gets loaded through the DbContext, however, since ReportOutlineId matched the outline's Id, the ReportOutline.OutlineItems is getting populated with all four items. This results in the sub-items appearing both under the parent items and the main outline itself:

Title:  My Report
Author: valverij

I.  Introduction  (Id: 1)
II. Pets          (Id: 2)
    A. Cats       (Id: 3)
    B. Dogs       (Id: 4)
III. Cats         (Id: 3) <--- Duplicated
IV.  Dogs         (Id: 4) <--- Duplicated

Now, if I were using NHibernate with FluentNhibernate, I could specify a where condition on the entity mapping, so that ReportOutline.OutlineItems only pulls parent items:

// in ReportOutlineMap
HasMany(x => x.OutlineItems).Where("ParentOutlineItemId IS NULL");

Without that, I would have to remember to only access ReportOutline objects through a pre-written query that explicitly deals with the OutlineItem collection.

1
4
12/8/2014 6:44:47 PM

Popular Answer

You could add an "RootOutlineItems" property to the ReportOutline class that filters for you, then call that when you want just the 1st level:

public class ReportOutline
{
    public long Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }    
    public virtual ICollection<OutlineItem> OutlineItems { get; set; }
    public ICollection<OutlineItem> RootOutlineItems { 
        get {
            return OutlineItems.Where(p=> p.ParentOutlineItem == null);
        }
    }
}

Another option would be to make the OutlineItem's ReportOutline nullable, and only ever set either the ReportOutline or the ParentOutlineItem property, but that's a little iffy, plus you'd have to navigate the tree if you ever want all the items.

0
9/8/2015 3:12:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow