LINQ Where 1=0

  • Thread starter Thread starter Chuck P
  • Start date Start date
C

Chuck P

I have a asp.net gridview with a LinqDatasource.
When I do a delete the SQL profiler shows

DELETE FROM [dbo].[UsersInRoles] WHERE 0 = 1
The exception is:
[System.Data.Linq.ChangeConflictException] = {"Row not found or changed."}

<asp:LinqDataSource ID="LinqDataSource1" runat="server"
onselecting="LinqDataSource1_Selecting"
ContextTypeName="ApplicationManagment.ApplicationsDataContext"
TableName="UsersInRoles" StoreOriginalValuesInViewState="False"
EnableDelete="True" EnableInsert="True" EnableUpdate="True">
</asp:LinqDataSource>


<cwp:GridView_Ex ID="gvUserInRoles" runat="server"
AutoGenerateColumns="False"
DataKeyNames="UsersInRolesID" DataSourceID="LinqDataSource1"
CanDeleteRows="True" CanEditRows="True"
In the datasource_selecting event I have:

ApplicationsDataContext ctx = new
ApplicationsDataContext(ConfigurationManager.ConnectionStrings["cnnApplicationManagement"].ConnectionString);

int[] iqueryAuthorizedApplications =
queryAuthorizedApplications.ToArray();

var queryRoles =
from
u in ctx.UsersInRoles

where
//get authorized and Filtered applications
iqueryAuthorizedApplications.Contains(u.ApplicationID)
&& //filter active
(chkActiveOnly.Checked == false ||
u.ApplicationRole.Application.DateRetired == null)
&& //filter application ddl
(ddlApplications.SelectedValue == "-1" ||
u.ApplicationID.ToString() == ddlApplications.SelectedValue)
&&
u.ApplicationRole.lutRole.IsPublicRole == true
&& // filter roles
(ddlRoles.SelectedValue == "-1" || u.RoleID.ToString() ==
ddlRoles.SelectedValue)
&& //filter Zno
(txtUser.Text == string.Empty || u.UserZno == txtUser.Text)

select u;
e.Result = queryRoles;

I saw in Scott G. Blog you can do this in the event and still have deleting.

How should I debug this?

In the exception handler I don't have access to the DataContext. Is their a
way to get the DataContext from GridViewDeletedEventArgs or the DataSource?
That way I could enumerate the context.ChangeConflicts.
 
Hello Chunk

The exception: System.Data.Linq.ChangeConflictException: Row not found or
changed could occur because the underlying row in the database was updated
between the original value being retrieved by the GridView and the actual
update being applied. To get a reference to the data context and resolve
the conflict, one solution is to register the ContextCreate event of the
LinqDataSource.

protected void LinqDataSource1_ContextCreated(object sender,
LinqDataSourceStatusEventArgs e)
{
if (e.Result != null)
{
dc = (NorthStudentDataContext)e.Result;
}
}

where dc is a member of the page class:
NorthStudentDataContext dc;

In the meantime, we register the Deleted event of LinqDataSource to handle
the ChangeConflictException.

protected void LinqDataSource1_Deleted(object sender,
LinqDataSourceStatusEventArgs e)
{
if (e.Exception is ChangeConflictException && dc != null)
{
//dc.ChangeConflicts contains the list of all conflicts
foreach (ObjectChangeConflict prob in dc.ChangeConflicts)
{
//there are many ways in resolving conflicts,
//see the RefreshMode enumeration for details
prob.Resolve(RefreshMode.KeepChanges);
}
e.ExceptionHandled = true;
}
}

In the process of page load / post back, ContextCreated is trigger prior to
Deleted, thus dc is set as long as the DataContext itself is created
successfully.

Please let me know if you have any other concerns, or need anything else.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
The row wasn't changed. I heard you get 1=0 stuff when linq gets confused.
For example your dbml diagram relationships are not identical to the db FKs.
So I am not sure on how to proceed on figuring out why LINQ created the
delete statement with 1=0

Is their a
way to get the DataContext from GridViewDeletedEventArgs or the Grid's
DataSource? My exception handler doesn't have access to the page load event.



"Jialiang Ge [MSFT]" said:
Hello Chunk

The exception: System.Data.Linq.ChangeConflictException: Row not found or
changed could occur because the underlying row in the database was updated
between the original value being retrieved by the GridView and the actual
update being applied. To get a reference to the data context and resolve
the conflict, one solution is to register the ContextCreate event of the
LinqDataSource.

protected void LinqDataSource1_ContextCreated(object sender,
LinqDataSourceStatusEventArgs e)
{
if (e.Result != null)
{
dc = (NorthStudentDataContext)e.Result;
}
}

where dc is a member of the page class:
NorthStudentDataContext dc;

In the meantime, we register the Deleted event of LinqDataSource to handle
the ChangeConflictException.

protected void LinqDataSource1_Deleted(object sender,
LinqDataSourceStatusEventArgs e)
{
if (e.Exception is ChangeConflictException && dc != null)
{
//dc.ChangeConflicts contains the list of all conflicts
foreach (ObjectChangeConflict prob in dc.ChangeConflicts)
{
//there are many ways in resolving conflicts,
//see the RefreshMode enumeration for details
prob.Resolve(RefreshMode.KeepChanges);
}
e.ExceptionHandled = true;
}
}

In the process of page load / post back, ContextCreated is trigger prior to
Deleted, thus dc is set as long as the DataContext itself is created
successfully.

Please let me know if you have any other concerns, or need anything else.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
 
Here is the enumeration of the context.ChangeConflicts

Row not found or changed.

Table name: dbo.UsersInRoles
-- Conflicts Start Here --
Member: ApplicationID
Current value: 0
Original value: 0
Database value: 12
Member: RoleID
Current value: 0
Original value: 0
Database value: 5
Member: UserJobCode
Current value:
Original value:
Database value: na
Member: UserOrgCode
Current value:
Original value:
Database value: na
Member: AuthorizedByZno
Current value:
Original value:
Database value: 148037
Member: AuthorizationDate
Current value: 1/1/0001 12:00:00 AM
Original value: 1/1/0001 12:00:00 AM
Database value: 8/10/2007 12:00:00 AM
Member: RoleStatusSetBy
Current value:
Original value:
Database value: 148037
Member: RoleStatusDate
Current value: 1/1/0001 12:00:00 AM
Original value: 1/1/0001 12:00:00 AM
Database value: 8/10/2007 12:00:00 AM
 
I got the delete to work by changing the LinqDataSource to storeviewstate
values.
The documentation says I don't have to:
http://msdn.microsoft.com/en-us/library/bb547113.aspx
The docs aren't to clear though:

If you programmatically create the context object in the Selecting event and
you do not have to store original values in the view state, the
ContextCreating and ContextCreated events are skipped.

I do create a context object, however, I guess it gets assigned during
e.Result=query?
If I turn viewstate off my sql statement becomes:

DELETE FROM [dbo].[UsersInRoles] WHERE ([UsersInRolesID] = @p0) AND
([RowVersion] = @p1)',N'@p0 int,@p1 timestamp',@p0=75,@p1=NULL

Apparently it doesn't know the value of the TimeStamp, since the value of
NULL is passed. I put the RowVersion in the gridview and got the same result.
 
Hello Chunk

I also did not notice the property ¡°StoreOriginalValuesInViewState¡± at the
beginning of the thread. You may want to have a look at the ¡°Remarks¡±
section of LinqDataSource.StoreOriginalValuesInViewState Property in MSDN:
http://msdn.microsoft.com/en-us/lib...atasource.storeoriginalvaluesinviewstate.aspx

<quote>
By default, when update and delete operations have been enabled, the
LinqDataSource control stores the original values for all the records in
view state. The LinqDataSource control stores values for all primary keys
and all properties not marked with UpdateCheck.Never in the Column
attribute. You set the UpdateCheck property of the Column attribute in the
O/R Designer.

Before LINQ to SQL updates or deletes data, it checks the values in view
state against the current values in the data source. If the values do not
match, the data source record has changed. In that case, LINQ to SQL throws
an exception and does not continue with the update or delete operation. For
more information about LINQ to SQL, see LINQ to SQL.

Storing the original values in view state can cause the page size to become
unnecessarily large and can expose sensitive data to a malicious user. You
can disable storing values in view state by setting the
StoreOriginalValuesInViewState property to false. If you do this, you must
provide your own way to make sure that the data has not changed. If you set
the StoreOriginalValuesInViewState property to false, the original values
are not persisted in view state for the data-bound control. In that case,
LINQ to SQL cannot verify the integrity of the data. LINQ to SQL will throw
an exception that indicates a data conflict even if the data in the data
source has not actually changed.

If the underlying data source contains a timestamp field that is
automatically updated during an update, you can store only that value in
view state. In that case, the timestamp property in the entity class is set
to IsVersion=true and all the properties are set to UpdateCheck.Never.
Because a timestamp field is automatically updated by the database every
time that data in that record changes, LINQ to SQL determines from that
value if data has changed. This helps reduce the size of view state, and no
sensitive data is exposed. LINQ to SQL will check for data consistency by
comparing the timestamp value in view state with the timestamp value in the
database. For more information, see Walkthrough: Using a Timestamp with the
LinqDataSource Control to Check Data Integrity.
</quote>

As well as this walkthrough: Using a Timestamp with the LinqDataSource
Control to Check Data Integrity
http://msdn.microsoft.com/en-us/library/bb470449.aspx

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Chuck P said:
I got the delete to work by changing the LinqDataSource to storeviewstate
values.
The documentation says I don't have to:
http://msdn.microsoft.com/en-us/library/bb547113.aspx
The docs aren't to clear though:

If you programmatically create the context object in the Selecting event
and
you do not have to store original values in the view state, the
ContextCreating and ContextCreated events are skipped.

I do create a context object, however, I guess it gets assigned during
e.Result=query?
If I turn viewstate off my sql statement becomes:

DELETE FROM [dbo].[UsersInRoles] WHERE ([UsersInRolesID] = @p0) AND
([RowVersion] = @p1)',N'@p0 int,@p1 timestamp',@p0=75,@p1=NULL

Apparently it doesn't know the value of the TimeStamp, since the value of
NULL is passed. I put the RowVersion in the gridview and got the same
result.
Click to expand...
 
I got the delete to work by changing the LinqDataSource to storeviewstate
values.
The documentation says I don't have to storeviewstate:
http://msdn.microsoft.com/en-us/library/bb547113.aspx
The docs aren't to clear though:

"If you programmatically create the context object in the Selecting event and
you do not have to store original values in the view state, the
ContextCreating and ContextCreated events are skipped."

I do create a context object, however, I guess it gets assigned during
e.Result=query?
If I turn viewstate off my sql statement becomes:

DELETE FROM [dbo].[UsersInRoles] WHERE ([UsersInRolesID] = @p0) AND
([RowVersion] = @p1)',N'@p0 int,@p1 timestamp',@p0=75,@p1=NULL

Apparently it doesn't know the value of the TimeStamp, since the value of
NULL is passed. I put the RowVersion in the gridview and got the same result.

So is the documentation wrong or can you really do this?


Also is their a way to get the DataContext from GridViewDeletedEventArgs or
the Grid's
DataSource? My exception handler doesn't have access to the page load event.
 
Hello Chunk

The document http://msdn.microsoft.com/en-us/library/bb547113.aspx says:
"If you programmatically create the context object in the Selecting event
AND
you do not have to store original values in the view state, the
ContextCreating and ContextCreated events are skipped."

The two "if" clauses are parallel:
If you programmatically create the context object in the Selecting event
-AND-
If you do not have to store original values in the view state.

That's to say, we can either choose to store original values (the default
setting), or not to store the original values. However, if we choose the
latter, there are some additional thing to do to verify the integrity of
the data. I'm not sure whether you've read my quote of MSDN in the last
reply. It reads:
<quote>
Storing the original values in view state can cause the page size to become
unnecessarily large and can expose sensitive data to a malicious user. You
can disable storing values in view state by setting the
StoreOriginalValuesInViewState property to false. *If you do this, you must
provide your own way to make sure that the data has not changed.* If you
set the StoreOriginalValuesInViewState property to false, the original
values are not persisted in view state for the data-bound control. In that
case, LINQ to SQL cannot verify the integrity of the data. *LINQ to SQL
will throw an exception that indicates a data conflict even if the data in
the data source has not actually changed.*
</quote>
(Please note the bold part in the above quote)

It also says:
If the underlying data source contains a timestamp field that is
automatically updated during an update, you can store only that value in
view state.

In your SQL log, the timestamp is NULL because it cannot find the original
value (StoreOringalValueInViewState=false), thus it is reasonable to have a
NULL in the SQL statement.

DELETE FROM [dbo].[UsersInRoles] WHERE ([UsersInRolesID] = @p0) AND
([RowVersion] = @p1)',N'@p0 int,@p1 timestamp',@p0=75,@p1=NULL

For the second question: is there a way to get the DataContext from
GridViewDeletedEventArgs or the Grid's DataSource?
No, DataContext cannot be retrieved from them. Is there any chance in your
development context to get e.Result from ContextCreated callback? What we
need to do is to store the reference to that e.Result (a DataContext
object) somewhere, and use the reference in Deleted event handler.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Back
Top