ASP.NET Web API searching multiple tables

Question

I have a search function that searches for a specific word in two related tables and one unrelated table. Owner table and Registration table are related, and Vehicle table has no relations to the other two tables. I've tried multiple ways of approaching this problem, but non of them have worked. Below is the code in question.

What I have tried so far was placing three tables within a ViewModel and called it within my controllers search method.

Owner Table
public int OwnerId {get;set;}
public string FirstName {get;set;}
public string LastName {get;set;}

Registration Table
public int RegistrationId {get; set;}
public DateTime RegisteredDate {get; set;}
public string RegistrationLocation {get;set;}
public int OwnerId {get;set;}

Vehicle Table
public int VehicleId {get;set;}
public string VehicleName {get;set;}
public DateTime VehicleYear {get;set;}
public string Model {get;set;}

SearchViewModel

public IEnumerable<Owner> Owners {get;set;}
public IEnumerable<Registration> Registrations {get;set;}
public IEnumerable<Vehicle> Vehicles {get;set;}

Controller

[HttpGet]
 public SearchViewModel Search(string searchString)
{ 
   SearchViewModel searchVM = new SearchViewModel();
   searchVM.Owner = searchVM.Owner.Where(o => o.FirstName.Contains(searchString));
   searchVM.Registration = searchVM.Registration.Where(r => r.RegistrationLocation.Contains(searchString));
   searchVM.Vehicle = searchVM.Vehicle.Where(v => 
   v.Model.Contains(searchString));

   return searchVM;
}

Another way I've tried is:

[HttpGet]
 public IActionResult Search(string searchString)
{ 

 var owner = from owners in _context.Owner select owners; 
 var registration = from registrations in _context.Registration select 
 registrations; 
 var vehicle = from vehicles in _context.Vehicle select vehicles; 


   owner = owner.Where(o => o.FirstName.Contains(searchString));
   registration = registration.Where(r => 
   r.RegistrationLocation.Contains(searchString));
   vehicle = vehicle.Where(v => 
   v.Model.Contains(searchString));

   return Ok(); //I'm not sure how to return the three.
}

What isn't working? Are you ever actually querying the database? It looks like you're querying the view model itself, but where does it get its data? — David
– David, Commented Feb 5, 2019 at 19:07
Hi David, that is probably where I'm going wrong. I need to query the database. How do I go about doing that? — Chris H.
– Chris H., Commented Feb 5, 2019 at 19:08
@ChrisH.: If you currently have no code which queries a database at all then where you'd start is basically a tutorial on using a database in ASP.NET MVC/WebAPI. Entity Framework is usually the framework used for that purpose. — David
– David, Commented Feb 5, 2019 at 19:09
I was thinking of this method => var owner = from owners in _context.Owner select owners; var registration = from registrations in _context.Registration select registrations; var vehicle = from vehicles in _context.Vehicle select vehicles; — Chris H.
– Chris H., Commented Feb 5, 2019 at 19:15

David · Accepted Answer · 2019-02-05 19:28:06Z

1

Combine your two attempts. In the first attempt you're building a view model, but not querying the database. In the second attempt you're querying the database, but not building a view model. Do both.

For example:

var searchVM = new SearchViewModel();

searchVM.Owners = _context.Owner.Where(o => o.FirstName.Contains(searchString));
searchVM.Registrations = _context.Registration.Where(r =>  r.RegistrationLocation.Contains(searchString));
searchVM.Vehicles = _context.Vehicle.Where(v => v.Model.Contains(searchString));

return searchVM;

answered Feb 5, 2019 at 19:28

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris H. Over a year ago

you're a genius. Thank you so much. Another thing I've noticed is that when I'm searching for a specific word, it only retrieves results that match the data in the database. How can I search for results that are not case sensitive? For example, I want to be able to search for "car" & "Car". Right now, I'm only able to retrieve results based on "Car"

David Over a year ago

@ChrisH.: There may be a more elegant way to do it, but I usually just append .ToLower() to the relevant strings so they're all lowercase. For example: .Where(o => o.FirstName.ToLower().Contains(searchString.ToLower()))

Chris H. Over a year ago

You've done it again @David. Thank you again for your help!

Collectives™ on Stack Overflow

ASP.NET Web API searching multiple tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related