EF MVC Core filtering a list is not working

A Cameron 0
Viewed 15

I have a PostgreSQL query which is beyond the capacity of a LINQ statement as far as I know or at least I haven't been able to figure it out, so I decided I would use raw SQL. Following are the relevant code snippets.

Controller:

public async Task<IActionResult> Index(string sortOrder, string currentFilter, string searchString, int? page)
        {
            ViewBag.CurrentSort = sortOrder;
            ViewBag.ConOrgSortParm = String.IsNullOrEmpty(sortOrder) ? "Client_desc" : "Client";
            ViewBag.AssignedSortParm = String.IsNullOrEmpty(sortOrder) ? "Assigned_desc" : "Assigned";
            ViewBag.ExpiresSortParm = String.IsNullOrEmpty(sortOrder) ? "Expires_desc" : "Expires";
            ViewBag.LastActiveSortParm = String.IsNullOrEmpty(sortOrder) ? "LastActive_desc" : "LastActive";
            ViewBag.IDSortParm = String.IsNullOrEmpty(sortOrder) ? "Id_desc" : "";

            if (searchString != null)
            {
                page = 1;
            }
            else
            {
                searchString = currentFilter;
            }

            ViewBag.CurrentFilter = searchString;

            var results = _context.NodeIndexViewModel.ToList();

            if (!String.IsNullOrEmpty(searchString))
            {
                results = results.Where(s => s.ContractingOrg.Contains(searchString)
                                       || s.NodeID.ToString().StartsWith(searchString));
            }

            
            int pageSize = 10;
            int pageNumber = (page ?? 1);
            return View(await results.ToPagedListAsync(pageNumber, pageSize));
        }

NodeIndexViewModel:

using NpgsqlTypes;
using System.ComponentModel.DataAnnotations;
using static UnidAdmin.Models.Bundle;

namespace UnidAdmin.Models
{
    public class NodeIndexViewModel
    {
        public int NodeID { get; set; }
        public string? Name { get; set; }
        public String? AssignedOrg { get; set; }
        public String? ContractingOrg { get; set; }
        public DateTime? Expiry { get; set; }
        [DisplayFormat(DataFormatString = "{0:MMM dd,yyyy}", ApplyFormatInEditMode = true)]
        public DateTime? LastActive { get; set; }
        [DisplayFormat(DataFormatString = "{0:MMM dd,yyyy}", ApplyFormatInEditMode = true)]
        public NodeType? NodeType { get; set; }
        public String? Comment { get; set; }
        
    }
}

DbContext ModelBuilder:

modelBuilder.Entity<NodeIndexViewModel>()
.ToSqlQuery("select DISTINCT ON(s.id) s.id as NodeID, s.name as Name, bn.node_type as NodeType, t.short_name as AssignedOrg, o.short_name as ContractingOrg, bn.end_utc as Expiry, s.active_date as LastActive, s.comment as Comment from admin.node s FULL OUTER JOIN admin.bundle_node bnn on s.id = bnn.node_id FULL OUTER JOIN admin.bundle bn on bnn.bundle_id = bn.id FULL OUTER JOIN admin.agreement ag on bn.agreement_id = ag.id FULL OUTER JOIN admin.organization o on ag.org_id = o.id JOIN admin.organization t on t.id = s.org_id order by s.id, bn.end_utc")
.HasKey(m => m.NodeID);

DbContext DbSet:

public DbSet<NodeIndexViewModel> NodeIndexViewModel { get; set; }

The initial load of the View works perfectly. The View code isn't relevant because the issue is arising when trying to filter the list in the controller with the following lines of code:

if (!String.IsNullOrEmpty(searchString))
  {
    results = results.Where(s => s.ContractingOrg.Contains(searchString)
              || s.NodeID.ToString().StartsWith(searchString));
  }

The solution won't build and I am getting CSO266 casting error.

This is the suggested fix:

results = (List<NodeIndexViewModel>)results.Where(s => s.ContractingOrg.Contains(searchString)
                                       || s.NodeID.ToString().StartsWith(searchString));

And this is the error message:

InvalidCastException: Unable to cast object of type 'WhereListIterator1[UnidAdmin.Models.NodeIndexViewModel]' to type 'System.Collections.Generic.List1[UnidAdmin.Models.NodeIndexViewModel]'.

model-view-controller asp.net-mvc
1 Answers

Try this

  var results = from s in _context.NodeIndexViewModel 
                 select s;

        if (!String.IsNullOrEmpty(searchString))
        {
            results = results.Where(s => s.ContractingOrg.Contains(searchString)
                                   || s.NodeID.StartsWith(searchString));
        }

        
        int pageSize = 10;
        int pageNumber = (page ?? 1);
        return View(await results.OrderBy(x => something).ToPagedListAsync(pageNumber, pageSize));

If you use list in the initial query, that would defeat the purpose of having PageList. As it loads everything out and consume memory. If you really need to use toList() on the initial query, you will need to include to list in the filter as well. So you will need to put tolist() on the filtering.

Finally IpageList won't work without ordering. So you need to put an order in order to get the pagelist to work.

Yat Fei Leong0
Related