Whats the best solution to Entity Framework cores lack of moderate LINQ query support?

.net c# entity-framework-core linq sql-server

Question

So basically I have a table containing a set of data. This data is then joined onto an organisation table to which multiple users can be apart of. Im then trying to get all files in the table where the user executing the query, has permission to access the organisation. To do this I'm using a where clause that checks the users permissions from the application, to the files that have them organisations linked. Im then selecting the top 100 results and counting the records returned. (I want to see if the user has access to 100+ files over all the organisations).

The problem is when I use the following LINQ query:

(from f in File
 join o in Organisation on f.OrganisationId equals o.Id
 where permissions.Contains(o.Id.ToString())
 select f).Take(100).Count();

The take and the count aren't executed on the SQL server and are run in memory when I try a contains on a list which should convert to an IN (VALUES) query on SQL. I have 70,000+ File records and this is very slow and times out on a web server. This is expected as Entity Framework core is in early stages and does not support moderate or advanced LINQ queries yet.

My question is, is there a better alternative to raw SQL queries while still being able to filter by an array of items and still using Entity Framework core v1.1? Thanks.

Edit: I tried updating to the latest version, this still did not solve my issue as I still got the following output.

The LINQ expression '{permissions => Contains([o].Id.ToString())}' could not be translated and will be evaluated locally.
The LINQ expression 'Contains([o].Id.ToString())' could not be translated and will be evaluated locally.
The LINQ expression 'Take(__p_1)' could not be translated and will be evaluated locally.
The LINQ expression 'Count()' could not be translated and will be evaluated locally. 
1
2
5/4/2017 12:49:58 PM

Accepted Answer

The warnings are misleading - the problem is the ToString() call which causes client evaluation of the query.

The following should produce the intended SQL query:

var idList = permissions.Select(int.Parse);
var result = (
    from f in File
    join o in Organisation on f.OrganisationId equals o.Id
    where idList.Contains(o.Id)
    select f).Take(100).Count();

which in my environment (EF Core v1.1.1) produces the following SQL with no warnings (as expected):

SELECT COUNT(*)
FROM (
    SELECT TOP(@__p_1) [f].[Id], [f].[Name], [f].[OrganisationId]
    FROM [Files] AS [f]
    INNER JOIN [Organisations] AS [o] ON [f].[OrganisationId] = [o].[Id]
    WHERE [o].[Id] IN (1, 3, 4)
) AS [t]
4
5/4/2017 1:32:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow