Best way to query custom entities with linq?

[mcockrellsana]

Hey Joel,

I was wondering if you had any preferred methods as to query custom entities? I noticed there's a search method on the customentityrepository but this isn't quite what i'm looking for. We've set up a custom entity that is the heart of our data in the CMS. We have a search page that uses various filters to search through this data and show only pertaining results. However i'm a bit confused on the best method to query this data directly (either with dbcontext or with built in methods) because it is stored as JSON. I would hate to have to move away from a custom entity because it creates such a nice editable user interface in the admin section automatically.

Is there any advice you can give as to how to say query the custom entities by a property/value on our custom entity data model?

Thanks!

[HeyJoel]

There's two open issues around this:

• 12 Custom Entity Partitioning & Taxonomy

• 31 Custom Entity/Full-Text Search Index

Unfortunately there's still a lot of work for us to do here. For now if you want to have a decent search and filter capability your best bet is to build an index by hooking into the Message Aggregator and subscribing to custom entity messages (events).

In the past I've used Lucene.NET to build a search index, but it can be tricky to get it working in a multi-server deployment. Your probably better off using something like elastic search, SQL FullText indexes or something hosted like Azure Search. If you don't need full-text searching you can just use a SQL table to create a simple index table. It really depends on your search requirements and your deployment environment.

For example, to update your index when a custom entity is added/updated/removed you can use these handlers:

public class MyEntityUpdatedMessageHandler : IMessageHandler<ICustomEntityContentUpdatedMessage>
{
    public MyEntityUpdatedMessageHandler()
    {
        // TODO: inject services etc
    }

    public async Task HandleAsync(ICustomEntityContentUpdatedMessage message)
    {
        if (message.CustomEntityDefinitionCode != MyEntityCustomEntityDefinition.DefinitionCode) return;

        // TODO: Get entity

        // TODO: Update index
    }
}

public class MyEntityDeletedMessageHandler : IMessageHandler<CustomEntityDeletedMessage>
{
    public MyEntityDeletedMessageHandler()
    {
        // TODO: inject services etc
    }

    public async Task HandleAsync(CustomEntityDeletedMessage message)
    {
        if (message.CustomEntityDefinitionCode == MyEntityCustomEntityDefinition.DefinitionCode)
        {
            // TODO: delete from index
        }
    }
}

You'll need to register those handlers using IMessageSubscriptionRegistration as per the docs

[mcockrellsana]

Interesting... So would there be a way (I will test this locally) to inject the DBContext into my service *using methods previously documented) and then off the context query the CustomEntityVersionDetails and then sub query on to the Model that comes back? Since the ICustomEntityDataModel appears to deserialize into the specific model type, I would assume LINQ would allow me to select out what I was looking for. Just a thought

[HeyJoel]

Well, yes you can inject CofoundryDbContext into your service and query that directly, but that query may not be ideal depending on how much data you plan to have because you'd need to deserialize and filter client-side rather than on your SQL Server (ICustomEntityDataModelMapper can help you deserialize the JSON to a DataModel)

If you're looking to write a custom DbContext query for custom entities I'd suggest taking a look at the source to SearchCustomEntityRenderSummariesQueryHandler, particularly with regards to how the CustomEntityPublishStatus table is used to efficiently get the correct version of the data for the specified PublishStatusQuery (e.g. Published/Latest/Draft).

If you don't mind getting your hands dirty with SQL you can also probably take advantage of the built-in JSON querying support in SQL Server 2016, but I've no idea of the performance characteristics of doing that, I'd image it's similar to querying with a LIKE clause.

[mcockrellsana]

Does GetCustomEntityRenderSummariesByDefinitionCodeAsync in the custom entity repository work this way as well? I notice it also contains the Model property which denotes it deserializes automatically. If i could query this way and then filter down based on Model properties, that would be somewhat efficient. We don't plan to have more than ~1000 entries for this custom entity.

Sorry for all the questions, just trying to wrap my head around your querying engine

[mcockrellsana]

Say we did something like this:

var definitionQuery = new GetCustomEntityRenderSummariesByDefinitionCodeQuery(SchoolEntityDefinition.DefinitionCode, PublishStatusQuery.Published);

var query = await _customEntityRepository.GetCustomEntityRenderSummariesByDefinitionCodeAsync(definitionQuery);

var result = query.Where(x => ((SchoolDataModel) x.Model).Grade == ESchoolGrade.A).ToList();

What are your thoughts on querying the context and custom entity like that?

[HeyJoel]

GetCustomEntityRenderSummariesByDefinitionCodeQuery will return all custom entities of that type, so the filter you do will be in memory. That query is really intended for smaller collections e.g. for lookup lists of 5-100 items where you'll always want to return all items, but depending on the performance needs and characteristics of your application you might find that works fine for you.

[HeyJoel]

This issue is covered by #12 and #31 as previously mentioned, so i'm closing it.