Typesafe SQL queries

[Wimmen]

In my project I use EF 9 for the default crud operations and SQL Kata for writing queries for list tables with pagination and filtering.

To make the SQL Kata queries use the entities created by EF I have created a helper class:

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace Project.Domain.Context
{
    public class EntityBuilder<T>(DbContext context, string alias)
        where T : class
    {
        private readonly string _tableName = context.Set<T>().EntityType.GetTableName() ?? context.Set<T>().EntityType.GetViewName() ?? string.Empty;
        private readonly IEntityType _entityType = context.Set<T>().EntityType;

        public string Table()
        {
            return _tableName + " as " + alias;
        }
        
        public string? Column<TProperty>(Expression<Func<T, TProperty>> property)
        {
            var name = property.Body.ToString().Split(".")[1];
            if (string.IsNullOrEmpty(_tableName) || string.IsNullOrEmpty(name))
            {
                return null;
            }

            return alias + "." + _entityType.FindProperty(name)?.GetColumnName();
        }

        public string? ColumnRaw<TProperty>(Expression<Func<T, TProperty>> property)
        {
            var name = property.Body.ToString().Split(".")[1];
            if (string.IsNullOrEmpty(_tableName) || string.IsNullOrEmpty(name))
            {
                return null;
            }

            return $"[{alias}].[{_entityType.FindProperty(name)?.GetColumnName()}]";
        }
    }
}

And now I can create queries like this:

var webUserTable = new EntityBuilder<WebUser>(DbContext, "user");
var contactTable = new EntityBuilder<Contact>(DbContext, "contact");
var companyTable = new EntityBuilder<Rasclient>(DbContext, "company");

var users = queryFactory.Query(webUserTable.Table());
users = users.Join(contactTable.Table(), c => c
            .WhereRaw("Convert(varchar, " + contactTable.ColumnRaw(x => x.Id) + ") = " + webUserTable.ColumnRaw(x => x.ContactId))
            .OrOn(contactTable.Column(x => x.Username), webUserTable.Column(x => x.ContactId)));

users = users.LeftJoin(companyTable.Table(), c => c
            .On(companyTable.Column(x => x.Clientid), contactTable.Column(x => x.CompanyId)));

users = ApplyFilter(users, filter, webUserTable, contactTable, companyTable);

The syntax can probably be improved, but maybe a start for an EF addon.