Hard-coded SQL fix

[kentico-matthews]

I noticed there is hard-coded SQL for the insertion of ContactGroupMember bindings

Generally, Kentico recommends against hard-coding SQL statements, and the Support team tells customers it is unsupported.

I recommend swapping out the existing logic to use ObjectQuery. Using nested object queries and BulkInsert, it could be done in only 2 database queries- one to get the contact IDs and one to insert the bindings.

Rough sketch of the idea:

// Inject these
private readonly IInfoProvider<ContactGroupMemberInfo> contactGroupMemberInfoProvider;
private readonly IInfoProvider<ContactInfo> contactInfoProvider;

int myContactGroupID = XX;
List<ContactGroupMemberInfo> groupMemberList = new();

// Subquery for all existing group member IDs - doesn't execute here because we don't enumerate its result
existingGroupMemberIdSubquery = contactGroupMemberInfoProvider.Get()
	.WhereEquals(nameof(ContactGroupMemberInfo.ContactGroupMemberType), ContactGroupMemberTypeEnum.Contact)
	.WhereEquals(nameof(ContactGroupInfo.ContactGroupMemberContactGroupID), myContactGroupID)
	.Column(nameof(ContactGroupMemberRelatedID));

// Query utilizing subquery, this is the one we actually execute via GetListResultAsync
contactIDs = await contactInfoProvider.Get()
	.WhereNotIn(nameof(ContactInfo.ContactId), existingGroupMemberIdSubquery)
	.WhereIn(nameof(ContactInfo.ContactGUID), theContactGuidsFromTheCSV)
	.Column(nameof(ContactInfo.ContactId))
	.GetListResultAsync<int>();
	
foreach (int contactID in contactIDs)
{
	// Add the current contact to the contact group
	groupMemberList.Add( new ContactGroupMemberInfo {
		ContactGroupMemberRelatedID = contactID,
		ContactGroupMemberType = ContactGroupMemberTypeEnum.Contact,
		ContactGroupMemberContactGroupID = myContactGroupID,
		...
	});
}
// Just one query to insert all group members
contactGroupMemberInfoProvider.BulkInsert(groupMemberList);