Searching by strings concatenation

[DariuszRKozlowski]

Hi, Using Blazor, I have a model which contains, among other things, two properties: FirstName and LastName. As a result, I receive a grid with two separated columns with first and last name. Searching feature for the records is enabled. Lets assume that grid contains a record with first name: John and last name: Smith. When I search for "John", the record is found, when I search for "Smith" - the record is also found. But when I am trying to search for "John Smith", I receive: "There are no items to display". I guess why it happens, but I would like to ask if there is any workaround to create a column with concatenation of first and last name (this column should be hidden) and enable to search by both values (get the result for: "John Smith"). I read the documentation and tried to add "Not connected column", but it does not work (I found the information that sorting and filtering will not work, but there was nothing about searching). Because the data comes from database, I would like to avoid adding a new column in database with redundant informations from other columns. Do You have any tips, ideas or implemented workaround for this kind of problem?

Best regards Dariusz

[gustavnavar]

A way to make it work is to define a database view as described bellow.

In your case the view should be like this (I assume your table name is User):

CREATE VIEW [dbo].[UserVM]
    AS
    SELECT        dbo.User.Id, CONCAT(dbo.User.FirstName, ' ', dbo.User.LastName) AS UserName
    FROM            dbo.User
    
    GO

You also have to create a class for the view:

    public class UserVM
    {
        [Key]
        public long Id { get; set; }
        [JsonIgnore]
        public virtual User User { get; set; }
        public string UserName { get; set; }
    }

And modify the User model to include a property for the view:

    public class User
    {
        [Key]
        public long Id { get; set; }
        public virtual UserVM UserVM { get; set; }
        ...
        ...
    }

Then you have to add the following one-to-one relationship for the User table and UserVM view in the OnModelCreating method of the context:

    builder.Entity<UserVM>()
                .ToView("UserVM");

    builder.Entity<UserVM>()
                .HasOne(u => u.User)
                .WithOne(a => a.UserVM)
                .HasForeignKey<UserVM>(b => b.Id);

and the following attribute in the context:

    public DbSet<UserVM> UserVM { get; set; }

Then you have to add a column for the UserVM.UserName property in the grid column definition:

    Action<IGridColumnCollection<User>> columns = c =>
    {
        c.Add(c => c.Id).Titled("Id").SetPrimaryKey(true);
        c.Add(c => c.UserVM.UserName).Titled("User Name");
        ...
        ....
    };

And finally you have to modify the service to get the source from the view model:

    public ItemsDTO<User> GetAllRecords(Action<IGridColumnCollection<User>> columns, QueryDictionary<StringValues> query)
    {
        using var context = this.Context;

        var server = new GridServer<User>(context.User.Include(r => r.UserVM), new QueryCollection(query), true, "myGrid", columns, 10)
            .Filterable()
            .Searchable()
            .Sortable()
            .WithGridItemsCount()
            .WithMultipleFilters();

        return server.ItemsToDisplay;
    }