I am trying to build a global search feature for an application that has ~40-50 tables in (and will continue to grow), with each table containing anything from 5-20 fields. I'm struggling to think of an efficient way to include a search feature.

A simplified example:

public class Vehicle : BaseDataEntity
{
    public string RegistrationNumber { get; set; }

    public string? VINNumber { get; set; }

    public string? Make { get; set; }

    public string? Model { get; set; }
}

public class VehicleInsurance : BaseDataEntity
{
    public string? InsuranceCompany { get; set; }

    public string? PolicyNumber { get; set; }

    public string? Type { get; set; }
}

public class Mortgage : BaseDataEntity
{
    
    public string? Type { get; set; }
    public string? Provider { get; set; }
    public string? AccountNumber { get; set; }
    public string? Amount { get; set; }
    public string? InterestRate { get; set; }
    public string? MonthlyPayment { get; set; }
    public string? StartDate { get; set; }
    public string? EndDate { get; set; }
    public string? SpecialRateStartDate { get; set; }
    public string? SpecialRateEndDate { get; set; }
}

I want to search the words "viva", I should match a search on a vehicle with model "viva" and vehicle insurance company "aviva", and whatever else matches.

With the number of tables/fields so large, I don't really want to have to code this out like

var vehicles = _dbContext.Vehicles.Where(v => v.RegistrationNumber.Contains(searchText) ||
                               v.Make.Contains(searchText) ||
                               ...And so on);
var insurances = _dbContext.VehicleInsurances.Where(v => v.InsuranceCompany.Contains(searchText) ||
                                                         v.PolicyNumber.Contains(searchText) ||
                                                         ...And so on);
...And so on with all the other tables

All I would be after is a reponse similar to something like this

public class SearchResponse
{
    public string ResultText {get;set;} // Give the full value back
    public string ResultId {get;set;} // Id of entity from BaseDataEntity
}

Can anyone point me to a better more efficient way (both coding and db performance) on how to achieve this?