Querying a view immediately after writing to underlying tables in SQL Server 2014

I finally got to the bottom of this: the DB writes are done in their own threads, with the main thread waiting for all the write threads to complete before checking the results. However, there was a bug in the code which checked whether all the threads were complete, causing the main thread to do the check too early.

Is it possible that your Link to SQL to check the effect of the write is looking at old cached data? Try refreshing the cache beforehand with the refresh method of the context object. Use RefreshMode.OverwriteCurrentValues on the object.

Could you try with table hints i.e.

CREATE VIEW [Position].[Transactions]WITH SCHEMABINDINGAS(  SELECT    Account,    Book,    TimeAPIClient AS DateTimeUtc,    BaseCcy AS Currency,    ISNULL(QuantityBase, 0) AS Quantity,    ValueDate AS SettleDate,    ISNULL(CAST(0 AS tinyint), 0) AS TransactionType  FROM Trades.FxSpotMF WITH(NOLOCK)  WHERE IsCancelled = 0  UNION ALL  SELECT    Account,    Book,    TimeAPIClient AS DateTimeUtc,    QuoteCcy AS Currency,    ISNULL(-QuantityBase * Rate, 0) AS Quantity,    ValueDate AS SettleDate,    ISNULL(CAST(0 AS tinyint), 0) AS TransactionType  FROM Trades.FxSpotMF WITH(NOLOCK)  WHERE IsCancelled = 0  ...)

Alos check this blog entry, in my case use the nolock hint solve the issue.