If you weren't using EntityFrameworkCore, my suggestion would look something like this:

  1. Get the list of values from database 1
  2. Use `SqlBulkCopy` to insert a bunch of them into some table on database 2
  3. Join to that table in your "GetDeletablePersonProfiles" query.
  
Unfortunately, based on some cursory googling, it looks like EntityFrameworkCore [doesn't support bulk operations][1] ([this extension][2] does, however). I'll admit - I don't know a ton about EFC, but based on what you're doing I assume the following is possible:

  1. Get the list of values from database 1 (you're already doing this)
  2. In the code you execute, dump the values from the XML into a table (better performance than joining to the XML)
  3. Use that table in your query
  
Specifically, the main change I would suggest is taking this:

    AND [PersonIdf] IN (
        SELECT PersonIdf
        FROM (
            SELECT PersonIdf = XTbl.value('(PersonIdf)[1]', 'bigint')
            FROM @PersonIdfs.nodes('/root') AS XD(XTbl)
        ) AS XmlToData
    )

And turn it into this (you'll notice that I've also transformed both of your `EXISTS` into `JOIN`s; they're easier to read and accomplish the same task.

    SELECT PersonIdf = XTbl.value('(PersonIdf)[1]', 'bigint')
      INTO #MyFunTempTable
      FROM @PersonIdfs.nodes('/root') AS XD(XTbl);
      
    SELECT DISTINCT PersonProfile.[Id]
      FROM [authorization].[PersonProfile]
        INNER JOIN #MyFunTempTable PersonIdfList
          ON PersonProfile.PersonIdf = PersonIdfList.PersonIdf
        LEFT OUTER JOIN [authorization].[ProfilesToReplace]
          ON PersonProfile.ProfileId = ProfilesToReplace.ProfileId
      WHERE ProfilesToReplace.ProfileId IS NULL
      
It would also be good to make this a stored procedure, so that you don't have to worry about GetDeletablePersonProfiles.sql being replaced by a malicious actor.

You could also split it into two stages, using `ExecuteSqlCommand` to insert the values into a table (can't be a temp table anymore) and then you should really just need LINQ to get the values back.

You can also get an ADO.NET `DbConnection` object from the database context using `_dbContext.Database.GetDbConnection()`, and if you know that this is a SQL Server connection I've heard that you can cast that to a `SqlConnection` object, at which point you have access to the `SqlBulkCopy` method. This is questionable at best, but it might provide clean enough/performant enough code to be worthwhile. I'm definitely not endorsing this approach, but I'll put it out there.


  [1]: https://github.com/aspnet/EntityFrameworkCore/issues/7256
  [2]: https://github.com/borisdj/EFCore.BulkExtensions