Table-Valued Parameter from local List with Join of DbSet

[philippk80]

Hello,
I'm curious if it's possible to build something like that with ef core:

Using a table-valued parameter and fill it with data from a local List using a DataTable.
After this join it with DbSet and the table-valued parameter as table for the inner join.

I have done it with a rawsql. Is there a way to map and fill the table-valued parameter type and build this with ef core on the fly? (without using rawsql at all)

best regards

[roji]

Duplicate of #30426

[roji]

This is something we're actively working on at the moment - but with JSON rather than with table-valued parameters or DataTables. SQL Server TVPs require a type to be defined ahead of time, which isn't appropriate for a component such as EF Core. In addition, this is a SQL Server-specific feature which doesn't exist in other databases.

In contrast, SQL Server OPENJSON allows us to send an array of (primitive values) as a JSON string, and unpack that as a table in the database, allowing JOINs and other similar techniques. Benchmarks show that TVPs are actually slower than this OPENJSON, and very similar techniques around JSON exist in all other major databases as well.

See specifically #13617 (comment) for using this to translate Contains over an array of primitive values, and for benchmarks.

[yv989c]

Hey @philippk80, while you're waiting, you can give QueryableValues a try. It does exactly what you're asking for, and it even supports OPENJSON behind the scenes.

If you want to see an example that relates to your specific use case, check out this. It shows you how to join data from your local list directly into your EF query.

By the way, I'm assuming that you're using SQL Server.