none
SQL Server View to Entity Framework - NO Primary Key RRS feed

  • Question

  • Hi all. I wonder if anyone has a work around for the following situation.

    I am creating a view in SQL Server but it requires a Group By so needs to NOT infer the primary key otherwise the grouping will not work.

    Due to not having a primary key I cannot add it to the entity framework.

    Its quite a complicated view and with work I could get around it but I would like to know of anyone has a way around getting a view in without primary key, or maybe making one up (maybe rownumber or such like) from the view?

    Many thanks in advance Scott

    Sunday, June 26, 2011 7:50 AM

Answers

All replies

  • Hi scottsanpedro;

    Please have a look at this article.

    Building an Entity Framework 4.0 model on views: practical tips

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by scottsanpedro Sunday, June 26, 2011 4:00 PM
    Sunday, June 26, 2011 2:50 PM
  • Thanks Mr Soto.

    Also along with this link, . http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view-question

    and also Row_Number the View was accepted  by EF.

    CREATE VIEW ViewExpectedActualRow WITH SCHEMABINDING
    AS
    WITH RES
    AS
    (
    SELECT   
    	Row_Number() OVER (ORDER BY MK_SKU) AS RowNo, 
    	dbo.tblMasterSKU.MK_SKU, 
    	LTRIM(RTRIM(dbo.tblMasterSKU.MK_Description)) AS MK_Description, 
    	LTRIM(RTRIM(dbo.tblMasterSKU.MK_Category)) AS MK_Category, 
    	LTRIM(RTRIM(dbo.tblMasterSKU.MK_Colour)) AS MK_Colour, 
    	dbo.tblMasterSKU.MK_Size, 
    	dbo.tblScannedImport.LocationID, 
    	dbo.tblStock.ST_SKU, 
    	SUM(dbo.tblScannedImport.Quantity) AS Quantity
    FROM     
    	dbo.tblMasterSKU 
    		INNER JOIN dbo.tblScannedImport ON dbo.tblMasterSKU.MK_ISBN = dbo.tblScannedImport.BarCode 
    		INNER JOIN dbo.tblStock ON dbo.tblMasterSKU.MK_SKU = dbo.tblStock.ST_SKU
    GROUP BY 
    	dbo.tblMasterSKU.MK_SKU, 
    	LTRIM(RTRIM(dbo.tblMasterSKU.MK_Description)), 
    	LTRIM(RTRIM(dbo.tblMasterSKU.MK_Category)), 
      LTRIM(RTRIM(dbo.tblMasterSKU.MK_Colour)), 
      dbo.tblMasterSKU.MK_Size, 
      dbo.tblScannedImport.LocationID, 
      dbo.tblStock.ST_SKU
    )
      SELECT   
    		ISNULL(A.RowNo,-999) as RowNo, 
    		A.MK_SKU, A.MK_Description, 
    		A.MK_Category, 
    		A.MK_Colour, 
    		A.MK_Size, 
    		A.LocationID, 
    		A.ST_SKU, 
    		A.Quantity, 
    		B.ST_Quantity
       FROM     
    		RES AS A 
    			INNER JOIN dbo.tblStock AS B ON A.MK_SKU = B.ST_SKU


    Sunday, June 26, 2011 4:02 PM
  • Glad to hear it worked out for you.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, June 26, 2011 4:12 PM