Is there a way to perform a string interpolation in the join clause using LINQ?

I have to translate a query from a legacy database to LINQ but I'm having some problems with it.
Basically I need to do a left join and the property I want to compare is a composite of properties from the second table.

SELECT A.DATE, B.UNIT, B.PERSON
FROM TABLE1 A 
LEFT JOIN TABLE2 B ON A.KEY = CONCAT(B.UNIT, B.CONTRACT, B.VARIABLE)
WHERE B.DATE = someDate

So far I have tried to create this way, but without success:

var date = someDate;

from a in _context.Table1

join b0 in _context.Table2 on a.Key equals $"{b0.Unit}{b0.Contract}{b0.Variable}" into bGroup
from b in bGroup.DefaultIfEmpty()
where b.Date == date
                    
select new ResponseDto
{
    Date = a.Date,
    Unit = b.Unit,
    Person = b.Person
})

{
  "Message": "The LINQ expression '...' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.",
  "ErrorName": "InvalidOperationException"
}

When removing the string interpolation the query runs without any problem, so I think the problem is in the way I approached this problem. Is there a way to do this?

EF cannot translate the string.Format call (which is what the interpolated string gets converted into) into SQL because Format can do much more than just concatenate strings. It does support string.Concat, but only the overload that takes 2 parameters. I would just try string concatenation the old-fashioned way:

join b0 in _context.Table2 on a.Key equals (b0.Unit + b0.Contract + b0.Variable)

Approaches to try depending on your data:

• Concatenate the fields in the C# query like D. Stanly suggested
• Split out the fields in your C# query based on fixed width substrings assuming the concatenated field is fixed width
• If there is not much data in the tables and you are not running this query very often, bring in the raw data an do an in-memory C# query on the data. This is not recommended unless you know the data searched is very small and the number of queries done per day is small
• Create a SQL view for the query with the fields concatenated and query that instead. Not recommended if you need to do updates
• If the data is used read-only, add Entity Framework AsNoTracking() to the query
• If one of the data fields, Unit, Contract or Variable, are very selective or unique, then select on that field only in Entity Framework C# query and then filter the results in your C# code.
• Entity Framework pass-through SQL

The general idea is to reduce the subset of data brought back into C# memory to as small as possible and then do filtering in C#.

Note: If this is millions of rows in the tables, you'd need a different approach. Most likely a SQL approach.