sql-server - 没有定义主键并且无法推断出有效的主键

Question

我在制作 View 时有此查询，但问题是此 View 未包含在我的模型中。所以，我不能使用这个 View 。

这是我的代码:

CREATE VIEW [dbo].[Payment_Transaction_vw]
AS 
    SELECT 
        id = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        Transaction_Info.trans_name, Student_Info.ID_Number,
        Student_Info.student_fname, Student_Info.student_lname,
        SUM(Payment_Transaction.amount) AS amount
    FROM 
        [Payment_Transaction]
    INNER JOIN 
        Student_Info ON Payment_Transaction.student_info_id = Student_Info.student_info_id 
    INNER JOIN 
        Transaction_Info ON Payment_Transaction.trans_info_id = Transaction_Info.trans_info_id 
    GROUP BY 
        ID_Number, student_lname, student_fname, trans_name;

创建此 View 后，可以在数据库中看到此 View 。但当我添加模型时不会。这不包括在我的实体中。

这是将显示的错误:

dbo.Payment_Transaction_vw does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review our schema, add the correct keys, and uncomment it.

请帮我解决这个问题。提前致谢。

Answer 1

我认为这可能是因为 EF 很难选择正确的主键。强制 EF 选择您喜欢的键的一种方法似乎是使用 ISNULL()对于主键，和 NULLIF()对于其他列(至少是那些可能导致混淆的列，例如来自其他表的主键或不可为空的列)。

在您的情况下，这意味着:

SELECT 
    ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), -1) AS Id,
    NULLIF(Transaction_Info.trans_name,'') AS trans_name, 
    NULLIF(Student_Info.ID_Number,-1) AS ID_Number,
    NULLIF(Student_Info.student_fname,'') AS student_fname, 
    NULLIF(Student_Info.student_lname,'') AS student_lname,
    NULLIF(SUM(Payment_Transaction.amount),-1) AS amount
FROM [Payment_Transaction]
INNER JOIN Student_Info ON 
    Payment_Transaction.student_info_id = Student_Info.student_info_id 
INNER JOIN Transaction_Info ON 
    Payment_Transaction.trans_info_id = Transaction_Info.trans_info_id 
GROUP BY ID_Number, student_lname, student_fname, trans_name;

您可能不需要所有这些 NULLIF() 's，但由于我不确定是哪个列导致了问题，所以最好全部采用。如果它适用于此代码，您可以开始从学生姓名和 trans_name 中删除 NULLIF。