Print Page | Close Window

Sql Where Clause

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=518
Printed Date: 11-Jun-2026 at 3:03pm


Topic: Sql Where Clause
Posted By: orcities
Subject: Sql Where Clause
Date Posted: 23-Oct-2007 at 10:01am
After setting the Entity Where clause is there a way to override it?
 
99% of the time I only need an entity with a specific flag set, not deleted. But, when someone tries to add a new entity I want to verify that it's not one that has been marked deleted.
 
When running an Rdb query it will never return a result even if I explicitly set the AddClause to the oposite value.


Replies:
Posted By: eileenv
Date Posted: 31-Oct-2007 at 3:51pm

Can you provide a code snippet of the SQLWhereClause and the query you are trying to execute?

Posted By: TrevLeyb
Date Posted: 31-Oct-2007 at 5:32pm

Is this for a lot of tables or a single table?

I have managed this in two ways in the past. (based on the fact that the SQL where clause is defined in the definition of the table, and like you, not seeing how I could override that at runtime).
 
1. Create a second instance of the table in the Object Mapper which is identical but without the where clause. So, if it was for Customers, my main class would be 'Customers' whcih would include the where clause, and then have 'CustomersInclDeleted' as a object which did not. In that very special case where I need the deleted customers as well, I can use the second object.
 
2. The main way I manage this is to separate out the code that returns entities and entity sets behind a Facade layer. So I never directly call something like pm.GetEntities<Customer>() in my code. I will always call a separate method in a Facade layer to do this, such as GetCustomers();
 
So in this case, I will have possibly either GetCustomers() and GetCustomersInclDeleted() methods in my Facade layer, or I will have a GetCustomers() and GetCustomers(bool inclDeleted) method.
 
Hope this helps.

Posted By: orcities
Date Posted: 01-Nov-2007 at 7:17am
Originally posted by eileenv

Can you provide a code snippet of the SQLWhereClause and the query you are trying to execute?

SQL Where Clause in Object Mapper: InActive = 0
Snippet:
RdbQuery query = new RdbQuery(typeof(Contact), ContactIdEntityColumn, EntityQueryOp.EQ, CurrentContact.ContactId);
query.AddClause(Contact.InActiveEnitityColumn, EntityQueryOp.EQ, 1);
Contact aContact = MainPm.EntityManager.GetEntity<Contact>(query);
 
not copied and pasted. just an example
 

Posted By: Linguinut
Date Posted: 01-Nov-2007 at 12:40pm

Have you tried changing your query strategy to CacheOnly?

Posted By: eileenv
Date Posted: 07-Nov-2007 at 12:40pm
The "SQL Where Clause" works great if the value of the filter column is invariant. It is not good if the value of the filter column can change -- as in the case with an "InActive" or "Deleted" column.
 
The more dynamic approach would be to add a "Fetching" handler to the PersistenceManager and add the clause if necessary. The Fetching event is raised just before the PersistenceManager sends a query to the server; the query is included in the event args for the Fetching handler, and you can modify it before DevForce applies it.
 
You might initialize your PersistenceManager as follows:
 

private void InitializePm() {

  mManager = PersistenceManager.DefaultManager;

  // other stuff

  mManager.Fetching += new EventHandler<EntityFetchingEventArgs>(mManager_Fetching);

}

 

 

static void mManager_Fetching(object sender, EntityFetchingEventArgs e) {

  IdeaBlade.Persistence.EntityQuery q = e.Query as EntityQuery;

  if ( q.EntityType == typeof(Contact) ) {

    q.AddClause("InActive", EntityQueryOp.EQ, 0); 

  }

}

 
Hope this helps.

Posted By: orcities
Date Posted: 12-Nov-2007 at 7:16am
Would I put this with each entity class that is needed?

Posted By: eileenv
Date Posted: 12-Nov-2007 at 10:05am
You could either add an "if" clause in the event handler for every applicable entity or create a base class in which every applicable entity derives from and check for the base class in the handler.


Print Page | Close Window