LINQPad "native" connection to local Windows Search/oledb database

Question

I'm trying to use LINQPad to connect to my local Windows Search index. I'm able to connect manually using code as described here: https://forum.linqpad.net/discussion/2060/connection-to-windows-search:

using (var connection = new OleDbConnection ("Provider=Search.CollatorDSO;Extended Properties=\"Application=Windows\""))
{
    connection.Open();
    var cmd = connection.CreateCommand();
    cmd.CommandText = @"SELECT TOP 10 System.ItemPathDisplay, System.ItemUrl FROM SYSTEMINDEX WHERE System.ItemType = '.config'";
    cmd.ExecuteReader().Dump();   // LINQPad lets you Dump a DataReader
}

I'm hoping that by now there's a way to do it using the native LINQPad connections. I played around a little with the 3rd-party "LINQ to DB" driver but haven't been successful. Anyone know how to do this? Thanks!

Answer 1

Joe Albahari (LINQPad creator) confirmed that there's no driver for this. Here's what I came up with instead, using the Dapper and System.Data.OleDb nuget packages:

const string defaultActivityQuery = @"SELECT
    System.ActivityHistory.AppId,
    System.ActivityHistory.AppActivityId,
    System.ActivityHistory.StartTime, 
    System.ActivityHistory.EndTime,
    System.ActivityHistory.ActiveDuration,
    System.Activity.AppDisplayName,
    System.Activity.ContentUri,
    System.Activity.Description,
    System.Activity.DisplayText,
    System.Activity.AppImageUri,
    System.Activity.BackgroundColor
FROM SystemIndex
WHERE (System.Activity.ActivityId IS NOT NULL) AND (System.ActivityHistory.StartTime > {0})
ORDER BY System.ActivityHistory.EndTime DESC";

...

var activities = WinSearch(string.Format(defaultActivityQuery, DateTime.Today.AddDays(-1).ToFileTimeUtc()));
...

static List<ActivityRecord> WinSearch(string query)
{
    using OleDbConnection oleDbConnection = new OleDbConnection("Provider=Search.CollatorDSO;Extended Properties=\"Application=Windows\"");

    var results = oleDbConnection.Query(query);
    return JsonConvert.DeserializeObject<List<ActivityRecord>>(JsonConvert.SerializeObject(results));
}

public class ActivityRecord
{
    [JsonProperty("SYSTEM.ACTIVITYHISTORY.APPID")]
    public string? AppId { get; set; }

    [JsonProperty("SYSTEM.ACTIVITYHISTORY.STARTTIME")]
    public string? StartTimeString
    {
        set => this.StartTime = double.TryParse(value, out double result) ? DateTime.FromFileTimeUtc((long)result).ToLocalTime() : null;
    }

    public DateTime? StartTime { get; private set; }

    [JsonProperty("SYSTEM.ACTIVITYHISTORY.ENDTIME")]
    public string? EndTimeString
    {
        set => this.EndTime = double.TryParse(value, out double result) ? DateTime.FromFileTimeUtc((long)result).ToLocalTime() : null;
    }

    public DateTime? EndTime { get; private set; }

    [JsonProperty("SYSTEM.ACTIVITYHISTORY.ActiveDuration")]
    public string? ActiveDurationString
    {
        set => this.ActiveDuration = double.TryParse(value, out double result) ? TimeSpan.FromTicks((long)result) : null;
    }

    public TimeSpan? ActiveDuration { get; private set; }

    public TimeSpan? Duration => this.EndTime - this.StartTime;

    [JsonProperty("SYSTEM.ACTIVITY.APPDISPLAYNAME")]
    public string? AppDisplayName { get; set; }

    [JsonProperty("SYSTEM.ACTIVITY.ContentUri")]
    public string? ContentUri { get; set; }

    [JsonProperty("SYSTEM.ACTIVITY.DESCRIPTION")]
    public string? Description { get; set; }

    [JsonProperty("SYSTEM.ACTIVITY.DISPLAYTEXT")]
    public string? DisplayText { get; set; }

    [JsonProperty("SYSTEM.ACTIVITYHISTORY.APPACTIVITYID")]
    public string? AppActivityId { get; set; }
}

Hope this helps someone else!