Adding a default where clause to a linq-to-sql table

Question

Can I add a default clause for every SQL statement created by the Linq-to-SQL class?

I have a custom DataContext with class Customer. The Customer class has a Deleted attribute, which should always be NULL when I query a table.

So, for example, I could write:

 List<Customer> customers = db.Customers.ToList<Customer>(); 

But we really get:

 List<Customer> customers = db.Customers.Where(o => o.Deleted == null).ToList<Customer>(); 

I want to keep the “deleted” data in my database, but I will never see this in my .NET code. This type would be convenient by default, so I don’t forget to add a filter to each request.

Answer 1

Instead of adding a table to your dbml constructor, add a view containing the default clause and just name the Customer view in your dbml.

To allow updates, inserts, and deletes, make sure that you select the primary key columns in your DBML design and mark their Primary Key property as true.

Answer 2

You can add a new ActiveCustomers property that returns this:

 public IQueryable<Customer> ActiveCustomers { get { return db.Customers.Where(e => e.Deleted == null); } } 

Any queries to this property may indicate additional Where conditions and otherwise modify the results, but they will always start with clients that have not been deleted. And due to the delayed execution of LINQ, this statement does not raise any additional queries.

If you need to make sure that no one can access db.Customers , you can try to hide it by doing something like this (it may not work, I will need to see your implementation):

 public new IQueryable<Customer> Customers { get { throw new InvalidOperationException("Use property ActiveCustomers instead."); } }