Entity framework materialized view not showing in wizard

Question

I am using PostgreSQL 12 with EF6. I am trying to add materialized view to my model but the view is not listed in the model wizard. I know to be able to add a view into a model, Entity Framework needs at least one column in database view to be not nullable. But I am not sure how to do it.

I have tried creating unique index and also rownumber with no success.

CREATE MATERIALIZED VIEW "Stock"
AS

SELECT coalesce(cast(ROW_NUMBER() OVER() as int), 0) as "Id",
    "PartMaster"."PartID",
    "OEMPart"."PartCatalogID",
    "PartMaster"."PartCode",
    "PartCatalog"."CustomPartCode",
    "OEMPart"."OEMCode",
    "PartMaster"."PartDesc",
    "GoodsReceivedDetail"."Cost",
    "GoodsReceivedDetail"."Markup",
    "GoodsReceivedDetail"."SellingPrice",
    coalesce("GoodsReceivedDetail"."Quantity", 0) as "InQuantity",
    coalesce("InvoiceDetail"."Quantity", 0) as "OutQuantity", 
    coalesce("GoodsReceivedDetail"."Quantity", 0) - coalesce("InvoiceDetail"."Quantity", 0) as "StockQuantity"
FROM
    "PartCatalog"
INNER JOIN
    "PartMaster"
ON
    (
        "PartCatalog"."PartID" = "PartMaster"."PartID")
INNER JOIN
    "OEMPart"
ON
    (
        "PartCatalog"."PartID" = "OEMPart"."PartID")
INNER JOIN
    "GoodsReceivedDetail"
ON
    (
        "OEMPart"."PartCatalogID" = "GoodsReceivedDetail"."PartCatalogID")
LEFT OUTER JOIN
    "InvoiceDetail"
ON
    (
        "GoodsReceivedDetail"."PartCatalogID" = "InvoiceDetail"."PartCatalogID")
INNER JOIN
    "PartCategory"
ON
    (
        "PartMaster"."PartCategoryID" = "PartCategory"."PartCategoryID") ;
        
CREATE UNIQUE INDEX "Id"
  ON "Stock" ("PartCatalogID");

Is your question how to make sure one of the columns in your Materialized View is not nullable? If so, check out https://stackoverflow.com/questions/47242219/how-to-indicate-that-a-column-is-not-nullable-when-creating-a-materialized-view/47245081#47245081 — Lars Skaug, Aug 02 '20 at 15:14

Lars Skaug · Accepted Answer · 2020-08-03T14:17:22.210

0

To make this object visible, simply add a view to your database.

CREATE VIEW stock_view
AS select * from Stock

Note: Materialized views are used to increase query performance. If you do not have performance issues, stick to regular views. Simply re-write your original materialized view as a regular view by removing the word materialized.

To answer your original question on how to make column Id in table Stock not nullable, you can combine the answers in this Stack Overflow post with this one as follows:

UPDATE pg_catalog.pg_attribute 
SET attnotnull = true
WHERE attrelid = Stock::regclass::oid
 and attname  = 'Id'

edited Aug 03 '20 at 14:17

answered Aug 02 '20 at 15:26

Lars Skaug

1,376
1
7
13

Thanks Lars for the suggestion. I have already tried the given solution(altering the table), but the wizard still doesn't shows views. – Prashant Aug 02 '20 at 18:12
I see. Are you sure you’re looking at the right schema? Your stock view is created without specifying one, so it’s going to be in a default one. – Lars Skaug Aug 02 '20 at 20:13
I updated my question with instructions on how to create a view on top of your structure, which begs the question why you need a materialized view. They should be used for performance. If you don't have query performance issues, simply use a view instead. – Lars Skaug Aug 03 '20 at 14:14
1

Thanks Lars. Normal view is visible in the wizard. But if you find a solution for materialized view, please update the question. – Prashant Aug 03 '20 at 18:04

Entity framework materialized view not showing in wizard

1 Answers1