1
\$\begingroup\$

I need help refactoring this code to dynamically generate a sort string that I can send to my data layer so that my database does the sorting instead of it happening in memory. I am using MVC4 with EF5 as my data layer.

public ActionResult InstanceSearch(int? page, string sortOrder, string computerName, string instanceName,
                                       string productName, string version)
    {
        int pageSize = 10;

        int pageNumber = (page ?? 1);
        if (pageNumber < 1)
        {
            pageNumber = 1;
        }

        if (string.IsNullOrEmpty(sortOrder))
        {
            sortOrder = "Computer";
        }

        ViewBag.CurrentSort = sortOrder;
        ViewBag.ComputerSort = sortOrder == "Computer" ? "ComputerDesc" : "Computer";
        ViewBag.InstanceSort = sortOrder == "Instance" ? "InstanceDesc" : "Instance";
        ViewBag.VersionSort = sortOrder == "Version" ? "VersionDesc" : "Version";
        ViewBag.ProductSort = sortOrder == "Product" ? "ProductDesc" : "Product";

        //IEnumerable<Instance> instances = ecuWebDataContext.Instances;

        IQueryable<Instance> instances
        = string.IsNullOrEmpty(computerName) == true ? ecuWebDataContext.Instances : ecuWebDataContext.Instances.Where(i => i.Computer.Name == computerName);

        if (string.IsNullOrEmpty(instanceName) == false)
        {
            instances = instances.Where(i => i.Name == instanceName);
        }

        if (!string.IsNullOrEmpty(productName))
        {
            ProductName product = (ProductName)Enum.Parse(typeof(ProductName), productName);
            instances = instances.Where(i => i.Product.Name == product);
        }

        if (!string.IsNullOrEmpty(version))
        {
            instances = instances.Where(i => i.Version == version);
        }

        switch (sortOrder)
        {
            case "Computer":
                instances = instances.OrderBy(i => i.Computer.Name);
                break;

            case "ComputerDesc":
                instances = instances.OrderByDescending(i => i.Computer.Name);
                break;

            case "Instance":
                instances = instances.OrderBy(i => i.Name);
                break;

            case "InstanceDesc":
                instances = instances.OrderByDescending(i => i.Name);
                break;

            case "Version":
                instances = instances.OrderBy(i => i.Version);
                break;

            case "VersionDesc":
                instances = instances.OrderByDescending(i => i.Version);
                break;

            case "Product":
                instances = instances.OrderBy(i => i.Product.Name);
                //instances = instances.OrderBy(i => Enum.Parse(typeof(ProductName), i.Product.Name));
                break;

            case "ProductDesc":
                instances = instances.OrderByDescending(i => i.Product.Name);
                //instances = instances.OrderByDescending(i => Enum.Parse(typeof(ProductName), i.Product.Name));
                break;
        }
        ViewBag.SortOrder = sortOrder;

        var instanceSearchModel = new InstanceSearchModel { ComputerName = computerName, InstanceName = instanceName };
        ViewBag.ComputerName = computerName;
        ViewBag.InstanceName = instanceName;
        ViewBag.ProductName = productName;
        ViewBag.InstanceCount = instances.Count();
        ViewBag.Version = version;
        return View(instances.ToPagedList(pageNumber, pageSize));
    }

I tried returning an IQueryable from the data layer, but then I run into connection problems because I never close it.

\$\endgroup\$
2
  • \$\begingroup\$ Does this work correctly? If not, then you need to get it working before posting here. \$\endgroup\$
    – Leonid
    Jan 4, 2013 at 3:13
  • \$\begingroup\$ Yes this works correctly. However I have wrappers around the datalayer to hide it behind interfaces so I can swap out databases without affecting the application and this code uses the database context directly which has the intened effect of offloading to the database for the sorting, but I am looking for help building the sort string then sending it completely to my data layer. \$\endgroup\$
    – twreid
    Jan 4, 2013 at 16:53

1 Answer 1

1
\$\begingroup\$

The data layer that appears to be causing your problem is the root cause.

You're abstracting over an abstraction, and in the process having to marshall sort orders around as a result. Prefer injecting your context in a request scope and remove the abstraction.

You can always create an abstraction over the specific query, and inject the query, which has a dependency on the context, if you really need to.

\$\endgroup\$
1
  • \$\begingroup\$ Ok thank you i will get started on doing that. \$\endgroup\$
    – twreid
    Jan 11, 2013 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.