2

Because a 

SELECT * FROM visits where id not in (1,2,3,4,5,6,...)

with 100's of IDs is really slow, I'd like to use a declared table and do a 'where not exists' on it, like:

DECLARE @list Table(ID int);
INSERT INTO @list VALUES (192000),(934282),(991116),(235091),(89709),(282605),(339057),(368062),(512013),(536548),(94714),(197477),(654839),(848893),(15917),(476700),(909048),(536400),(823404),(777915),(228055),(492591),(439780),(493784),(732689),(328185),(620171),(183835),(373595),(536869), ...;
INSERT INTO @list VALUES (3),(5);
SELECT TOP (1000) [Id]
      ,[IsDeleted]
      ,[Url]
      ,[Username]
      ,[VisitDate]
  FROM [dbo].[Site].[Visits] v
  where not exists (select id from @list l where l.ID =  v.Id)

However, I'd like to use LINQ-to-Entities of Entity Framework Core 2.0 to add other .Where() clauses and the .OrderBy, .Take(), ... functionalities on the Visits.

Can this query be translated to LINQ-To-Entities or atleast to an IQueryable where I can use extension functions on afterwards? I'm using ASP.NET Core 2.0.5 and the database is an MSSQL Server 2016.

Appsum Solutions
  • 999
  • 2
  • 10
  • 30
  • Did you actually try the `where not exists` method and found a noticeable difference in terms of performance? – Rafalon Feb 20 '18 at 14:20
  • From where you are receiving the input id values? I guess it should have came from another table. You can better do a left join and use is null to get what are the data exists in visits. – Sivaprasath Feb 20 '18 at 14:20
  • Not easily. The problem is that you'll need a separate (dedicated) table-valued parameter type just to pass this table, and there is no obvious way to specify that should be used in the query translation. It's certainly possible in theory (LINQ providers can do anything they like with expression trees, after all), and I was sure some question on SO addressed it, but a cursory search doesn't find anything. EF can't do this in general because quite literally any type could be used in `IN` (even if it's usually integers) and creating TVPs on the fly... wouldn't fly. – Jeroen Mostert Feb 20 '18 at 14:26
  • Having said all that, if this is an operation you're willing to write a custom stored procedure for, you can use EF to invoke that and still leave it to do the entity mapping. See, e.g., [this question](https://stackoverflow.com/q/8157345/4137916), though I'm not sure which of any of the answers are applicable to EF Core. – Jeroen Mostert Feb 20 '18 at 14:28
  • @Rafalon yes, the 'where not exists in' with a table variable with over a 1000 id's in it takes some milliseconds, while the not in takes 30 seconds with 100 id's. – Appsum Solutions Feb 20 '18 at 14:42
  • @Sivaprasath I get it in multiple loops in code, where I get the current user's roles, permissions and fill an array with id's of entities the user is either allowed or not allowed to get. Then I'm gonna return a paged result set from the API with on each page only the allowed entities – Appsum Solutions Feb 20 '18 at 14:44
  • You can't translate it to LINQ to Entities. But as soon as you select all expected columns, you can use `context.Visits.FromSql(your_sql)` which in EFC returns `IQueryable` that can be combined with other LINQ operators. – Ivan Stoev Feb 20 '18 at 15:21
  • Maybe you should take a look at [row-level security](https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security). – Gert Arnold Feb 20 '18 at 15:34

0 Answers0