title: Custom query in getter to return specific value when missing part of composite key
body: <p>I have a table (<em>Interaction_Data</em>) with a composite key consisting of 5 data fields. Four of these keys are provided as a foreign key by another class (<em>Interactions</em>). This gives me the problem of a missing key in my <em>Interaction_Data</em> table, so my desired data field always returns the first result since it is getting back multiple results.</p>
  
  <p>In my <em>InteractionData</em> model, I am attempting to call a SQL query in my getter (See "<em>DataValueNetCost</em>" below). I want my getter to return a specific value based on input from another field in my table. Namely, I want to get:</p>
  
  <blockquote>
    <p>DATA_VALUE when DATA_DEF_ID = "NET_COST"</p>
  </blockquote>
  
  <p>Interactions model:</p>
  
  <pre><code>[Table("INTERACTIONS")]
      public partial class Interaction {
          // NOTE: The primary key for the INTERACTIONS table is a composite key
          //       consisting of:
          //           - ACCOUNT_ID
          //           - CONTACT_DATE
          //           - CONTACT_SEQ
          //           - PAGE
  
          [Key]
          [Column("ACCOUNT_ID", Order = 0)]
          public int ACCOUNT_ID { get; set; }
  
          [Key]
          [Column("CONTACT_DATE", Order = 1)]
          public DateTime CONTACT_DATE { get; set; }
  
          [Key]
          [Column("CONTACT_SEQ", Order = 2)]
          public int CONTACT_SEQ { get; set; }
  
          [Key]
          [Column("PAGE", Order = 3)]
          public int PAGE { get; set; }
  
          [ForeignKey("ACCOUNT_ID,CONTACT_DATE,CONTACT_SEQ,PAGE")]
          public InteractionData InteractionData { get; set; }
      }
  </code></pre>
  
  <p>InteractionData model:</p>
  
  <pre><code>[Table("INTERACTION_DATA")]
      public partial class InteractionData {
          // NOTE: The primary key for the INTERACTION_DATA table is a composite key
          //       consisting of:
          //           - ACCOUNT_ID
          //           - CONTACT_DATE
          //           - CONTACT_SEQ
          //           - PAGE
          //           - DATA_DEF_ID
  
          [Key]
          [Column("ACCOUNT_ID", Order = 0)]
          public int ACCOUNT_ID { get; set; }
  
          [Key]
          [Column("CONTACT_DATE", Order = 1)]
          public DateTime CONTACT_DATE { get; set; }
  
          [Key]
          [Column("CONTACT_SEQ", Order = 2)]
          public int CONTACT_SEQ { get; set; }
  
          [Key]
          [Column("PAGE", Order = 3)]
          public int PAGE { get; set; }
  
          // IN THE DB TABLE THIS IS ALSO PART OF THE COMPOSITE KEY
          // BUT DATA_DEF_ID DOES NOT EXIST IN THE INTERACTIONS TABLE
          // SO I CAN'T NAME IT A PART OF THE COMPOSITE KEY
          [Column("DATA_DEF_ID")]
          [StringLength(60)]
          public string DATA_DEF_ID { get; set; }
  
          [Column("DATA_VALUE")]
          [StringLength(400)]
          public string DATA_VALUE { get; set; }
  
          //// Since we are using custom getters and setters we need a backing field
          private string _dataValueNetCost;
  
          [StringLength(400)]
          public string DataValueNetCost {
              get {
                  // We need to get the specific DATA_VALUE when DATA_DEF_ID is "NET_COST"
                  // This next section will call a SQL query to get this field and return it.
                  SourceCodeContext db = new SourceCodeContext();
  
                  IEnumerable&lt;InteractionData&gt; netCost = db.InteractionsData.SqlQuery(
                      "SELECT DATA_VALUE FROM PBDS.INTERACTION_DATA as id WHERE id.Account_Id = '@p0' AND id.CONTACT_DATE = '@p1' AND " +
                      "id.CONTACT_SEQ = '@p2' AND id.PAGE = '@p3' AND id.DATA_DEF_ID = '@p4'",
                      ACCOUNT_ID, CONTACT_DATE, CONTACT_SEQ, PAGE, "NET_COST"
                  );
  
                  string returnString = netCost.ToString();
  
                  return returnString;
              }
              set {
                  _dataValueNetCost = value;
              }
          }
      }
  </code></pre>
  
  <p>Currently, the result of this is <em>DataValueNetCost</em> has the value of the first DATA_VALUE record returned from the query with only 4 of the 5 keys required for the composite key.</p>
  
  <p>I am getting data based on an Odata call that sends the 4 primary keys to Interactions. I need to display <em>InteractionData.DataValueNetCost</em> on screen in my View.</p>
  
  <p>How can I return DATA_VALUE when DATA_DEF_ID = "NET_COST" to a text field in my View?</p>