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 (this extension 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 JOINs; 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.

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 (this extension 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 JOINs; 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.