title: EF Core one-to-one relationship with string property as key
body: <p>I am using entity framework core 5 query syntax. There are 3 classes which reflect the tables in database.</p>
  <pre><code>public class Record { public int RecordID {get; set;} public decimal Amount {get; set} public string AccountName {get; set;} }
  </code></pre>
  <pre><code>public class Dimension { public int DimensionID {get; set;} public string DimensionSymbol {get; set;} } 
  public class DimensionName { public int ID {get; set;} public string Name {get; set;} }
  </code></pre>
  <pre><code>public class RecordDto { public int ID {get; set;} public string DimensionName {get; set;} }
  </code></pre>
  <p>The aim is to find DimensionName foreach Record. The relationship is as follows. RecordID field is a foreign key for Dimension. So it is easy to find this relationship. The problem is with DimensionName. DimensionID is NOT a foreign key to DimensionName. The logical key is DimensionSymbol which sometimes is a simple text like &quot;200.000B&quot; and sometimes it represents the number like 190. If DimensionSymbol is 190 then this 190 reflects the ID in DimensionName.</p>
  <p>My query is as follows.</p>
  <pre><code>var query = from r in context.Records
  join d in context.Dimensions on r.RecordID equals d.DimensionID
  join n in context.DimensionNames on int.Parse(d.DimensionSymbol)\&gt;0 ?                    int.Parse(d.DimensionSymbol) : 0  equals n.ID
  
  select new RecordDto
  {
  ID = r.RecordID,
  DimensionName = n.Name
  }
  </code></pre>
  <p>And it doesn't work. Could you please adjust the code so that it works? Maybe I should configure sth in OnConfiguring method.</p>