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. |