Calculated fields on the server side

I would like to add a calculated field to a “Person” table, for example a Fullname field which would be derived from concatenating Firstname + " " + Lastname. (I realize this could probably also be done on the client side in this case, but other more complex scenarios would require a server side computation)

Can I create a Person.Custom.cs partial class with the new Fullname property?

Hi @semafox,

Yes, you can create a partial class and add the new property. However there are a few other things required. Here is the full procedure of adding a FullName property to the Employee entity from the Northwind database (that already has FirstName and LastName properties).

  1. Create a partial class in the server\models\Northwind\ directory and add the property (the empty setter is needed):
    public partial class Employee
    {

        public string FullName
        {
            get
            {
                return this.FirstName + " " + this.LastName;
            }

            set
            {
            }
        }
    }
  1. Create a new partial class that will extend the EF context in order to tell it to ignore that calculated property:
    public partial class NorthwindContext
    {
        partial void OnModelBuilding(ModelBuilder builder)
        {
            builder.Entity<Employee>().Ignore(e => e.FullName);
        }
    }
  1. Finally use that property in Radzen. It won't display in autocomplete and dropdowns so you have to type it in:

In the end the computed property should display in a DataGrid like this:

That works great! Very nice!

One question though: filtering on this field works but is case sensitive, how can I change that?

Can you try the following:

  1. Change the property definition to:
        public string FullName
        {
            get;
            set;
        }
  1. Create a new partial file for the controller of that entity e.g. EmployeeController with the following method:
  public partial class EmployeesController
  {
      partial void OnEmployeesRead(ref IQueryable<Employee> items)
      {
          items = items.Select(item => new Employee{
              EmployeeID = item.EmployeeID,
              FirstName = item.FirstName,
              FullName = item.FirstName + " " + item.LastName
          });
      }
  }

What this does is to change the place where the field calculation takes place. This one should do it at DB level (in SQL). Then if your SQL collation is set to ignore case filtering should work as expected.

Will this work on an expanded table? Like if you had Employee expanded in the Orders grid, could you search on FullName from there? I have a similar case and can't get it to work.

Actually, I can't get the search to work at all, even on the Employees grid. Getting this error in the Network > Response tab:
System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(e => e.FullName.Contains(__TypedProperty_0))' could not be translated. Additional information: Translation of member 'FullName' on entity type 'Employee' failed. This commonly occurs when the specified member is unmapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Seems like it's looking for a field in the database...

OK, I was able to get it working. I had been incorrectly using a foreach loop in the OnEmployeesRead method instead of the items.Select method shown here. I just figured a foreach loop would do the same thing, but evidentially not. Shows how much I understand C# I guess.

One question for you C# gurus, do I have to name all the members when I add one like this? In TypeScript there is the spread operator that would make something like this very easy.
Something like:

  items = items.Select(item => new Employee {
    ...item,
    FullName = item.FirstName + " " + item.LastName
  });

Is there anything like that in C#?

Thanks, Dan

With the help of a colleague, I did find a solution to keep from having to innumerate all the properties.
I used the AutoMapper package and it works great.
For anyone interested, here is how it works.

  1. Run this from the command prompt, in the project server directory:
    dotnet add package AutoMapper
  2. In your Models > Northwind > Employee.Custom.cs (you have to Ignore Collections):
namespace Northwind.Models.NorthwindDb
{
  public partial class Employee
  {
    public string FullName { get; set; }
  }
  public class EmployeeProfile: AutoMapper.Profile
  {
    public EmployeeProfile()
    {
      CreateMap<Employee,Employee>()
        .ForMember(dest => dest.Employees1, opt => opt.Ignore())
        .ForMember(dest => dest.Orders, opt => opt.Ignore())
        .ForMember(dest => dest.EmployeeTerritories, opt => opt.Ignore())
        .ForMember(dest => dest.FullName,
          opt => opt.MapFrom(src => src.FirstName + " " + src.LastName)
        );
    }
  }
}
  1. In Controllers > NorthwindDB > EmployeeController.Custom.cs:
    using System.Linq;
namespace Northwind.Controllers.NorthwindDb
{
  using Northwind.Models.NorthwindDb;
  using AutoMapper.QueryableExtensions;

  public partial class EmployeesController
  {
    partial void OnEmployeesRead(ref IQueryable<Employee> items)
    {
      var config = new AutoMapper.MapperConfiguration(cfg => cfg.AddProfile<EmployeeProfile>());
      items = items.ProjectTo<Employee>(config);
    }
  }
}
  1. Then, to keep the ModelBuilder happy, here is my Data > NorthwindDBContext.Custom.cs file:
using Microsoft.EntityFrameworkCore;
using Northwind.Models.NorthwindDb;
namespace Northwind.Data
{
  public partial class NorthwindDbContext
  {
    partial void OnModelBuilding(ModelBuilder builder)
    {
      builder.Entity<Employee>().Ignore(e => e.FullName);
    }
  }
}

I think you could also just use [NotMapped] in the Model definition, instead of the last step. Not sure about that as I didn't try it.
Thanks, Dan

Hey @danh,

Please format your code. You can use markdown code blocks.

OK, I think I got it now.