Stored Procedures and QueryStrategy.DataSourceOnly

Currently we have data grid populated by view results returned from store procedures.

We want the grid to refresh with latest information from database.

I tried the following by apparent the entity manager is not re-executing the stored proc from db because subsequent calls shows no sql statements in sql profiler. (see the codes commented out).

 

          int intTemporalType = (int)type;
          IList<ViewTrainingsByGroup> retList = new List<ViewTrainingsByGroup>();
          // commented because QueryStrategy.DataSourceOnly has no effect
          //StoredProcQuery spQuery = em.GetTrainingsByGroupQuery(trainingGroupId, timePoint, intTemporalType, range.Start, range.End);
          //StoredProcQuery spQuery = new StoredProcQuery(typeof(DomainModel.ViewTrainingsByGroup));
          //spQuery.ProcedureName = "GetTrainingsByGroup";
          //spQuery.Parameters.Add(new QueryParameter("TrainingGroupId", trainingGroupId));
          //spQuery.Parameters.Add(new QueryParameter("TimePoint", timePoint));
          //spQuery.Parameters.Add(new QueryParameter("TemporalType", type));
          //spQuery.Parameters.Add(new QueryParameter("TimeRangeStart", range.Start));
          //spQuery.Parameters.Add(new QueryParameter("TimeRangeEnd", range.End));

          //if (refetchFromDatabase)
          //{
          //    QueryStrategy qs = spQuery.QueryStrategy;
          //    retList = spQuery.With(QueryStrategy.DataSourceOnly).Execute().Cast<ViewTrainingsByGroup>().ToList();
          //}
          //else
          //{
          //    retList = em.ExecuteQuery(spQuery).Cast<ViewTrainingsByGroup>().ToList();
          //}
          retList = em.GetTrainingsByGroup(trainingGroupId, timePoint, intTemporalType, range.Start, range.End).ToList();

 

Also, when entity is updated but not refreshed in grid, the following exception is thrown and I have no idea yet :(

 

Exception thrown during save:
Unable to update the EntitySet 'ViewTrainingsByGroup' because it has a DefiningQuery and no <UpdateFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

 

Any help is appreciated.

 

Thanks.

 

 

 

 

 

Indeed, the save changes exception is due to EF "limitation", I have to define an UpdateFunction.

Read this post: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/220262bd-85d4-4f29-96a8-4a7d1d2c8293

As quoted:

Unfortunately, when you generate a model including a view, the mapping to that view gets setup with a DefiningQuery which is a way to signal to the framework that standard DML operations to the view won't work (because in the general case views don't support DML).  The normal expectation is that you would write stored procedures for the create, update and delete operations and map them to the entityset so that the view can be used for reads and the stored procedures for writes.

 

After I defined the update stored proc, even with an no-op like the following stored proc, there is no more exception:

 

 

 

 

 

 

Best regards,

Sebastian

 

 

 

 

 

 

The MergeStrategy in effect was OverWriteChanges (see attached pic). I can see there is this thing called EntityReferenceStrategy, with LoadStrategy = Lazy. However, we are executing a stored proc that returns a view, not entity, so I do not know if and how this could have any effect.

 

uploads/370/IdeaBladeForumMyTopic3.zip 

 

Sebastian

 

As mentioned above in this post, the view comes from joins of 3 tables for instance, with some columns that are nullables, including the Title and Version columns. These columns therefore cannot be keys (see table mappings).

Title and Version columns were edited and saved. Note that there was no change in key values.

 

We seem to have already the right settings for fetch and merge strategy.

What's confusing is, is there anyway to just ask the EntityManager to just fetch directly from datasource?

 

Thanks Kim!