ActiveRecord / NHibernate: Update error on Query

Question

I have a search feature on my website which sometimes fails. It is build in C# / ASP.NET / WebForms and uses ActiveRecord.

Basically it seams as when I get too many records returned from my VIEW in my database I get en UPDATE error.

string query = "s";
Order[] orders = new order[] { Order.Asc("LastName") };
List<ICriterion> crit = new List<ICriterion>();
Crit.Add(Expression.Sql(String.Format("LastName" LIKE '%{0}%' OR FirstName LIKE '%{0}%'", query)));
var rv = ActiveRecordBase.FindAll(typeof(vMyView), orders, crit.ToArray())

The above part of my code failes on the FindAll command, when using 's' as the query to search for (where I will get many results). But using 'x' as query it works like a charm.

The error I get is:

View or function 'vAllowedVessels' is not updatable because the modification affects multiple base tables. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: View or function 'vAllowedVessels' is not updatable because the modification affects multiple base tables.

Source Error: 

Line 74:             List<ICriterion> crit = new List<ICriterion>();
Line 75:             Crit.Add(Expression.Sql(String.Format("LastName" LIKE '%{0}%' OR FirstName LIKE '%{0}%'", query)));
Line 75:             var rv = ActiveRecordBase.FindAll(typeof(vMyView), orders, crit.ToArray()) <--- This is highlighted in red!

Stack Trace:
[GenericADOException: could not update: [Ovf.Model.vAllowedVessels#3843][SQL: UPDATE vAllowedVessels SET FixtureId = ?, ContractDate = ?, No = ?, ContractForm = ?, VesselName = ?, Status = ?, CreatedDate = ?, Buyer = ?, BuyerName = ?, BuyerShortName = ?, BuyPrincipal = ?, BuyPrincipalName = ?, Seller = ?, SellerName = ?, SelPrincipal = ?, SelPrincipalName = ?, Operator = ?, Broker = ?, UsrCode = ? WHERE VesselFolderId = ?]]
NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) +2474
NHibernate.Persister.Entity.AbstractEntityPersister.UpdateOrInsert(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) +335
NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, Object rowId, ISessionImplementor session) +1898
NHibernate.Action.EntityUpdateAction.Execute() +764
NHibernate.Engine.ActionQueue.Execute(IExecutable executable) +48
NHibernate.Engine.ActionQueue.ExecuteActions(IList list) +128
NHibernate.Engine.ActionQueue.ExecuteActions() +50
NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session) +215
NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event) +225
NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet`1 querySpaces) +288
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +524
NHibernate.Impl.CriteriaImpl.List(IList results) +76
NHibernate.Impl.CriteriaImpl.List() +71
Castle.ActiveRecord.ActiveRecordBase.FindAll(Type targetType, Order[] orders, ICriterion[] criteria) in c:\TeamCity\buildAgent\work\e41ee5ead2eba140\src\Castle.ActiveRecord\Framework\ActiveRecordBase.cs:1034

[ActiveRecordException: Could not perform FindAll for vAllowedVessels]
Castle.ActiveRecord.ActiveRecordBase.FindAll(Type targetType, Order[] orders, ICriterion[] criteria) in c:\TeamCity\buildAgent\work\e41ee5ead2eba140\src\Castle.ActiveRecord\Framework\ActiveRecordBase.cs:1046
Ovf.Model.vAllowedVessels.SearchByType(String query, String typename, String usrCode) in g:\Dev\2012\OnTrack\model\Profile\vAllowedVessels.cs:76
Search_Default.BindVesselFolders() in g:\Dev\2012\OnTrack\webapp\Search\Default.aspx.cs:155
Search_Default.ChangeStatus(Object sender, EventArgs e) in g:\Dev\2012\OnTrack\webapp\Search\Default.aspx.cs:145
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +116
System.Web.UI.WebControls.DropDownList.RaisePostDataChangedEvent() +133
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +13
System.Web.UI.Page.RaiseChangedEvents() +132
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1644

It should be noted that I do NOT want to update anything - just query my VIEW.

What do you mean by "too many records"? What makes you think that is the problem? — Daniel Schilling
– Daniel Schilling, Commented Aug 26, 2013 at 18:13
I thought it was because of too many records (900+) as this was a pattern I saw. I have now been told that if "certain fields" in the request are NULL values this will happen. I dont know yet what these fields are, but that is the next thing to focus on. — olf
– olf, Commented Aug 27, 2013 at 7:09
Hmm... did not find a solution. "Solution" so far is to put it in a try-catch and return an error message on this fail! — olf
– olf, Commented Aug 30, 2013 at 13:51

Daniel Schilling · Accepted Answer · 2013-08-30 16:27:21Z

Apparently some of your data looks like it's dirty to NH and needs to be saved even though you haven't made any changes. This could possibly be from property getters or custom user types that are transforming the values, such that they no longer mach the values that came out of the database. For example - code like this could be problematic:

private string _lastName;
public virtual string LastName
{
    get { return _lastName.Trim(); }
    set { _lastName = value; }
}

Even though you never touch the setter, the value of the property still looks like it changed. See if you can remove these types of transformations - deal with them another way.

Here's another, probably easier, way to deal with this: since you cannot save changes through this SQL view, you should add mutable="false" to the class mapping. See the documentation:

Changes to immutable classes, mutable="false", will not be persisted.

In Fluent NHibernate, you use the ReadOnly() method to control this mutable flag.

Collectives™ on Stack Overflow

ActiveRecord / NHibernate: Update error on Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related