Retrieving data from interactions table

Question

Is there a way to retrieve data from table xdb_collection.Interactions in xdb_collection.shard0 datasource in my code?

I already tried to use the method in the following link Sorting Pages by most views but it only works with the reporting datasource.

Edit: When trying the extraction approach i get the below error and the code exists.

Answer 1

You should not query xdb_collection database. XConnect API is meant to be used for this type of scenarios. You have 2 ways to retrieve interactions from XDB database:

1. Interaction extraction to get all interactions only limited by date.
2. Search interactions if you need to have more complex search criteria.

---

Code samples taken from documentation for reference

Extraction approach

using Sitecore.XConnect;
using Sitecore.XConnect.Collection.Model;
using System;
using System.Collections.Generic;

namespace Documentation
{
    public class DataExtraction
    {
        // Async interaction
        public async void ExampleAsync()
        {
            using (Sitecore.XConnect.Client.XConnectClient client = Sitecore.XConnect.Client.Configuration.SitecoreXConnectClientConfiguration.GetClient())
            {
                try
                {
                    // Extract interactions
                    var allInteractions = new List<Interaction>();
                    var interactionEnumerator = await client.CreateInteractionEnumerator(DateTime.UtcNow, new InteractionExpandOptions(), 200);
                    while (await interactionEnumerator.MoveNext())
                    {
                        foreach (var interaction in interactionEnumerator.Current)
                        {
                            allInteractions.Add(interaction);
                        }
                    }

                }
                catch (XdbExecutionException ex)
                {
                    // Manage exceptions
                }
            }
        }
    }
}

Search based approach

using Sitecore.XConnect.Collection.Model;
using Sitecore.XConnect.Operations;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Linq;
using Sitecore.XConnect.Search;
using Sitecore.XConnect;
using Sitecore.XConnect.Client;

namespace Documentation
{
    public class SearchInteractionProperty
    {
        // Async example
        public async void ExampleAsync()
        {
            using (Sitecore.XConnect.Client.XConnectClient client = Sitecore.XConnect.Client.Configuration.SitecoreXConnectClientConfiguration.GetClient())
            {
                try
                {
                    var someChannelId = Guid.Parse("0565e9b0-936c-4594-8cc3-5fcace3918ed");

                    IAsyncQueryable<Sitecore.XConnect.Interaction> queryable = client.Interactions
                        .Where(x => x.ChannelId == someChannelId && x.StartDateTime > DateTime.UtcNow.AddDays(-10));

                    var enumerable = await queryable.GetBatchEnumerator(10);

                    while (await enumerable.MoveNext())
                    {
                        IReadOnlyCollection<Interaction> currentBatch = enumerable.Current;
                    }
                }
                catch (XdbExecutionException ex)
                {
                    // Handle exception
                }
            }
        }    
    }
}