CODEWITHSUNDEEP
sql-serverasp.net-mvc-4
Jen143Me
Jen143Me

Reputation: 273

Does not have a primary key defined and no valid primary key could be inferred

I have this query in making my view but the problem is this view is not included in my Model. So, I could not use this view.

Here is my code:

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;

This view can be seen in the database after I create this. But not when I add a model. This is not included in my Entity.

Here is the error will displayed:

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.

Please help me with this matter. Thanks in advance.

Upvotes: 5

Views: 5245

Answers (2)

Mackan
Mackan

Reputation: 6271

I think it might be because EF has a hard time choosing the correct primary key. A way to force EF to choose a key of your liking seems to be to use ISNULL() for the primary key, and NULLIF() for the other columns (at least the ones that could cause confusion, like primary keys from other tables or non-nullable columns).

In your case this would mean:

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;

You probably don't need all these NULLIF()'s, but since I'm not sure what column is causing issues it's better to take all. If it works with this code, you can start removing the NULLIF from student names and trans_name.

Upvotes: 6

Laxman Parmar
Laxman Parmar

Reputation: 120

You need to define first id column primary key mvc does not allow this when you are using it with model

Upvotes: 0

Related Questions