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

Question

Is it possible to add a default where clause to every SQL statement generated by a Linq-to-SQL class?

I have a custom DataContext with a Customer class. The Customer class has a Deleted attribute, which is what I want to always be NULL whenever I query the table.

So for example, I could write:

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

But really get:

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

I want to maintain the "deleted" data in my DB, but will never need to see it in my .NET code. This sort of default would be handy, so I don't have to remember to add the filter to every query.

Instead of adding the table to your dbml designer, add a view that contains the default where clause, and just name the view Customer in your dbml. — hatchet - done with SOverflow
– hatchet - done with SOverflow, Commented Sep 21, 2012 at 19:13

Yuck · Accepted Answer · 2012-09-21 19:11:18Z

4

You can add a new property called ActiveCustomers that returns this:

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

Any queries against that property can specify additional Where conditions and otherwise modify the results, but they will always be starting with customers who haven't been deleted. And due to LINQ's deferred execution this statement doesn't cause any additional queries to be executed.

If you need to ensure no one can access db.Customers you could try to hide it by doing something like this (may not work, I'd need to see your implementation):

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

answered Sep 21, 2012 at 19:11

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Over a year ago

The problem with this solution is that when I create the new IQueryable<Customer> Customers, I get an ambiguity error between that and the original Customers which returns the System.Linq.Table<Customer>. But I can't remove the original, because then my InsertOnSubmit calls fail, since it's trying to perform that against an IQueryable.

Yuck Over a year ago

@Paul If you're using an implementation of the repository pattern you shouldn't be querying directly against a context anyway.

hatchet - done with SOverflow · Accepted Answer · 2012-09-21 20:15:02Z

1

Instead of adding the table to your dbml designer, add a view that contains the default where clause, and just name the view Customer in your dbml.

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

answered Sep 21, 2012 at 20:15

hatchet - done with SOverflow

16.3k5 gold badges49 silver badges58 bronze badges

2 Comments

Paul Over a year ago

After some more testing, I'm actually able to do inserts and updates just fine, except for when I try to update the Deleted field. I get ChangeConflictException - Row not found or changed. when I call SubmitChanges(). I'm guessing this is due to the fact that it's looking to the view, which does not contain that record since Deleted is no longer null in my DataContext's mind.

Paul Over a year ago

Found the issue. I had to change my computed columns from AutoSync=Always to AutoSync=Never.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related