CODEWITHSUNDEEP
c#.netsqlsql-serverentity-framework
Stefan Bossbaly
Stefan Bossbaly

Reputation: 6804

Entity Framework constrains on navigation properties

I want to limit model that are returned by a navigational property. For example, I am using an AuditInfo model to log the activity of a model. Once a model is deleted the DeletedBy and Deleted attributes are set. However since nothing is ever really "deleted" from the database, these models will still be populated in navigational properties referenced by other models.

AuditInfo Class

public class AuditInfo
{
    [Key]
    public int AuditInfoID { get; set; }

    //Other attributes

    public string DeletedBy { get; set; }

    public DateTime? Deleted { get; set; }
}

Class that has a navigational properties

public class BlogPost
{
    //Other attributes

    //Only return Comment where Comment.AuditInfo.Deleted is NULL
    public virtual IList<Comment> Comments { get; set; }
}

Class that is being audited 

public class Comment
{
    //Other attributes

    public int AuditInfoID { get; set; }
}

How would I set up a constrain so that only the non-deleted comments (Comment.AuditInfo.Deleted is NULL) from a BlogPost.Comments?

Upvotes: 4

Views: 1830

Answers (3)

Slauma
Slauma

Reputation: 177163

(I assume you are using EF Code-First, because of the [Key] attribute.)

There are different ways to load navigation properties and related entities and you can apply a filter for some of these ways but not for all:

  • Lazy loading:

    Your navigation property has to be virtual so that lazy loading works at all:

    public virtual IList<Comment> Comments { get; set; }

    Loading the parent:

    var blogPost = context.BlogPosts.Find(1);
foreach (var comment in blogPost.Comments) // lazy loading triggered here
{
}

    You can't apply a filter here. Lazy loading will always load all comments of the given blog post.

  • Eager loading:

    var blogPost = context.BlogPosts.Include(b => b.Comments)
    .SingleOrDefault(b => b.Id == 1);

    You can't apply a filter in Include. Eager loading will always load all comments of the given blog post.

  • Explicit loading:

    Loading the parent:

    var blogPost = context.BlogPosts.Find(1);

    You can apply a filter when you load the comments now:

    context.Entry(blogPost).Collection(b => b.Comments).Query()
    .Where(c => !c.AuditInfo.Deleted.HasValue)
    .Load();

  • Projection:

    You can apply a filter in the projected properties:

    var blogPost = context.BlogPosts
    .Where(b => b.Id == 1)
    .Select(b => new
    {
        BlogPost = b,
        Comments = b.Comments.Where(c => !c.AuditInfo.Deleted.HasValue)
    })
    .SingleOrDefault();

It is not possible to apply some kind of global filter policy in the model definition so that this filter gets applied for all methods above automatically and without specifying it explicitly in the explicit loading and in the projection example. (I think you have such a global model definition in mind, but that's not possible.)

Upvotes: 5

podiluska
podiluska

Reputation: 51504

Map the entity to a SQL View on that filters out the deleted entries.

Upvotes: 0

steavy
steavy

Reputation: 1576

maby add a custom property to entity class, which will use the navigation property but filter it and return filtered data?

Upvotes: 1

Related Questions