View Model to display a list and single item-CodePudding

I have 3 tables like this: TableA TableB TableC

   MID | Name                         MID| someNumber           MID| Price
   ----| -----                     ------| -----                 ---- | -----   
   001 | Iphone                    001   | 02389                 001  | 434
   001 | Iphone X8                  001  | 02389                  001 | 34434
   003 | Iphone ns                   003   | 43533                 003 | 343
   003 | Tissue                   003    | 23123                 003  | 234
   006 | Bottle                   006    | 43453                 006  | 454

In the app, there're two search fields: search number and another field called search name

When a user hit submits, I want to make a query to these tables and present output as follows: if the number matches, I want to display all the records from all tables that matches the number searched BUT not as a list rather as a consolidated record (single record in a row) showing the total number of records.

For example if you search for 02389 there're two records of 001 in tableB so I want to output:

Number | Name | Price | TotalRecords
------------------------------------
02389  |Iphone|434    |  2

when you click on this record, it expands to show each row with the total number of records like this:

Number | Name | Price |
-----------------------
 02389 |Iphone|434    |  
 02389 |Samsung|34434 |

When u searched for 'name' it will make a fuzzy search to should all matching records of the name but will display data base on consolidated 'someNumber'. For example if you search for 'Iphone' there're three records with the word IPhone so the output will look like this:

 Number  | Name     | Price | TotalRecords
------------------------------------
 02389   |Iphone   |434     |  2     
 43533   |Iphone ns|434     |  1

My question now is, considering the view only accepts one model, I've use view model but how do I make the model to hold a single matching record plus another to hold a list and display in same view accordingly? my code snap is like this

  var userEnterSearchValue = from x in _db.tableB select x;
  userEnterSearchValue = userEnterSearchValue.AsNoTracking().Where(x => 
  x.Name.Contains(model.NameSearch) || x.Number.Contains(model.NumberSearch));
 
  var resultsFromDb = userEnterSearchValue.OrderBy(x => x.Name).ToList(); //this holds 
  matching search values from db

  foreach (var i in resultsFromDb)
  {
    var MID= i.MID;
    var getPrice = _db.TABLEC.Where(a => a.MID== 
    MID).FirstOrDefault().Price; //this is how how I get the price
   
   //How do I search to add up all the prices that match a particular number for example 
   get all prices in table C matching the number '02389' (in TABLE B)?
  
  }
                               
  ViewModel vm = new ViewModel
  {
  ListOfRecords = resultsFromDb = resultsFromDb.OrderBy(x => 
  x.SomeNumber).ToList(),

  //I need now to find a way to get a single record that match multiple numbers or name? 
  //I have started it off like this
  ConsolidatedRecord= resultsFromDb = resultsFromDb.GroupBy(x => 
  x.SomeNumber).Select(a=>a.First()).ToList(),
                        

  };
  return View(vm)

I just need to make the right linq query to join these data together so that it's display as a multiple lists and also a single list with consolidated matching number in the SAME view) hope that make sense? Any Help?

CodePudding user response：