Filter every call made by a DataContext when using LinQ Entities

Question

I'm using logical delete in my system and would like to have every call made to the database filtered automatically.

Let say that I'm loading data from the database in the following way :

product.Regions

How could I filter every request made since Regions is an EntitySet<Region> and not a custom method thus not allowing me to add isDeleted = 0

So far I found AssociateWith but I'd hate to have to write a line of code for each Table -> Association of the current project...

I'm looking into either building generic lambda Expressions or.. something else?

Answer 1

It looks to me like you're using a relationship between your Product and Region classes. If so, then somewhere, (the .dbml file for auto-generated LINQ-to-SQL), there exists a mapping that defines the relationship:

[Table(Name = "Product")]
public partial class Product
{
    ...
    private EntitySet<Region> _Regions;
    [Association(Storage = "_Regions")]
    public EntitySet<Region> Regions
    {
        get { return this._Regions; }
        set { this._Regions.Assign(value); }
    }
    ...
}

You could put some logic in the accessor here, for example:

public IEnumerable<Region> Regions
{
    get { return this._Regions.Where(r => !r.isDeleted); }
    set { this._Regions.Assign(value); }
}

This way every access through product.Regions will return your filtered Enumerable.

Answer 2

You could create an extension method that implements your filter and use that as your convention.

public static class RegionQuery
{
    public static IQueryable<Region> GetAll(this IQueryable<Region> query, bool excludeDeleted=true)
    {
        if (excludeDeleted)
            return query.Regions.Where(r => !r.isDeleted);

        return query.Regions;
    }
}

So whenever you want to query for regions you can make the following call to get only the live regions still providing an opportunity to get at the deleted ones as well.

context.Regions.GetAll();

It my be a little wonky for access the Products property, but still doable. Only issue is you would have to conform to the convention. Or extend the containing class.

someProduct.Regions.GetAll();

I hope that helps. That is what I ended up settling on because I haven't been able to find a solution to this either outside of creating more indirection. Let me know if you or anyone else comes up with a solution to this one. I'm very interested.