|
I am not a SQL expert so it was a bit of a challenge to come up with the query that satisfies your request. I do think, though, that I have found it.
If I am interpreting your request correctly, a query that satisfies it is:
select Id from Customer where Id NOT IN (select DISTINCT CustomerId from OrderSummary where OrderSummary.EmployeeId <> 1)
in the case where you want to find all customers who have not been served by Employee Id 1.
To implement this query in IdeaBlade's Object Query Language (OQL) we add the clauses, subqueries, and operators that make up the query to it using Polish Reverse Notation syntax. For example, the OQL query that is the equivalent of the SQL query "select * from OrderSummary where Id > 10240 AND Id < 10260" is:
EntityQuery aOrderSummaryQuery = new EntityQuery(typeof(OrderSummary));
aOrderSummaryQuery.AddClause("Id", EntityQueryOp.GT, 10240);
aOrderSummaryQuery.AddClause("Id", EntityQueryOp.LT, 10260);
aOrderSummaryQuery.AddOperator(EntityBooleanOp.And);
EntityList<OrderSummary> orders = mPm.GetEntities<OrderSummary>(aOrderSummaryQuery);
The OQL for the SQL query that gives what you want looks like this:
// Ultimately want to return Customer objects
EntityQuery aCustomerQuery = new EntityQuery(typeof(Customer));
// We are actually querying against Orders so we need a subquery that queries OrderSummary
EntitySubquery aOrderSummarySubquery = new EntitySubquery(EntityRelationLink.From(typeof (Customer),"OrderSummaries"));
aOrderSummarySubquery.AddClause("EmployeeId",RdbQueryOp.NE, 1); // The actual query
// Now we will build the "query stack"
aCustomerQuery.AddSubquery(aOrderSummarySubquery);
aCustomerQuery.AddOperator(EntityBooleanOp.Not);
// Fetch Customer Entities that satisfy this OQL query
EntityList<Customer> customers = mPm.GetEntities<Customer>(aCustomerQuery);
|