Clever Geek Handbook

Table Values ​​in EF Core

I am using EF Core 1.1 and have a request like

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

I have a UDF that returns an Id table and I basically want to generate the following query:

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

Can this be done?

+3


source to share


1 answer


I think you are limited to running a raw SQL query against the database in order to be able to use the table-aware 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 object types that are part of your model. Our lag has an improvement to allow ad-hoc types to be returned from raw SQL queries.
  • The SQL query must return data for all properties of the entity type.
  • Column names in the result set must match the names of the columns to which the properties are mapped. Note that this differs from EF6.x where property / column mapping was ignored for raw SQL queries and the result set column names must match property names.
  • The SQL query cannot contain related data. However, in many cases, you can top the query with an Include statement 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 something more complex, you will either have to abandon the use of raw data access (like ADO.Net) or another ORM. I know people who use EF Core for most of the work and then sometimes switch to Dapper for performance or raw SQL, which is not an EF suit.

+4


source






More articles:


All Articles