Table Valued Functions in EF Core

Question

I am using EF Core 1.1 and have a query like

var list=from l in context.Users
         where l.SomeProp==someVal
         select l;

I have a UDF which returns a table of Id's and I basically want to generate the following query:

select * from Users where SomeProp=@someVal and SomeId in (select id from fn_myfunc(@id))

Is this possible to do?

Answer 1

I think you are limited to running a raw SQL query against the database to be able to use a table valued function. For example:

var query = @"SELECT * FROM Users WHERE SomeProp = {0} 
                AND SomeId IN (SELECT id FROM fn_myfunc({1})";

var users = context.Users
    .FromSql(query, someProp, someId)
    .ToList();

There are some limitations with this method. From the docs:

• SQL queries can only be used to return entity types that are part of your model. There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.
• The SQL query must return data for all properties of the entity type.
• The column names in the result set must match the column names that properties are mapped to. Note this is different from EF6.x where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.
• The SQL query cannot contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data.

You can return related data (i.e. Include) like this:

var users = context.Users
    .FromSql(query, someProp, someId)
    .Include(u => u.OtherThings)
    .ToList();

If you need to do anything more complex, then you would need to either drop down to using raw data access (like ADO.Net) or another ORM. I know people who use EF Core for the bulk of the work and then occasionally drop into Dapper for performance or raw SQL that doesn't suit EF.