Sitecore LINQPad search queries

Sitecore LINQPad search queries.md

Sitecore LINQPad search queries

The following queries can be run in LINQPad to run searches against the Sitecore database.

Search for a term in versioned and shared fields.

This script searches against all versioned and shared fields for a search term.

It will return any results from the current version of the item.

// Run in LINQPad against the _Master database.

var searchTerm = "jskemp";

var fieldsToExclude = new List<Guid>();
// __Created by
fieldsToExclude.Add(new Guid("5dd74568-4d4b-44c1-b513-0af5f4cda34f"));
// __Updated by
fieldsToExclude.Add(new Guid("badd9cf9-53e0-4d0c-bcc0-2d784c282f6a"));
// __Owner
fieldsToExclude.Add(new Guid("52807595-0f8f-4b20-8d2a-cb71d28c6103"));

var searchResultsShared = from f in SharedFields
						  join i in Items on f.ItemId equals i.ID
						  where (
						  	f.Value.Contains(searchTerm)
							|| f.Value.Contains(searchTerm.Replace("/", "%252f"))
							|| f.Value.Contains(searchTerm.Replace("/", "%252f").Replace(" ", "%2520"))
							|| f.Value.Contains(searchTerm.Replace("/", "%252f").Replace(" ", "%2b"))
						  )
						  	&& !fieldsToExclude.Contains(f.FieldId)
						  select new CleanedResult() {
							ItemId = f.ItemId,
							FieldId = f.FieldId,
							Version = 0,
							Value = f.Value,
							FieldCreated = f.Created,
							FieldUpdated = f.Updated,
							Name = i.Name,
							ItemCreated = i.Created,
							ItemUpdated = i.Updated
						  };
						  
"Shared fields".Dump();
searchResultsShared.Dump();
searchResultsShared.Select (r => r.ItemId).Dump();

var searchResults = from f in VersionedFields
					join i in Items on f.ItemId equals i.ID
					where (f.Value.Contains(searchTerm) || f.Value.Contains(searchTerm.Replace("/", "%252f")))
						&& !fieldsToExclude.Contains(f.FieldId)
					select new {
						f.ItemId, f.Version, f.Value, f.Created, f.Updated, f.FieldId,
						i.ID, i.Name, ItemCreated = i.Created, ItemUpdated = i.Updated
					};
					
var cleanedResults = new List<CleanedResult>();
foreach (var searchResult in searchResults)
{
	var item = VersionedFields
		.Where (i => i.ItemId == searchResult.ItemId)
		.OrderByDescending(i => i.Version)
		.Take(1).FirstOrDefault();
	if (searchResult.Version == item.Version) {
		cleanedResults.Add(new CleanedResult() {
			ItemId = searchResult.ItemId,
			FieldId = searchResult.FieldId,
			Version = searchResult.Version,
			Value = searchResult.Value,
			FieldCreated = searchResult.Created,
			FieldUpdated = searchResult.Updated,
			Name = searchResult.Name,
			ItemCreated = searchResult.ItemCreated,
			ItemUpdated = searchResult.ItemUpdated
		});
	}
}

"Versioned fields".Dump();
cleanedResults.Dump();

cleanedResults.Select (r => r.ItemId).Dump();
}
public class CleanedResult {
	public Guid ItemId { get; set; }
	public Guid FieldId { get; set; }
	public int Version { get; set; }
	public string Value { get; set; }
	public DateTime FieldCreated { get; set; }
	public DateTime FieldUpdated { get; set; }
	public string Name { get; set; }
	public DateTime ItemCreated { get; set; }
	public DateTime ItemUpdated { get; set; }
	
	public CleanedResult() {
	}