Unfortunately there is no way to search for serializable carts now - at least for metafield filter you'd want. Due to the nature of how the data is stored, that might be proven to be impossible. 

If that is critical for businesses we might try to see what we can do - but I can't promise anything just yet

Thanks Quan, I'll have to go down the manual route then which I think will work, just trying to minimze custom SQL and it's the only identifer we have.

<p>I just want to add a comment to second the need for custom fields to be searchable on SerializableCarts.</p> <p>In my current project there are plans to move over from the old concrete&nbsp;classes to the new abstraction APIs, Serializable Carts would be a part of that. Due to how our payment provider works we need to be able to identify and retrieve carts based on their ID of a purchase.</p> <p>Being able to search for that ID is critical, I'd say.</p> <p>As Scott implied there are workarounds, but it would be great if this was supported by the API.</p>

Yeah, I've managed to easily implemented it with a custom SQL query but I'd obviously prefer to have something this is nativly supported and garaunteed to be efficient and future proof.

Is there a need to support actual searches or is filtering enough?

I'm looking for a search on meta field that is against the entire cart collection, e.g. similar to how the order search worked in old carts and works for PurchaseOrders

We actually have a story on implementing the search functionalities for new abstraction APIs in our "backlog". I'll try to push for it - can't promise will get it through, but I'll try.

@Scott and Jafet: Just so you know EMVPs have a slight boost on priority ;). We of couse appreciate any feedbacks. 

Better work to getting that EMVP then :p Thanks, what I've written seems to be working and we're a cut of point for upgrades at we have moved to a later phase of our UAT rounds but it would be good for future work.

@Scott. Is it possible for you to share how you implemented this functionality in SQL? We've just hit that same brick wall switching over from the old cart system to the serializable cart system. 

In my case I'm looking for a string value and using 

        public ICart GetCartByMerchantReference(string reference)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["EcfSqlConnection"];
            var sqlString = "SELECT CartId FROM [SerializableCart] WHERE Data Like '%\"MerchantReference\":{\"$type\":\"System.String\",\"$value\":\"" + reference +"\"}%'";

            using (var sqlConnection = new SqlConnection(connectionString.ConnectionString))
            {
                var cmd = new SqlCommand(sqlString, sqlConnection);
                sqlConnection.Open();

                var value = cmd.ExecuteScalar();

                if (value != null)
                {
                    var cartId = (int)value;
                    return _serializableCartProvider.Load(cartId);
                }
            }

            return null;
        }

You would need to change the type if you're searching for something different and obviously replace it with your meta field name.

<p>Perfect Scott! Thanks a lot!&nbsp;</p> <p>I've also just stumbled across a mentioning of&nbsp;SerializableCartDB in a comment (documentation!) on&nbsp;<a href="/documentation/developer-guides/commerce/orders/serializable-carts/">https://world.episerver.com/documentation/developer-guides/commerce/orders/serializable-carts/</a>&nbsp;which sounds promising, will give that a try first, then look into your SQL-query :-)&nbsp;</p>

No worries, yeah I've seen that an as far as I remember although SerializableCartDB has a find method I think it's limited to dates and customer searching and doesn't have the same filtering paramters that the old cart/order system did for meta fields so you'd have to load all the carts and search in memory which would be expensive, hence my choice of this implementation.

True, can only load all carts based on some "basic" criterias which might be quite expensive as you say. 

As per above thread, do we know if/when there will be a Cart search API to work with the serializablecarts? 

I'm trying to load all carts starting with a specific name, e.g. "WHERE Name like 'prefix%' ", which would have been simple with the OrderContext.Current.FindCarts OrderSearchParameters API. But don't think there's any way to achieve this with current API's (I'm on Commerce v11.8.5).

Scott's simple example might be our temporary way to achieve this.

Just keen to know if this is something we can look forward to :)

There will be a cart search API, however it will not be an intensive API, it's not even public (will be in internal namespace, for instance). It will be quite basic, actually.

I understand the need for a real search API, but currently that is not an easy problem to solve, given the priority 

Hi Quan

I understand it'll be constrained by the limits of the serialized data, but all I needed was to filter by the name with a wildcard which is very simplistic in SQL and performs okay.

Look forward to being able to use an API but I'll be okay code similar to Scott's for the meantime :)

Thanks for the reply and info.

Matt 

Latest SQL Server (starting from 2016) has JSON column support:

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

If Episerver would drop support for older SQL Servers then could use this. Or create a separate library which adds support for this for projects on latest SQL Server.

With JSON column, it would be possible to search, filter and do whatever is needed with serialized carts.

Drop support for old versions is easier said than done, especially for a business critial and (IMO) expensive software like SQL Server. Yes for me I would like to try JSON support,but that is not feasible to do in a framework level. Many customers will be angry to find out they will have to upgrade SQL Server just for that feature.

Yes, agree.

But there could be other options to support JSON column. Add some configuration which would allow choosing which version of SQL Server to use. Then Episerver could initialize proper column type. And additional library (NuGet package) could add support for search in serialized carts.

It's not actually a native JSON type it's just stored as an NVARCHAR type https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/ but there's a set of libraries for manipulating and handling the JSON in queries and indexing such as https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-2017 . So i think it will be pretty much the same for handling apart from the spocs and indexing on the database would need to be optimized for the newer version so the queries and speed via indexing is faster. Underlying code for accessing the spocs I would think could possibly stay the same. To be honest I think my SQL search script I posted could be migrated to a possibly support this and an index on without affecting Episerver. 

Something I might look in to.