Manjit Singh Manjit Singh - 5 months ago 13
SQL Question

Optimize the linq query to sort the row?

I am getting the values from the database "Media" table by using the following query.Now i have to order the rows by its parent id means Firstly i have to show the parent Id row and then under it its child row like the following table:

ID Name ParentMediaId
3 C null
1 A 3
4 D null
2 B 4


How can i get the results like the upper table by using the following data table and modify following linq query with lambda expression?

Media table in Database:

ID Name ParentMediaId
1 A 3
2 B 4
3 C null
4 D null


Query to get the Data:

var dataset = _mediaRepository.GetAll().
Where(d => d.matter== matterId).Select(d => new
{
d.Id,
d.Name,
d.ParentMediaId,
}).ToList();

Answer

You can OrderBy by ParentMediaId if the value isn't null; otherwise Id, then order by using ThenBy by ParentMediaId to make the parents appears first.

For the OrderBy you can use null-coalescing operator (??). It returns the left-hand operand if the operand is not null; otherwise it returns the right hand operand.

var objects = _mediaRepository.GetAll()
                   .Where(d => d.matter== matterId)
                   .Select(d => new
                    {
                        d.Id,
                        d.Name,
                        d.ParentMediaId,
                    });

var dataset = objects.OrderBy(o => o.ParentMediaId ?? o.Id)
                     .ThenBy(o => o.ParentMediaId)
                     .ToList();

If childs appears first, then use ThenByDescending instead of ThenBy.

Elements will be ordered as follow:

ID   Name   ParentMediaId
3     C           null
1     A            3
4     D           null
2     B            4
Comments