New Posts New Posts RSS Feed: Sql Where Clause
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Sql Where Clause
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
orcities View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Aug-2007
Location: United States
Posts: 454 		Post Options Post Options   Quote orcities Quote  Post ReplyReply Direct Link To This Post Topic: Sql Where Clause
    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.
Back to Top
eileenv View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Jun-2007
Location: United States
Posts: 68 		Post Options Post Options   Quote eileenv Quote  Post ReplyReply Direct Link To This Post Posted: 31-Oct-2007 at 3:51pm

Can you provide a code snippet of the SQLWhereClause and the query you are trying to execute?
Back to Top
TrevLeyb View Drop Down
Newbie
Newbie
Avatar

Joined: 28-Jun-2007
Location: New Zealand
Posts: 9 		Post Options Post Options   Quote TrevLeyb Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
orcities View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Aug-2007
Location: United States
Posts: 454 		Post Options Post Options   Quote orcities Quote  Post ReplyReply Direct Link To This Post 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
 
Back to Top
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Posted: 01-Nov-2007 at 12:40pm

Have you tried changing your query strategy to CacheOnly?
Back to Top
eileenv View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Jun-2007
Location: United States
Posts: 68 		Post Options Post Options   Quote eileenv Quote  Post ReplyReply Direct Link To This Post 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.


Edited by eileenv - 07-Nov-2007 at 3:27pm
Back to Top
orcities View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Aug-2007
Location: United States
Posts: 454 		Post Options Post Options   Quote orcities Quote  Post ReplyReply Direct Link To This Post Posted: 12-Nov-2007 at 7:16am
Would I put this with each entity class that is needed?
Back to Top
eileenv View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Jun-2007
Location: United States
Posts: 68 		Post Options Post Options   Quote eileenv Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down