Is there a way to mimic Entity Framework Core’s navigation properties in a many-to-many relationship without foreign keys?

Adding a many-to-many table in this scenario would generate hundreds of millions of rows in a many-to-many table, which I’m trying to avoid for disk space reasons.

This scenario seems like foreign keys may be unnecessary:

1. There is an entity Trade (row in a database table) with properties EntryDateTime and ClosedDateTime. There are millions of Trade rows and this number will grow exponentially.

2. A trade has many Bar, which are all rows in the table/entity Bar that have a DateTimeOpen between the Trade.EntryDateTime and Trade.ClosedDateTime. There are approximately 80 million Bar rows, but there will be many, many more eventually.

3. A Trade has at least one Bar and could theoretically contain every Bar, but typically would have between 1,000 and maybe 20,000. A Bar could belong to millions of Trade rows.

4. In some situations, I’d like to select many rows from the Trade table and include every Bar that has a Bar.DateTimeOpen between the Trade.EntryDateTime and Trade.ClosedDateTime. For example, it would be convenient to use await context.Trades.Include(t => t.Bar).ToListAsync()

A quick workaround is to query the Bar table for each trade, but (obviously) firing off thousands of queries to load related Bar items for the desired Trade entities isn’t good.

Do you have any suggestions on the standard EF Core way to approach this problem?

I’m using the following versions of .NET and Entity Framework Core at the moment (they are the latest) and can use any desired library or package:

• Microsoft.EntityFrameworkCore: 8.0.8
• .NET 8 (could upgrade to 9)
• Database: Clickhouse (could change database provider if needed)