Unable to fetch Not Shared records

Question

I have a scenario, to find the records which are not shared with access team.

QueryExpression query = new QueryExpression("task")
        {
            ColumnSet = new ColumnSet("activityid", "subject", "customid"),
            Criteria = new FilterExpression()
            {
                Conditions =
                {
                    new ConditionExpression("customid", ConditionOperator.NotNull)
                }
            },
            LinkEntities =
            {
                new LinkEntity("task", "principalobjectaccess", "activityid", "objectid", JoinOperator.Inner)
                {
                    Columns = new ColumnSet(true),
                    EntityAlias = "POA",
                    LinkCriteria = new FilterExpression()
                    {
                        Conditions =
                        {
                            new ConditionExpression("principaltypecode", ConditionOperator.NotEqual, "team")
                        }
                    }
                }
            }
        };

Result contains records which are shared and not shared.

principaltypecode value contains either SystemUser or Team, in my scenario I am expecting records which are not shared with any team.

Update: XrmToolBox has one plugin which helped me to find not shared records.

This plugin has the answer to my question but I need a c# Code to do this. Does anybody knows the mechanism of this tool to find such records?

Answer 1

You do not have direct way to retrieve Records which are not shared. But what you could do is

1. Get principalobjectaccess where regarding Record is Account or contact or so on
2. In this way you will have all the Accounts/contacts which are shared.
3. Now Iterate through all Accounts/Contact and check the Id's you fetched from principalobjectaccess does match or not

4. In this way you could get list or Account/Contact (Records) which are not shared

   

Answer 2

I checked the database and PrincipleTypeCode is an ObjectTypeCode stored as an int, rather than a string.

8 is the object type code for SystemUser
9 is the object type code for Team

principaltypecode should never equal "team", so your current query returns everything.

You might want to try:
new ConditionExpression("principaltypecode", ConditionOperator.NotEqual, 9)

Answer 3

Solution 1

Do a Left Outer Join between Task and POA table, retrieving only the acitivityId column and the principaltypecode column:

        var query = new QueryExpression("task");

        var principalObjectAccess = query.AddLink("principalobjectaccess", "activityid", "objectid", JoinOperator.LeftOuter);

        principalObjectAccess.Columns.AddColumns("principaltypecode");

        var entityCollection = _service.RetrieveMultiple(query);

Then do an in-memory filtering to remove all entries in the collection where principaltypecode equals team. Beware that if you find an Id that fulfills this condition you have to remove all entries with that Id, this is important to remove tasks that are shared both with an user and a team.

Solution 2

This query will give you all the tasks that are shared with a team:

        var teamPrincipalTypeCode = 9;

        var query = new QueryExpression("task");

        var principalObjectAccess = query.AddLink("principalobjectaccess", "activityid", "objectid");

        principalObjectAccess.LinkCriteria.AddCondition("principaltypecode", ConditionOperator.Equal, teamPrincipalTypeCode);

        var entityCollection = _service.RetrieveMultiple(query);

After running it you just have to query for all task guids and subtract the ones returned by the query above. You can easily do this by using the Except method from System.Linq namespace.