1

I have 2 tables, REGISTRATION and STATUS_CODE

CREATE TABLE [dbo].[REGISTRATION](
   [REG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL PRIMARY KEY,
   [TYPE] [char](1),
   [CREDIT_STATUS] [varchar](4) NULL,
   [CREDIT_STATUS2] [varchar](4) NULL,
   [CREDIT_STATUS_TEMP]  AS (case when [TYPE] = '1' then [CREDIT_STATUS] when [TYPE] = '2' then [CREDIT_STATUS2] end)
 )

CREATE TABLE [dbo].[STATUS_CODE](
    [STATUS_CODE] [varchar](4) NOT NULL PRIMARY KEY,
    [STATUS_DESC] [varchar](10),
)

Which my ultimate goal is to run the following query using EF

SELECT REG.REG_ID, STS.STATUS_DESC FROM [dbo].[REGISTRATION] REG
INNER JOIN [dbo].[STATUS_CODE] STS
ON REG.CREDIT_STATUS_TEMP = STS.STATUS_CODE
WHERE STS.STATUS_DESC LIKE '%FAILED%'

In C#, I have the following annotation in REGISTRATION class for the computed column

...
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[ForeignKey("CreditStatusTemp")]
[StringLength(4)]
public string CREDIT_STATUS_TEMP { get; private set; }

public virtual STATUSC_CODE CreditStatusTemp { get; set; }
...

However, whenever I try to save the REGISTRATION record. I will hit this error.

{"A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'CREDIT_STATUS_TEMP'."}

I know I need the FK annotation to perform the query but I not sure how to do it correctly. Please help, Thanks.

Kah Hoe
  • 11
  • 2
  • Can't have FK on a computed column. You'll need to rethink your design. http://stackoverflow.com/questions/6384659/a-dependent-property-in-a-referentialconstraint-is-mapped-to-a-store-generated-c – Steve Greene Mar 29 '16 at 18:07
  • You don't need FK annotation in order to perform a query with join. But you have to do the join manually (w/o navigation property). – Ivan Stoev Mar 29 '16 at 18:17
  • Thank Mr Greene, I just want to perform a simple query with inner join(EF as an ORM) Is there any one to zero relational mapping instead of declare as FK? – Kah Hoe Mar 30 '16 at 00:24
  • Thanks Mr @Stoev, You mean I can still query and join manually without declaring the relationship in EF? How? – Kah Hoe Mar 30 '16 at 00:28

0 Answers0