none
How to set a default condition over a table in linq-to-sql? RRS feed

  • Question

  • I have a field on all database tables called ISDELETED. It's used for avoid to delete phisically over the database, if ISDELETED is true I know it was deleted. How could I set a default condition for all querys always return database rows ISDELETED = false in linq??? and avoid to write:

    var query = from row in Table 
              where ISDELETED == false //how to set this condition always as default
              select row;
    

    Thursday, May 5, 2011 2:56 PM

Answers

  • Ohh, I see, well, not by default, you can't, you can create a mechanism to do this for you, like a deleted items  table or using a cached copy of the table so that the original is unmodified, etc.

    Regards

    Friday, May 6, 2011 5:24 PM
  • In your datacontext you'll find this method :

    public System.Data.Linq.Table<Person> Persons
    {
    	get
    	{
    		return this.GetTable<Person>();
    	}
    }
    
    

    The datacontext-class is a partial class, so you could add an alternative function in another partial part of your class like this:

     

    public partial class NorthWindDataContext {
    
    public System.Data.Linq.Table<Product> ExistingProducts
    {
    	get
    	{
    		return this.GetTable<Product>().Where(p=>!p.ISDELETED);
    	}
    }
    }
    
    

    In your queries use db.ExistingProducts instead of db.Products

     

     

     

    Wednesday, May 11, 2011 3:29 PM
  • Or

    return this.Products.Where(p=>!p.ISDELETED);

     

    Anyway, just tested it, and it works, but the ExistingProducts-property does not return a Table<Product> but an IQueryable<Product>

     

     

    Wednesday, May 11, 2011 3:32 PM

All replies

  • What do you want to prevent? A trip to the db?

    You could also keep an array here and sync it to the column in one go when you are done.

    Btw most queries in LINQ are deferred to prevent innecesary trips to the db already.

    Regards

    Friday, May 6, 2011 1:49 PM
  • I want to use a "Logical delete". I used the partial methods Detele_Person(Person instance) in my DataContext here I update my attribute ISDELETED = true, and It works!! but I ever have to make a condition "ISDELETED == false" when I retrieve data from the database using select clause in Linq, for example:

     

    //In my datacontext partial class
    //It works!!!
    partial void DeletePerson(Person instance)
    {
       //I call a stored procedure to update instance.ISDELETED = true
    }
    
    //When I do a query
    public IEnumerable<Person> GetPersonByName(string name)
    {
       using(DBDataContext db = new DBDataContext())
       {
          var query = from person in Persons
                    where person.ISDELETED == false //how to set this condition always as default
                    select person;
          return query;
       }
    }
    

     


    It's possible to avoid write person.ISDELETED == false whenever I want to do a query from database? so my table Persons ignore the logical deleted data (where ISDELETED == false) by default.

     

    Friday, May 6, 2011 2:31 PM
  • Ohh, I see, well, not by default, you can't, you can create a mechanism to do this for you, like a deleted items  table or using a cached copy of the table so that the original is unmodified, etc.

    Regards

    Friday, May 6, 2011 5:24 PM
  • In your datacontext you'll find this method :

    public System.Data.Linq.Table<Person> Persons
    {
    	get
    	{
    		return this.GetTable<Person>();
    	}
    }
    
    

    The datacontext-class is a partial class, so you could add an alternative function in another partial part of your class like this:

     

    public partial class NorthWindDataContext {
    
    public System.Data.Linq.Table<Product> ExistingProducts
    {
    	get
    	{
    		return this.GetTable<Product>().Where(p=>!p.ISDELETED);
    	}
    }
    }
    
    

    In your queries use db.ExistingProducts instead of db.Products

     

     

     

    Wednesday, May 11, 2011 3:29 PM
  • Or

    return this.Products.Where(p=>!p.ISDELETED);

     

    Anyway, just tested it, and it works, but the ExistingProducts-property does not return a Table<Product> but an IQueryable<Product>

     

     

    Wednesday, May 11, 2011 3:32 PM