LINQ to SQL. Filtering or custom select. RRS feed

  • Question

  • There is standard ability to have custom insert, update, delete in LINQ to SQL (using stored procedures).
    It is possible to have filter at DataContext level, but for related data only:

    // Preload Orders for Customer.
    // One directive per relationship to be preloaded.
    DataLoadOptions ds = new DataLoadOptions();
    ds.LoadWith<Customer>(c => c.Orders);
        (c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
    db.LoadOptions = ds;

    But I did not find any ability to have custom SELECT or filtering of main data (table).
    To illustrate the question, say, there is field [Deleted] in each and every table,
    and I want to get only records that have [Deleted]=0.
    Something like
    ds.LoadWith<Customer>(c => c.Deleted==0);
    ds.LoadWith<Order>(o => o.Deleted==0);
    ds.AssociateWith<Customer>(c => c.Orders.Where(p.Deleted==0));

    This way expression
        customer.Orders and
    would generate 
        WHERE CUSTOMER.Deleted=0 AND ORDER.Deleted=0
    in SQL statement.

    Saturday, January 17, 2009 7:17 PM

All replies

  • You could use the inheritance model to expose ActiveCustomers and use the IsDeleted bit as the Discriminator column. Then you could query for those with IsDeleted=0 with the following:

    var query = from c in dc.ActiveCustomers
                       select c

    Alternatively, you could enforce this by using a view or stored proc to fetch your objects. Unfortunately a stored proc would preclude you from joining the results on the server. You could use a Table value function which would allow for server side joins but allow for stored proc like functionality.

    I hope at least one of these ideas works for you.

    Jim Wooley - "LINQ In Action", The book is now available. Don't wait for the movie
    Saturday, January 17, 2009 8:21 PM
  • Thank you for your answer.
    Now I understand that there is no standard and convenient way to filter a Table, even though I'm able to do this for related tables.
    Inheritance can be a workaround, but looks, sorry to say, ugly: you'll need two objects for each table with two different names. This is not very convenient for developers who will use such DAL.
    I'd rather wanted ability to simply set WHERE clause for each table.
    This way I'd be able to easily navigate all objects, for example, like this:
    Right now such statement produces three SQL requests to get Orders, Products, and Manufacturer, I just need convenient way to add WHERE clause to each of them.
    Maybe there are some Attributes for this?

    I did not found this feature in LINQ to Entity either. That's a pity.
    Sunday, January 18, 2009 2:51 AM
  • As another option, you could extend the context through a partial class and expose a function similar to the following:

    Public IQueryable<Customer> ActiveCustomers()  
        Return This.GetTable<Customer>().Where(c => c.Deleted==0);  

    While this would work for querying, it wouldn't  give you access to updating because you would be exposing the IQueryable rather than Table type.

    FWIW, The prefered way of handling this scenario in the Entity Framework would be through the inheritance model with Deleted being the discriminating column the same way you could do with LINQ to SQL.

    Jim Wooley - "LINQ In Action", The book is now available. Don't wait for the movie
    Sunday, January 18, 2009 8:28 PM
  • Thank you again Jim, and hello from Sarasota.

    On the issue.
    I consider using inheritance to solve filtering problem as very broken logic.
    If I follow this broken logic,
    1. I have to have inherited subclasses for each and every static filter I can possibly think of: deleted=0; lastModified>yearago; cardIsValid=1, and so on.
    2. It does not solve the problem of automatic filtering in case of access like this

    And I do not undestand Microsoft's developers logic that allows filtering of related tables but does not allow filtering of the main table.

    Sunday, January 18, 2009 11:00 PM