Linq sort entity if JSON value exists [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 4 months ago.

Improve this question

I have a db table column where some properties are stored as json. Here is an example :

properties 
----------
{ "SortBy" : "ASC" }

List<MyItem> items = await this.dbContext.AllItems
    .Where(x => x.field.CompanyId== CompanyId)
    .OrderBy(x => x.Value);

I need to change the OrderBy based on that JSON value. It can be ASC, DESC, or no OrderBy() if there is no value in there.

The sort column "properties" is in the same table as AllItems. I will need to dynamically sort the column based on properties JSON.

Any suggestions?

1) "I need to change the OrderBy based on that JSON value" - is this a value stored in another table rather than AllItems? 2) What database are you using? 3) How would the query look in pure SQL? — Guru Stron
– Guru Stron, Commented Aug 9 at 5:49
Do you just need to change the sort direction (.OrderBy(...) vs .OrderByDecending(...)) or do you also need to dynamically select the sorted column (.OrderBy(x => x.SomeSortByColumn))? — T N
– T N, Commented Aug 10 at 0:07
in the same table as AllItems you can't use it in the same query. That's a very weird design. A SQL query can't automagically change its behavior based on data that didn't even exist when the SQL itself was compiled into an execution plan. You'll have to run one query to extract the JSON data, another to get the real data. Given how wasteful this is, if you don't have too many rows, you might as well load the data then sort in memory — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 11 at 13:50
What does the table actually look like? How would you get the result you want using SQL? Or was this design chosen without thinking about querying? If AllItems contains a company's orders, is the properties JSON repeated across all orders? Even though it doesn't change? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 11 at 13:52

Michał Turczyn · Accepted Answer · 2025-08-09 07:44:23Z

1

First of all you need to deserialize the JSON:

public record OrderDescriptor(string SortBy);

Then use it

var json = @"{ \"SortBy\" : \"ASC\" }";
var descriptor = JsonSerializer.Deserialize<OrderDescriptor>(json);

Then you can use it in your original context:

var queryable = this.dbContext.AllItems
    .Where(x => x.field.CompanyId== CompanyId);

if(descriptor.SortBy == "ASC") 
    return await queryable
        .OrderBy(x => x.Value)
        .ToListAsync();
else if(descriptor.SortBy == "DESC") 
    return await queryable
        .OrderByDescending(x => x.Value)
        .ToListAsync();

// Fallback to no ordering      
return await queryable.ToListAsync();

answered Aug 9 at 7:44

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Linq sort entity if JSON value exists [closed]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related