1

I am trying to put a simple ASP.Net Core 3 MVC Application together and have followed several tutorials in getting a single input box to search against a single SQL Server table column which works fine, sadly I am a little lacking in knowledge on how to perform the next part....

My question is:

How do I put together a search where I might have several input boxes against a single column, or several input boxes against several columns?

i.e.

input1, input2, input3, input4, input5, input6 on the view, to be able to search against SQL Database table dbo.Products on the Column/Field "Description"?

Ideally, all input boxes can be left blank or be filled in to filtered data.

The reason I require several search boxes against a column is because the 'description' field can contain a lot of information that is in no particular order.

i.e.

Description: product category, product name, material1, material 2, material 3, material 4, size, rating, colour, thickness

The existing Access front-end has several search boxes so a user can filter previous purchased items:

name, size, colour or rating, material 4, product name.

The tutorial set has the following lambda expression to perform the search:

View = Views/Raptors/Index.cshtml

<form asp-controller="Raptors" asp-action="Index">
    <p>
        Position: <input type="text" name="searchString" />
        <input type="submit" value="Filter" />
        College: <input type="text" name="searchCollege" />
        <input type="submit" value="Filter" />
    </p>
</form>

Controller = Controllers/RaptorsController

// GET: Raptors
//public async Task<IActionResult> Index() // hidden to add search
public async Task<IActionResult> Index(string searchString)
{
var raptor = from m in _context.Raptors select m; // linq query alternative to sql query

// added for the search function
if (!String.IsNullOrEmpty(searchString)) // if it's not empty...
{
raptor = raptor.Where(s => s.PlayerPosition.Contains(searchString));
}
// end of search function

//  return View(await _context.Raptors.ToListAsync()); // hidden to add search
return View(await raptor.ToListAsync());
}

Being honest, I am still trying to get my head around what is actually going on in the controller, how things refer to each other. That is part of my battle here.

The updated example is more geared to two search boxes for two fields respectively, but I'd still like to be able to search one field by more than one search box because of all the variables in that one field.

I have been able to use the two search boxes in my example but they both have to be filled in, ideally I would like to be able to search using any of the input boxes blank or otherwise.

Craig Sunderland
  • 11
  • 4
  • "several input boxes against a single column"...why / how would that become necessary? You mean the user can search for 6 different terms in one go? It's possible I guess, but it's an unusual use case (and it might not be very fast). – ADyson Feb 13 '20 at 11:09
  • Anyway without seeing your current code it's hard to be specific about how to change it, but in basic SQL terms if you want to be able to search for more than one thing, you're probably looking to add `OR` sections to your `WHERE` clause. And you might only want to add each section to your final SQL command if the user actually typed something in the specific box which that section handles. So the concept is essentially building up the elements of the query gradually, based on what's been typed in. – ADyson Feb 13 '20 at 11:11
  • But I'm not really sure they need to be able to search for 6 different things in the _same field_ simultaneously. I've never seen a search tool like that. 6 search boxes to search six different fields, yes, quite common, but not all pointing to one field. Like I said - it could easily result in an inefficient query, and some big result sets. Normally people would just expect to repeat the search using a different term, if it didn't come up with something the first time. – ADyson Feb 13 '20 at 11:13
  • Well I've given you the general concept, above. That's about as much as I can do without seeing your current code and understanding how you run the query now. If you want to edit your question to include more detail then I (and others) can have another look at it. – ADyson Feb 13 '20 at 11:22
  • Would there happy to be any insight as to what I may need to look into? – Craig Sunderland Feb 14 '20 at 14:02
  • Since you're using Linq-to-entities to build your query, this might help you construct a dynamic WHERE clause: https://stackoverflow.com/questions/14621450/dynamic-where-clause-or-in-linq-to-entities – ADyson Feb 14 '20 at 14:11
  • Thank you for the push. After looking around and gaining a little more understanding, I have been able to pass multiple parameters using named input boxes to filter the text. I have follow on questions which I will ask separately. Thanks again. – Craig Sunderland Mar 11 '21 at 17:49

0 Answers0