Implementing Specification pattern with a join in C# and Entity Framework Core

I am using a Domain Driven Design approach to specifying filters on items returned from a Repository - but now I need to filter on a related table for which I do not have navigation properties.

I have the following tables

Group
===
uniqueidentifier Id
nvarchar(32) Name

PersonGroupMembership
===
uniqueidentifier Id
uniqueidentifier PersonId
uniqueidentifier GroupId

Person
===
uniqueidentifier Id
nvarchar(32) Name

My C# classes, which each have a DbSet<T> on my DbContext, are as follows

public class Group
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public class PersonGroupMembership
{
    public Guid Id { get; set; }
    public Guid PersonId { get; set; }
    public Guid GroupId { get; set; }
}

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

My current specification interface looks like this

public interface ISpecification<T>
{
	IQueryable<T> Apply(IQueryable<T> query);
}

And my SearchAsync method on a repository looks (something) like this

public Task<T[]> SearchAsync(ISpecification<T>[] specifications)
{
	if ((specifications?.Length ?? 0) == 0)
		throw new ArgumentException(
			paramName: nameof(specifications),
			message: "At least one specification is required");

	IQueryable<T> query = DbSet.AsQueryable<T>();
	foreach(var specification in specifications)
		query = specification.Apply(query);

	return query.ToArrayAsync();
}

An example specification

public class FilterHasEmailAddress : ISpecification<Person>
{
	public required string EmailAddress { get; init; }

	public IQueryable<Person> Apply(IQueryable<Person> query) =>
		query.Where(x => x.EmailAddress == EmailAddress);
}

Assuming that an example of what you want to achieve is to query Person by the Group name, the right answer is of course that you have to create the relationships in the model, but you probably have reasons not to do that (perhaps because these are loosely coupled aggregations and these objects are nowhere part of the same aggregate)

This is the eternal problem of the repository pattern. You implement everything, including the queries, using the high level entities model and abstract away the database completely. The specification pattern even gives you a high level customization for very specific queries so you think all is well.
But this ties you completely to relationships that exist in the models and it forbids you from using database specific constructs such as many of the EF.Functions that only apply to SQL servers. Moreover, when writing these database agnostic constructs you constantly have to take into account that your high level LINQ statement can actually be translated to the database that you are using (probability of which is quite bad for some databases such as MongoDB). True database agnosticity is almost impossible to achieve.

As you cannot forget about the database anyway I usually prefer to have a hard and explicit boundary between the repositories and the domain so that in your domain you truly have no worries about database details.

In this case, I think I would recommend a separate repository where you would put everything that you cannot do with the existing repositories and the Specification Pattern. So most of your code is nice and database agnostic (albeit only superficially for the reasons mentioned above) and the dirty stuff you cannot do with that you can put in a separate repository that you expose as a domain service but implement in the deepest database layer you've got.

In a big enough project you will inevitably run into a situation where you need database specific constructs, either because a query requires something very specific which is not supported through the LINQ-to-SQL translation, or because you need to do some heavy optimization.