Question Status

Verified
Jawad asked a question on 5 Mar 2014 10:18 AM

Hi,
How can i retrieve entity data who don't have any relationship with the entity context, my code is below but keys count gives return 0 ? :

 if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity Dlistentity = (Entity)context.InputParameters["Target"];
                string listid = string.Empty;
                if (Dlistentity.Attributes.Contains("new_listid"))
                {
                    listid = Dlistentity.Attributes["new_listid"].ToString();
                }             
                Entity Mapentity = new Entity("new_dlistcontactmap");
                ColumnSet columnSet = new ColumnSet(true);
                ColumnSet allFields = new ColumnSet() { AllColumns = true };
                
                var keys = Mapentity.Attributes.Keys;
                string countdata = keys.Count().ToString();
                throw new InvalidPluginExecutionException(countdata);
                foreach (var key in keys)
                {
                    if (key == listid)
                    {
                        string Contactid = Mapentity.Attributes["new_idcontact"].ToString();
                        Entity ContactEntity = new Entity("contact");
                        var contactkeys = ContactEntity.Attributes.Keys;
                        foreach (var Ckey in contactkeys)
                        {
                            string idcontact = ContactEntity.Attributes["new_idcontact"].ToString();
                            if (Ckey == idcontact)
                            {
                                Guid contactguid = ContactEntity.Id;
                                Relationship relationship = new Relationship("new_new_distributionlist_contact");
                                EntityReference contact = new EntityReference(ContactEntity.LogicalName, contactguid);
                                EntityReferenceCollection relatedEntities = new EntityReferenceCollection();
                                relatedEntities.Add(contact);
                                service.Associate(Dlistentity.LogicalName, contactguid, relationship, relatedEntities);
                            }
                        }
                    }
                }

Reply
Graham Davis responded on 5 Mar 2014 10:46 AM

Its a bit difficult to see from your code exactly what you are trying to achieve. The quick answer to your question is that you declare a new Entity called Mapentity, which are that point has absolutely no attributes in its property bag. You then define a column set which I assume you intended to use to retrieve an entity collection. Your next step declares a var which you set to Mapentity.Attributes.Keys; which as mentioned before is a completely empty object. You have not yet fetched anything from the CRM server. This is why your object is empty, its because there are no keys in your empty object.

If you post a step by step description of what you actually want to do, I would be happy to post some code for you.

Reply
Jawad responded on 5 Mar 2014 11:00 AM

Thanks Graham,

a scenario for what i want is: i'm trying to import data from SQL server, there is 3 tables (distribution list, contacts and contact_list that is maping between distribution list and contacts), in my CRM i have a custom entity called "new_distributionlist" that have a N-N with contacts system entity, i've exported the sql tables to csv files and to import them, i've create a custom entity "new_dlistcontactmap" that will contain the contactid, dlistid and contactguid, where contactid and dlistid are the IDs in SQL server, i want when importing a distribution list file compare the dlistid with the dlistid in "new_dlistcontactmap" entity and if equal get the guid of the contact from contact entity and store the contact in dlist entity.

i don't know if it's clear

Reply
Graham Davis responded on 5 Mar 2014 11:32 AM

one more clarification, are you simply trying to replicate your three SQL tables in CRM so that you have distribution lists with associated contacts?

Reply
Jawad responded on 5 Mar 2014 11:34 AM

yes

Reply
Verified Answer
Graham Davis responded on 5 Mar 2014 12:49 PM

Are you sure that a plugin is the right answer here? What happens if contacts are added to the contact table? Or new Distribution lists to the distribution list table? If this were to happen you would have a mix of old data and new data in all three tables.

My suggestion would be to eliminate manual imports from the process completely. Write a simple console app which does the following

1. Synchronize contacts in the SQL table with Contacts in CRM. Ensure that the SQL key field is populated into a text field.

2. Synchronize distribution lists from the SQL to table with Distribution lists in CRM and ensure that the SQL key field is populated into a text field.

3. Loop through your N:N relationship table and process the associations (which is at the heart of the code you posted originally)

The algorithm to do these synchronizations could be very simple and yet reliable. Here are the steps (they will be identical for both contacts and distribution lists).

1. Create a duplicate detection rule for contacts which does an exact match on your SQL key field.

2. Select the contact rows from SQL, pop them in a data table, loop through each record create a contact record in CRM

       public DataTable GetDataTable(string query, SqlConnection oConn)

       {

           SqlDataAdapter adapter = new SqlDataAdapter();

           adapter.SelectCommand = new SqlCommand("select * from contacts", new SqlConnection("your connection string here");

           DataTable myDataTable = new DataTable();

           try

           {

               adapter.Fill(myDataTable);

               foreach(DataRow oRow in myDataTable.Rows)

               {

                   Entity syncContact = new Entity("contact");

                   syncContact.Attributes.Add["attributename"] = oRow["columnname"]; //you may need to do some data type work here

                   //add any other columns here including your SQL key column

                   createEntityOrUpdateDuplicate(syncContact);

               }

           }

           catch

           {}

           return myDataTable;

       }

3. All I did here (and it is not the most elegant solution ever) was leveraged duplicate detection to decide on whether or not to update or create. That way all the logic for finding duplicates is configurable in CRM

   public void createEntityOrUpdateDuplicate(Entity syncEntity)

       {

           RetrieveDuplicatesRequest request = new RetrieveDuplicatesRequest();

           request.BusinessEntity = syncEntity;

           request.MatchingEntityName = syncEntity.LogicalName;

           request.PagingInfo = new PagingInfo();

           request.PagingInfo.PageNumber = 1;

           request.PagingInfo.Count = 1;

           RetrieveDuplicatesResponse response = (RetrieveDuplicatesResponse)_serviceProxy.Execute(request);

           if (response.DuplicateCollection.Entities.Count > 0) //duplicates found, go for the update

           {

               Entity duplicateEntity = response.DuplicateCollection.Entities[0];

               syncEntity.Id = duplicateEntity.Id;

               try

               {

                   OrganizationServiceContext _context = new OrganizationServiceContext(_serviceProxy);

                   _context.Attach(syncEntity);

                   _context.UpdateObject(syncEntity);

                   _context.SaveChanges();

               }

               catch

               {

                   throw;

               }

           }

           else //no duplicates, a simple save is safe

           {

               try

               {

                   using (_serviceProxy)

                   {

                       _serviceProxy.EnableProxyTypes();

                       _service = (IOrganizationService)_serviceProxy;

                       return _service.Create(syncEntity).ToString();

                   }

               }

               catch

               {

                   throw;

               }

           }

       }

Once that’s done, you should have perfectly synchronized contacts and distribution lists. All that is left is to grab the linking table from SQL and loop through it performing the “AssociateRequests”. Because you can guarantee that you will only have one record in CRM for each record in your SQL table, a simple method like this will get you the contact or distribution list (The filedname would be the schema name of your SQL key which you configured in CRM, and the fieldvalue would be the actual key which you grab from the DataRow 

public Entity getSingleRecordAllColumns(string entityName, string fieldName, string fieldValue)

       {

           _service = (IOrganizationService)_serviceProxy;

           QueryExpression query = new QueryExpression();

           query.EntityName = entityName;

           query.ColumnSet.AllColumns = true;

           query.Criteria = new FilterExpression();

           query.Criteria.FilterOperator = LogicalOperator.And;

           query.Criteria.Conditions.Add

           (

               new ConditionExpression(fieldName, ConditionOperator.Equal, fieldValue)

           );

           EntityCollection entities = _service.RetrieveMultiple(query);

           if (entities.Entities.Count > 0)

           {

               return entities[0];

           }

           else

           {

               return null;

           }

       }

And then finally do the associate request. This blog has a very straightforward explanation of how to pull of the AssociateRequest  mscrm-developer.blogspot.com/.../creating-records-for-many-to-many.html

Reply
Jawad responded on 5 Mar 2014 1:11 PM

Thanks lot, i'll try it tomorrow and let you know about it

thanks again

Reply
harihar responded on 5 Oct 2014 6:12 PM

Nice explanation Graham!

Jawad,

Could you please share your outcome and any additional lessons learned.

thank you,

Harihar

Reply
harihar responded on 4 Nov 2014 6:43 AM

Hi Jawad, What was the outcome of your test. Lessons learned.

Appreciate if you could share. Helps other forum users.

Harihar

Reply
Verified Answer
Graham Davis responded on 5 Mar 2014 12:49 PM

Are you sure that a plugin is the right answer here? What happens if contacts are added to the contact table? Or new Distribution lists to the distribution list table? If this were to happen you would have a mix of old data and new data in all three tables.

My suggestion would be to eliminate manual imports from the process completely. Write a simple console app which does the following

1. Synchronize contacts in the SQL table with Contacts in CRM. Ensure that the SQL key field is populated into a text field.

2. Synchronize distribution lists from the SQL to table with Distribution lists in CRM and ensure that the SQL key field is populated into a text field.

3. Loop through your N:N relationship table and process the associations (which is at the heart of the code you posted originally)

The algorithm to do these synchronizations could be very simple and yet reliable. Here are the steps (they will be identical for both contacts and distribution lists).

1. Create a duplicate detection rule for contacts which does an exact match on your SQL key field.

2. Select the contact rows from SQL, pop them in a data table, loop through each record create a contact record in CRM

       public DataTable GetDataTable(string query, SqlConnection oConn)

       {

           SqlDataAdapter adapter = new SqlDataAdapter();

           adapter.SelectCommand = new SqlCommand("select * from contacts", new SqlConnection("your connection string here");

           DataTable myDataTable = new DataTable();

           try

           {

               adapter.Fill(myDataTable);

               foreach(DataRow oRow in myDataTable.Rows)

               {

                   Entity syncContact = new Entity("contact");

                   syncContact.Attributes.Add["attributename"] = oRow["columnname"]; //you may need to do some data type work here

                   //add any other columns here including your SQL key column

                   createEntityOrUpdateDuplicate(syncContact);

               }

           }

           catch

           {}

           return myDataTable;

       }

3. All I did here (and it is not the most elegant solution ever) was leveraged duplicate detection to decide on whether or not to update or create. That way all the logic for finding duplicates is configurable in CRM

   public void createEntityOrUpdateDuplicate(Entity syncEntity)

       {

           RetrieveDuplicatesRequest request = new RetrieveDuplicatesRequest();

           request.BusinessEntity = syncEntity;

           request.MatchingEntityName = syncEntity.LogicalName;

           request.PagingInfo = new PagingInfo();

           request.PagingInfo.PageNumber = 1;

           request.PagingInfo.Count = 1;

           RetrieveDuplicatesResponse response = (RetrieveDuplicatesResponse)_serviceProxy.Execute(request);

           if (response.DuplicateCollection.Entities.Count > 0) //duplicates found, go for the update

           {

               Entity duplicateEntity = response.DuplicateCollection.Entities[0];

               syncEntity.Id = duplicateEntity.Id;

               try

               {

                   OrganizationServiceContext _context = new OrganizationServiceContext(_serviceProxy);

                   _context.Attach(syncEntity);

                   _context.UpdateObject(syncEntity);

                   _context.SaveChanges();

               }

               catch

               {

                   throw;

               }

           }

           else //no duplicates, a simple save is safe

           {

               try

               {

                   using (_serviceProxy)

                   {

                       _serviceProxy.EnableProxyTypes();

                       _service = (IOrganizationService)_serviceProxy;

                       return _service.Create(syncEntity).ToString();

                   }

               }

               catch

               {

                   throw;

               }

           }

       }

Once that’s done, you should have perfectly synchronized contacts and distribution lists. All that is left is to grab the linking table from SQL and loop through it performing the “AssociateRequests”. Because you can guarantee that you will only have one record in CRM for each record in your SQL table, a simple method like this will get you the contact or distribution list (The filedname would be the schema name of your SQL key which you configured in CRM, and the fieldvalue would be the actual key which you grab from the DataRow 

public Entity getSingleRecordAllColumns(string entityName, string fieldName, string fieldValue)

       {

           _service = (IOrganizationService)_serviceProxy;

           QueryExpression query = new QueryExpression();

           query.EntityName = entityName;

           query.ColumnSet.AllColumns = true;

           query.Criteria = new FilterExpression();

           query.Criteria.FilterOperator = LogicalOperator.And;

           query.Criteria.Conditions.Add

           (

               new ConditionExpression(fieldName, ConditionOperator.Equal, fieldValue)

           );

           EntityCollection entities = _service.RetrieveMultiple(query);

           if (entities.Entities.Count > 0)

           {

               return entities[0];

           }

           else

           {

               return null;

           }

       }

And then finally do the associate request. This blog has a very straightforward explanation of how to pull of the AssociateRequest  mscrm-developer.blogspot.com/.../creating-records-for-many-to-many.html

Reply