locked
Not possibleto add SQL Views to Entity Data Model? RRS feed

  • Question

  • Hello and sorry for the "double post" in the Silverlight 3 Beta section.

    but when i try to add SQL Views to my Entity Data Model nothing happens. Tables and Procedures are added bot none of my Views are.

    So, are they not supported?

    Thanks!

    Wednesday, March 25, 2009 4:30 AM

Answers

  • Yes, you need a column which uniquely identify each row in database view. So, you need to modify you view definition slightly.

    For example, let say you have following tables in your DB:

    Product

    SalesOrder

    SalesOrderLine

    Customer

    And, in your view you want to show folowing columns,

    Product.Description,SalesOrder.SalesOrderNo, SalesOrder.Date, SalesOrderLine.Quantity, SalesOrderLine.LineTotal, Customer.CustomerName

    The databse view above can't be added to EDMX since it has no unique key identify each row. So, in this case, since SalesOrderLine is the base table in the view, I will add SalesOrderLine.Id (the primary key of SalesOrderLine table) to my view definition since it will uniquely identify each record.

     Now, I can add the view to my EDMX.

     

    Wednesday, March 25, 2009 6:10 AM
  • Thank you very much, you guided me in the right direction!

    My View already included the Primary Key, but in a JOIN Condition i marked "include all rows from SubTableXYZ" and so the View returned Rows where the Primary Key was null. That was the Problem.

    Thanks again!

    Wednesday, March 25, 2009 6:49 AM

All replies

  • SQL Server data baseview is supported in Entity Framework.

     

    When you add new EDMX to your project, you can tick on the database views you want to generate the entity model from the wizard.

    Wednesday, March 25, 2009 5:09 AM
  • that didn't work with my existing EDMX.

    i created a new project and added a new EDMX,  then i got the following error when adding a view:

    the table/view xyz 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 your schema, add the correct keys and uncomment it.

    Key in a View ?

    Can someone explain to me how i can use a View in EDMX or what i should change in the View?

    Wednesday, March 25, 2009 5:23 AM
  • Yes, you need a column which uniquely identify each row in database view. So, you need to modify you view definition slightly.

    For example, let say you have following tables in your DB:

    Product

    SalesOrder

    SalesOrderLine

    Customer

    And, in your view you want to show folowing columns,

    Product.Description,SalesOrder.SalesOrderNo, SalesOrder.Date, SalesOrderLine.Quantity, SalesOrderLine.LineTotal, Customer.CustomerName

    The databse view above can't be added to EDMX since it has no unique key identify each row. So, in this case, since SalesOrderLine is the base table in the view, I will add SalesOrderLine.Id (the primary key of SalesOrderLine table) to my view definition since it will uniquely identify each record.

     Now, I can add the view to my EDMX.

     

    Wednesday, March 25, 2009 6:10 AM
  • Thank you very much, you guided me in the right direction!

    My View already included the Primary Key, but in a JOIN Condition i marked "include all rows from SubTableXYZ" and so the View returned Rows where the Primary Key was null. That was the Problem.

    Thanks again!

    Wednesday, March 25, 2009 6:49 AM