Splitting a large dataset into smaller groups

Question

I'm trying to build a sidebar search navigation filters of check boxes and radio buttons. I'm getting the values from a database. Something like the following, but with 12 filter categories total:

Color
    [] red
    [] green
    [] blue 
Size
    [] small
    [] medium
    [] large
Shape
    [] square
    [] circle
    [] triangle

It is working for me using something like the code below. But it seems really inefficient to make a database call for each of the sub-categories:

public ActionResult Index ()
{
    SearchBarViewModel model = new SearchBarViewModel();
    model.Color = GetValuesFromDb();
    model.Size = GetValuesFromDb();
    model.Shape = GetValuesFromDb();
    return View(model)
}

I'm guessing there is a more efficient way to do this by making a single database query, returning a large dataset that contains all the category values and them split then into groups with linq? I'm just not sure how this would be done?

Database Schema*

SearchKey           SearchValue
---------           -----------------
Id  Name            Id  KeyId   Value
---------           -----------------
1   Color           1   1       Red
2   Size            2   1       Green
3   Shape           3   1       Blue
                    4   2       Small
                    5   2       Medium
                    6   2       Large

Sql Query

SELECT sv.Id, sv.Value
FROM SearchKey sk
JOIN SearchValue sv ON sv.KeyId = sk.Id
WHERE sk.Name = @ValuePassedToSP

could use GroupBy https://msdn.microsoft.com/en-us/library/system.linq.enumerable.groupby(v=vs.110).aspx but all possible solutions highly depend on how how the table you are querying looks like — Rand Random, Feb 14 '17 at 14:34
If `Color`, `Shape`, and `Size` are all in the same database table.. then you could just do 1 call to the database.. store all records into a list.. then just filter that list based on the type of records you want. — Grizzly, Feb 14 '17 at 14:35
this answer can help you http://stackoverflow.com/a/15124989/335905 — celerno, Feb 14 '17 at 14:39

score 1 · Accepted Answer · answered Feb 14 '17 at 15:36

It might or might not be a little early in your development to be concerned about performance of db calls. If the menu values are not changing often or in different contexts, it can make more sense to have the menu structure stored in the database like you do. If the menu values are not changing often, it might be better to store them in a program code or settings file that is only loaded when your app is first loaded, or maybe at demand after that.

I think the linq in context you are looking for might go something like this, where the "GetALLSearchValuesFromDb()" method returns an IEnumerable generated by a SQL statement like you have already, only without the WHERE clause:

public ActionResult Index ()
{
    SearchBarViewModel model = new SearchBarViewModel();
    var searchvalues = GetALLSearchValuesFromDb();
    model.Color = searchvalues.Where(sv => sv.Name == "Color");
    model.Size = searchvalues.Where(sv => sv.Name == "Size");
    model.Shape = searchvalues.Where(sv => sv.Name == "Shape");
    return View(model)
}

Yes, this is like the solution I was thinking of, I just couldn't visualize it until I saw it. Thanks for the helpful post! — PixelPaul, Feb 14 '17 at 15:53

Splitting a large dataset into smaller groups

1 Answers1