0

So I have been using Entity Framework since version 4 and recently had some breaking changes in a project I was doing. Suffice to say I am using EF to get the differential from one database to another in an ETL process with EF core 3.1.2 which was EF Core 2.2.6. The part that is now breaking has code similar to this.

var setOne = await 1stContext.table.Where(x => x.DateModified >= inputDate)).Select(x => new { x.UserId, x.RoleId, x.UniversityId }).ToListAsync();

var toRemove = 2ndContext.table.Where(x => setOne.Contains(new { x.UserId, x.RoleId, x.UniversityId })).ToList();

Having an anonymous projection 'new { (items) }' used to work but now it blows up. I tried casting to a named tuple that was new since C# 7 but you cannot do that in an expression tree according to the compiler. So any ideas on a new ways? Maybe just do a linq join or similar? My idea was always to do the where clause first before realizing the data so the equivalent sql would not give me all the results and then go over them.

UPDATE Here is a simple print screen without ef of what I mean. I did this all the time with very wide sets I wanted to only do a smaller set of and then find it. I could do a join but the key is if you look at my second statement I am not projecting to a real object with a 'toList' yet and if I did I would lose the ability to get the objects I wanted to remove potentially. I could possibly do a join but I just don't want to make expensive database calls to get everything and THEN filter. But a join may work. I will probably play with this tomorrow at work but just wanted this up here if anyone knew.

enter image description here

djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • 1
    I stand corrected :) – TheGeneral Mar 05 '20 at 05:37
  • `Contains` on non primitive type memory collection never worked in EF / EF Core. In EF Core 1.x / 2.x it *might* look working, but in fact that was due to client evaluation, which basically performs the filtering in memory after retrieving all the data from the database. Which is the same as adding `.AsEnumerable()` before `Where`. – Ivan Stoev Mar 05 '20 at 09:41
  • @IvanStoev Ahh, I never knew that. So a join would be better potentially then for not doing the projection first and keeping at the SQL later the entire time? – djangojazz Mar 05 '20 at 19:38
  • Well, unfortunately queries involving more than one context / database are not supported. – Ivan Stoev Mar 05 '20 at 19:41
  • See this SO post for possible solutions - https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key/26201371#26201371 (it's for EF6, but the same pretty much applies to EFC). Also you could take a look at this 3rd party package [EntityFrameworkCore.MemoryJoin](https://www.nuget.org/packages/EntityFrameworkCore.MemoryJoin) - I never tried it, and have no idea if it supports EFC 3.x – Ivan Stoev Mar 05 '20 at 19:49

1 Answers1

0

Based on what Ivan Stoev said I just placed the 'AsEnumerable' after the entity declaration and before the where. It seems that EF Core 3.1 is much more strict than previous iterations.

So this:

2ndContext.table.Where

became this:

2ndContext.table.AsEnumerable().Where

Just basically explicitly stating that from what I gather from others it was implying on my client previously.

djangojazz
  • 14,131
  • 10
  • 56
  • 94