This project is read-only.

Using Views/Insteadof Triggers

Jul 9, 2012 at 11:17 AM

In  [discussion:360624] "Can you confirm that I can use Razor in in the generic NO views?" there is a tantalising reference "... This lets us map EF to either tables or - in more complex scenarios - to views /instead-of triggers."

I am using many such views but with stored procedure mapping in EF to provide the create/update/delete methods because EF does not seem to treat views/instead-of  like tables.

How can I use views/instead-of more directly using NO?


Jul 9, 2012 at 11:20 AM

I don't have any personal experience of this, but I know that Dan Haywood has definitely done it -  and I'm sure he'll pick up this post and respond.

Jul 9, 2012 at 11:47 AM


On 9 July 2012 11:17, lozdown <notifications@codeplex.com> wrote:

From: lozdown

I am using many such views but with stored procedure mapping in EF to provide the create/update/delete methods because EF does not seem to treat views/instead-of like tables.

How can I use views/instead-of more directly using NO?


Hmm, for me it just "seems to work". I build the edmx, generate the .edmx.sql, then clone it and replace the "create table" commands with "create view". I also add the instead-of triggers as and when needed (some views will be updateable automatically).

I have had to work around one case where there was an insert into a table with identity. In this case, the instead-of trigger needs to return a 1-row 1-column result set holding the value of the @@identity. (I figured this out using a SQL Profiler trace).

I've also been able to use views/instead-of triggers to implement a roll-up "table per hierarchy" pattern on a case-by-case basis. As you're probably aware, although the .edmx allows for different inheritance hierarchy strategies to be supported for different hierarchies, the EF designer in VS only allows for a single strategy to be specified. So what I'm doing is to let EF just map everything to its default, "table per type", and then replace the subtype tables with views that have an instead-of trigger that converts the INSERT into the subtype into an UPDATE into the "rolled-up" supertype table.

What sort of issues are you hitting? I'm keen to explore any limitations of this technique myself.

Dan



Jul 9, 2012 at 5:07 PM

Dan,

I shall have to pick this apart a bit and bear in mind I am not an EF expert. What you describe is very interesting and has some similarities to my endeavours. 

 I am using a database-first approach (I guess you  are model-first). The pre-existing database has complex views that have been made updateable using instead-of triggers. (In principle they could be refined into a semi-inheritance model but I haven't done that. Following that path would probably simplify what I have done. Thanks for the thought, I'll look at that but not just yet)

The database mapping  through EF  maps mostly to these updateable views. Unfortunately so far as I can tell the standard operation of EF Designer  makes views read-only, unless I also provide stored functions for the CUD operations on the view.

It is possible to manually edit the model.edmx file to make the updateable views look like  tables which are read-write (essentially the same principle as patching the edmx.sql file). I was reluctant to do that because I seem to have refix the model.edmx file each time I update the EF entity model (which is regularly as I am working incrementally with the ultimate users, both on the database functionality and the presentation. NO is superb support to this process). 

Apart from the additional complexity of defining and mapping stored procedures the DB  works well with NO, with one exception that I have not found the answer to. One 'instead-of-view' has an auto identity key. The create SP returns the newly created key to EF however the NO object -create form does not pick up this newly created key when it returns to the browser the persisted object data in the completed form so the key is '0'. If the user tries to Edit the returned object immediately, it will fail (incorrect PK reference '0'). However if the object is re-found through the menus (by search, browse etc) all is well, the object has been correctly persisted in the database, is known fully to the EF and is now known fully to NO.

I would very much like to find the answer to that problem. It seems related to your comment about returning the  @@identity which is effectively what I do through the stored procedure using the the standard MS mapping.

Laurie

Jul 9, 2012 at 6:11 PM
Edited Jul 10, 2012 at 1:15 PM

Interesting stuff.

It would seem my use of model-first is making life a little more easy for me than for you with the database-first approach. In particular, the .edmx that I end up with assumes that SQL Server has tables, so no hacking of it is required to write stored procs.
I do think it might be worth while you trying out model first to see if things are more convenient for you.
In terms of the identity question, you say: "The create SP returns the newly created key to EF". How do you do this? with a result set, or with an output param, or some other mechanism?
It might be worthwhile creating a simple spike without NO at all to see what EF does. I suspect that EF isn't actually getting back the identity value at all (though it is updated in the SQL Server DB).
Dan


Jul 9, 2012 at 10:41 PM

Thanks for the suggestions, I guess working model first effectively you are telling EF that the entities are mapped to tables even though they are actually implemented as views. (if it walks like a duck and quacks like a duck, it is a duck)

On the particular point of returning the identity key, I followed the MS technique to return the key from the stored procedure as a single row single column value . (see details in  http://msdn.microsoft.com/en-us/data/gg699321.aspx). I think you may well be right that EF is not receiving the identity key.  I need to check the exact details because I am puzzled by some specifics, especially your earlier comment about returning @@identity from a trigger. I understood triggers are not supposed to return results but can 'print', typically error/diagnostics messages, on the message stream which would normally be ignored. For example, receiving stored procedure results in .net normally requires SP out parameters to be mapped in the call to .net variables.  

I'll experiment later in the week and report back.

Laurie

 

Jul 9, 2012 at 11:10 PM
Edited Jul 10, 2012 at 1:15 PM



On 9 July 2012 22:41, lozdown <notifications@codeplex.com> wrote:

 

Thanks for the suggestions, I guess working model first effectively you are telling EF that the entities are mapped to tables even though they are actually implemented as views. (if it walks like a duck and quacks like a duck, it is a duck)

exactly!

On the particular point of returning the identity key, I followed the MS technique to return the key from the stored procedure as a single row single column value . (see details in http://msdn.microsoft.com/en-us/data/gg699321.aspx). I think you may well be right that EF is not receiving the identity key. I need to check the exact details because I am puzzled by some specifics, especially your earlier comment about returning @@identity from a trigger. I understood triggers are not supposed to return results but can 'print', typically error/diagnostics messages, on the message stream which would normally be ignored.

Hmm... well, they can. And they've been able to since before SQL Server was a Microsoft product (I used to work at Sybase!)

For example, receiving stored procedure results in .net normally requires SP out parameters to be mapped in the call to .net variables.

I'll experiment later in the week and report back.

I've just done a bit of archeology, and found the trigger I wrote:
create trigger trg_Communication_insert on uvw_Communication
instead of insert
as
SET NOCOUNT ON
insert into tbl_Communication (
CorrespondenceHolderObjectType,
CorrespondenceHolderId,
TemplateTranslationTemplateCode,
TemplateTranslationLanguageCode,
RtfDraft,
AsSent
)
output inserted.Id
-- the column name must match the domain object's property name exactly (http://thedatafarm.com/blog/data-access/entitykeys-are-case-sensitive/)
select
CorrespondenceHolderObjectType,
CorrespondenceHolderId,
TemplateTranslationTemplateCode,
TemplateTranslationLanguageCode,
RtfDraft,
AsSent
from inserted
go
where uvw_* is the view, and tbl_* is the table.
I'd forgotten about that second select there, I suspect that's also important. Note also the MSDN links in the comments within the trigger.
HTH
Dan
Jul 12, 2012 at 5:15 PM

I stand corrected and I have found the error thanks to your help. I was not capturing the returned value into EF because I had not mapped explicitly the return parameter holding the identity key.

In summary, using stored procedures: results can be returned using either 'OUTPUT ...'  or 'SELECT value as ReturnParam ...' .SP OUTPUT parameters cannot be used. The return parameter name must match case insensitively the EF parameter mapping.

Laurie

 

Jul 12, 2012 at 5:52 PM
great, glad you worked it out!