How to preform a relative complement query in CRM?

Option 1:

I’d prefer a modification of the proposal that AdamV makes above. I can’t think of a way that you’d get this particular query answered using Linq to CRM, Query Expressions, FetchXML alone. Daryl doesn’t offer what the client is, but I would suppose if Linq and Query Expressions were acceptable offerings, .NET is on the table. Creating aggregate fields containing the count of the related entity on the parent entity (contact in this case) offers more than the Boolean option. If the query requirements ever changed to a threshold (more than X cars, less than Y trucks, between X and Y total vehicles) the Boolean options fails to deliver. The client in this question isn’t known, but I can’t think of many (any?) cases where pulling all the records to the client on a set of 500K+ rows is more efficient than a the SQL query that CRM would make on your behalf against several integer fields with range clauses.

Upside:

1. Maintains client purity in Query approach
2. Simple client query
3. Probably as performant as possible

Downside:

1. Setups for Aggregate fields
2. Workflow or plugin to manage the increment and decrement of the aggregate fields
3. SQL Script for initial load of the aggregates.
4. Risk that aggregate fields get out of sync (workflow or plugin fails)

Option 2:

If purity within the client isn’t essential, and .NET is on the table – skip the aggregate fields and the setup and just run SQL against the Views. If you don’t want to work with the ADO.NET, a thin ORM like Dapper, Massive, or PetaPOCO can still give you an object model. As Andreas offers in his comment on the OP’s first answer, it seems like something fairly trivial to do in SQL.

Sketching something from top of mind:

SELECT c.*
FROM Contact 
WHERE C.Contactid in (
    Select contactid 
    FROM Vehicle v 
    group by v.contactid , v.type
    having v.type = ‘Car’ and count(contactid) > 1 
) 
AND NOT IN (
    Select contactid 
    FROM Vehicle v 
    group by v.contactid , v.type
    having v.type <> ‘Car’ and count(contactid) > 1 
)

Upside:

1. Much less work
2. CRM Entities get left alone

Downside:

1. Depending on the client and/or the application mixing DataAccess methods is a bit kludgy.
2. Likely less performant than Option 1

Option 3:

Mix and Match: Take the aggregate fields from Option 1. But update them using a scheduled SQL job (or something similar) with a query similar to the initial load job you’d need to write in Option 1

Upside:

1. Takes most of the work and risk out of Option 1
2. Keeps all of the performance of Option 1

Downside:

1. Some will see this as an unsupported feature.

I have resorted to pulling back all of my records in CRM, and performing the check on the client side since CRM 2011 doesn't support this via Query Expressions.

You could write two Fetch XML statements, one to return all contacts and the count of their vehicles, and another to return all contacts and the count of their cars, then compare the list on the client side. But once again, you're having to return every contact and filter it client side.

It's not tested but how about this query expression? I'm linking in the Vehicle entity as an inner join, requiring that it's a Car. I'm assuming that the field VehicleType is a String because I'm a bit lazy and don't want to test it (I'm typing this hardcore style, no compilation - pure brain work).

Optionally, you might want to add a Criteria section as well to control which of the Contact instances that actually get retrieved. Do tell how it went!

Sorry for the verbosity. I know you like it short. My brains work better when circumlocutory.

new QueryExpression
{
  EntityName = "contact",
  ColumnSet = new ColumnSet("fullname"),
  LinkEntities =
  {
    new LinkEntity 
    { 
      JoinOperator = JoinOperator.Inner, 
      LinkFromEntityName = "contact", 
      LinkFromAttributeName = "contactid", 
      LinkToEntityName = "vehicle", 
      LinkToAttributeName = "contactid",
      Columns = new ColumnSet("vehicletype"),
      EntityAlias = "Vroom",
      //LinkCriteria = { Conditions = 
      //{
      //  new ConditionExpression(
      //    "vehicletype", ConditionOperator.Equal, "car")
      //} }
      LinkCriteria = { Conditions = 
      {
        new ConditionExpression(
          "vehicletype", ConditionOperator.NotEqual, "truck") 
      } }
    } 
  }
};

EDIT:

I've talk to my MVP Gustaf Westerlund and he's suggested the following work-around. Let me stress that it's not an answer to your original question. It's just a way to solve it. And it's cumbersome. :)

So, the hint is to add a flag in the Contact or Person entity. Then, every time you create a new instance of Vehicle, you need to fire a message and using a plugin, update the information on the first about the creation of the latter.

This has several drawbacks.

1. It requires us to do stuff.
2. It's not the straight-forward do-this-and-that type of approach.
3. Maintenance is higher for every new type of Vehicle one adds.
4. Buggibility is elevated since there are many cases to regard (what happens to the flagification when a Vehicle instance is reasigned, deleted etc.).

So, my answer to your question is changed to: "can't be done". This remains effective until (gladly) proven wrong by presented alternative solution. Duck!

Personally, I'd fetch (almost) everything and unleash the hounds of LINQ onto it. But I'd do that without smiling nor proud. :)