How do I loop through all tables to set query filter automatically?

[ww9]

Hi! Instead of having to set Tenantid query filter individually for each table, I'd like to set it for all tables that have TenantId column.

Like this (see comments in code):

public partial class MyDb : LinqToDB.Data.DataConnection
{
public long currentTenantId = 0;

partial void InitMappingSchema()
{
	// Instead of having to specify query filter for each table like this:
	var builder = MappingSchema.GetFluentMappingBuilder();
	builder.Entity<MyTable>().HasQueryFilter<MyDb>((q, db) => q.Where(e => e.TenantId == currentTenantId));
	builder.Entity<MyTable2>().HasQueryFilter<MyDb>((q, db) => q.Where(e => e.TenantId == currentTenantId));

	// I would like to set it for all tables that have "TenantId" columns. But I can't get this to work:
	var schema = DataProvider.GetSchemaProvider().GetSchema(this);
	foreach (var table in schema.Tables)
	{
		if (table.Columns.Where(c => c.ColumnName == "TenantId").FirstOrDefault() != null)
		{
			builder.Entity<table.GetType()>().HasQueryFilter<MyDb>((q, db) => q.Where(e => e.TenantId == currentTenantId));
		}
	}
}
}

Environment details

linq2db version: Release 4.0.0-preview.1
Database Server: MariaDB 10.4
Database Provider: MySql.Data 8.0.25
Operating system: Windows 10 x64
.NET Framework: .NET 5.0

[sdanyliv]

Try this sample. It registers all entities which are part of MyDb class. For sure constructor should be corrected for your needs.

public partial class MyDb : LinqToDB.Data.DataConnection
{
    public long currentTenantId;

    public MyDb()
    {
        AddMappingSchema(InitializedMappingSchema);
    }

    private static MethodInfo RegisterTenantMethodInfo =
        MemberHelper.MethodOfGeneric(() => RegisterTenant<int>(null));

    private static MappingSchema InitializedMappingSchema = InitMappingSchema();

    public static MappingSchema InitMappingSchema()
    {
        var ms      = new MappingSchema();
        var builder = ms.GetFluentMappingBuilder();

        var props = typeof(MyDb).GetProperties().Where(p => typeof(ITable<>).IsSameOrParentOf(p.PropertyType));
        foreach (var prop in props)
        {
            var mi = RegisterTenantMethodInfo.MakeGenericMethod(prop.PropertyType.GetGenericArguments()[0]);
            mi.Invoke(null, new object?[] {builder});
        }

        return ms;
    }

    public static void RegisterTenant<T>(FluentMappingBuilder builder)
    {
        var prop = typeof(T).GetProperty("TenantId");
        if (prop == null)
            return;

        var entityParam   = Expression.Parameter(typeof(T), "e");
        var dbParam       = Expression.Parameter(typeof(MyDb), "db");

        var currentTenant = (Expression)Expression.PropertyOrField(dbParam, nameof(currentTenantId));
        if (currentTenant.Type != prop.PropertyType)
            currentTenant = Expression.Convert(currentTenant, prop.PropertyType);

        var filterLambda =
            Expression.Lambda<Func<T, MyDb, bool>>(
                Expression.Equal(currentTenant,
                    Expression.MakeMemberAccess(entityParam, prop)), entityParam, dbParam);

        builder.Entity<T>().HasQueryFilter(filterLambda);
    }


    public ITable<SampleClass> Some => GetTable<SampleClass>();
}

[ww9]

Hi @sdanyliv, I don't know how you find the time to be so helpful.
This was clearly a problem with my lack of C# knowledge and not with LINQ to DB project.
And yet you still found the time to help me. I just wanted to let you know that you're amazing.
Is there some way I can contribute financially to you? Even if a small amount. Just a token of my gratitude.
I also plan to contribute to linq2db project once my endeavour becomes profitable.