I am getting list of rows from the DB (which I am passing back to my controller and then processing in the view with Razor syntax), but I am not able to sort the list by a specific DB column.

public class GetTableDetails
{

    public string Column1 { get; set; }
    public string Column2 { get; set; }
    public string Column3 { get; set; }
    public string Column4PDT { get; set; }
    public string Column5 { get; set; }

    public static List<GetTableDetails> GetDetails(ApplicationDbContext _context)
    {

        var tableEntriesCount = _context.TableName.Count();
        var Table = _context.TableName.FirstOrDefault();
        string[,] table = { };

        List<GetTableDetails> mainList = new List<GetTableDetails>();

        List<string> column1List = _context.TableName.Where(c => c.Column1Title != null).Select(c => c.Column1Title).ToList();
        List<string> column2List = _context.TableName.Where(c => c.Column2Title!= null).Select(c => c.Column2Title).ToList();
        List<string> column3List = _context.TableName.Where(c => c.Column3Title!= null).Select(c => c.Column3Title).ToList();
        List<DateTime> postedDateTimeList = _context.TableName.Where(c => c.PostedDateTime != null).Select(c => c.PostedDateTime.Value).ToList();
        List<string> column5List = _context.TableName.Where(c => c.Column5Title!= null).Select(c => c.Column5Title).ToList();

        for (int i = 0; i < tableEntriesCount; i  )
        {
            rows = new string[,] { { column1List[i], column2List[i], column3List[i], postedDateTimeList[i].ToString(), column5List[i] } };

            mainList.Add(new GetTableDetails() { Column1 = rows[0, 0], Column2 = rows[0, 1], Column3 = rows[0, 2], Column4PDT = rows[0, 3], Column5 = rows[0, 4] });
        }

        return mainList;

    }
}

I have tried variations of mainList.OrderByDescending(p => p.PostedDateTime); at the bottom of the GetCardDetails class before returning the list. But I keep getting a variety of errors.

I have also tried sorting the DB table (Rather than my list) before getting the values but despite trawling Google I cant figure this out, the Entity Frameworks docs seem more complicated that what I need to do. The below inserted at the top of the GetCardDetails class doesnt throw an error, but doesnt do anything and is not applying any changes to the DB table.

_context.TableName.OrderByDescending(p => p.PostedDateTime);
_context.SaveChangesAsync();

I'm also not sure what 'best practice' is here. Sort the list or sort the table before creating the list. I figure sorting the list would be more efficient.

Can anyone help with how I can sort the list by the PostedDateTime column please?

CodePudding user response：

This code doesn't do anything:

_context.TableName.OrderByDescending(p => p.PostedDateTime);
_context.SaveChangesAsync();

It does sort the values, but then ignores the result of that sort. (You don't store the returned result in anything.) And then it "saves" changes to the database, but you didn't change anything so there's nothing to save. Sorting a set of results is not a change to data.

If GetDetails should return its list in a specific order, return it in that order:

return mainList.OrderByDescending(p => p.SomeField).ToList();

Or, if you want to order the DB table before reading it at all, store that ordered query in a variable and use it for your table access. For example:

var table = _context.TableName.OrderByDescending(p => p.PostedDateTime);

And anywhere you access the table:

List<string> column1List = table.Where(c => c.Column1Title != null).Select(c => c.Column1Title).ToList();
//                         ^--- here

As an aside, I strongly suspect that this overall method is very inneficient with querying the data. But I don't know anything about the data or the intent of the method, so I can't really be more specific than that. But in general you can probably build up a single expression tree to query the database, including whatever sorting and projection you need, and return an IEnumerable<SomeModel> as an expression tree to be evaluated when needed. But those details may evolve as you continue to learn.

CodePudding user response：

you have to do  mainList.OrderByDescending(p => p.Column4PDT).ToList(); since from what I remember orderbydescending returns an ienumerator and additionally your class doesn't have the variable you wanted to sort by in your post

can't currently check it, but it should work

btw. where are you defining the type of rows (string[,]) rows = new string[,]

CodePudding user response：

1. First separate your model from your query class.
2. Then do a Select into a view model like the code below.
3. Also, is this really a "details" query? Perhaps you should rename it to "Index" since it is returning a list.
4. You may want to change Column4PDT to a DateTime in your view model.

The code:

public class TableDetailsViewModel
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
    public string Column3 { get; set; }
    public string Column4PDT { get; set; }
    public string Column5 { get; set; }
}
public class TableDetailsQuery
{
    private readonly ApplicationDbContext _context;
    public TableDetailsQuery(ApplicationDbContext context)
    {
        _context = context;
    }
    public List<TableDetailsViewModel> GetDetails()
    {
        var viewModel = _context.TableName
            .Select(x => new TableDetailsViewModel {
                Column1 = x.Column1Title,
                Column2 = x.Column2Title,
                Column3 = x.Column3Title,
                Column4PDT = x.PostedDateTime,
                Column5 = x.Column5Title,
            })
            .OrderByDescending(p => p.PostedDateTime)
            .ToList();

        return viewModel;
    }
}