Awesome! Yes, we've though about the first option you mentioned... but that's not going to work on our design, because the fields can be overridden on the client side. Looks like the second option is the way to go. And yes, we also need changes to related entities as you anticipated. We'll give it a try and see how it's going to work.

Many thanks!

Hi Leo,

Thank you for the sample. Do you have a flowchart or a document that illustrates the flow of events or delegates used by the EntityContextProvider? We need to understand how and where to put processes within these events as necessary. The sample you have given us illustrates calculations done on WebApi layer, not on the database itself. It does make sense to process calculations that way if calculations are handled on WebApi controller. However, we need to fetch calculated values after the changes have been saved to the database, not before. The reason being... there are calculations being processed ON AFTER UPDATE triggers that rely on incoming data from client side.

Okay, let's try to make things simpler. Let's forget about related entities for the time being. Take a look at the table below, with a trigger. This sample indicates that Gross and CommissionAmount are calculated via trigger. In this design, the mentioned fields can be overriden in case the actual cash is not the same with the calculated value. Be that as it may, whatever values generated for these fields should also be fetched by the EntityContextProvider on its return to the client.

USE [IntersoftSample]
GO

CREATE TABLE [dbo].[Collection](
    [CollectionId] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Amount] [money] NOT NULL,
    [Refund] [money] NULL,
    [Gross] [money] NULL,
    [CommissionRate] [smallint] NOT NULL,
    [CommissionAmount] [money] NULL
)
GO

CREATE TRIGGER [dbo].[PostCommission]
   ON [dbo].[Collection]
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF (NOT UPDATE(Gross) AND
        NOT UPDATE(CommissionAmount))
    BEGIN
        UPDATE
            [dbo].[Collection]
        SET
            Gross = INSERTED.Amount - ISNULL(INSERTED.Refund, 0),
            CommissionAmount = (INSERTED.CommissionRate / 100.00) * (INSERTED.Amount - ISNULL(INSERTED.Refund, 0))
        FROM
            [dbo].[Collection] CO INNER JOIN
            INSERTED ON CO.CollectionId = INSERTED.CollectionId
    END
END
GO

Based on the current behavior of EntityContextProvider, is it capable of fetching the calculated values for Gross and CommissionAmount in this scenario if an update is made from client side for Amount, Refund, and/or CommissionRate? Please advise.

Thanks!

Hi,

Any updates on this? Thanks!