Vista - LINQ-to-SQL in an insert-only DB scenario

We’re storing our main entity in an insert only table which stores the
history of past revisions, but we’re facing problems with storing this
history as LINQ will only update the entity, and not reinsert it with a
different revision number.

Compounding the issue, we’ve also got an associated table storing properties
for our entities which is not revisioned, but we still want changes to the
children of our entity (additions, changes & removals) reflected in the
properties table when we insert the latest version of our entity.

MainObject{
string Name, //PK
string Path, //PK
int RevisionNum, //PK in DB
EntitySet<ChildObject> Children
}

The main problem right now is that when Updating instances of our
MainObject, we need a new record to be stored in the DB. When we were mapping
the object to insert via a stored proceedure, the ChildObjects weren't being
processed. Once we took out the stored proceedures, everything started
working very nicely.

How can we get LINQ to insert new records as part of the update, but also
handle the child objects nicely?

--
Eric Falsken
Technical Evangelist: db4o
http://www.db4o.com

Hi Eric,

From your description, I understand that the problem here is the default
generated update code won't help on your additional inserting (to a insert
only table), correct?

For such case, I think the directly generated LINQ update method may have
some limitation, it just focus on the original database table. If you want
some additional code logic when performing the update, I suggest you
consider the followign approaches:

1. LINQ DataContext class and Table mapped class have some partial methods
which you can define via your own code. For example, for the following like
LINQ class(IDE generated), it provide several partial methods:


===========================

[System.Data.Linq.Mapping.DatabaseAttribute(Name="testdb")]
public partial class UpdateDCDataContext : System.Data.Linq.DataContext
{

private static System.Data.Linq.Mapping.MappingSource mappingSource = new
AttributeMappingSource();

#region Extensibility Method Definitions
partial void OnCreated();
partial void Insertedit_table(edit_table instance);
partial void Updateedit_table(edit_table instance);
partial void Deleteedit_table(edit_table instance);
...................

======================

we can implement any of them in our own code as below:

======================
public partial class UpdateDCDataContext : System.Data.Linq.DataContext
{


partial void Updateedit_table(edit_table instance)
{

MessageBox.Show("Updateedit_table");
//reference the updated instance here
}


}
====================

thus, when you perform update operation (via the datacontext's
SubmitChanges method), the above partial method will get executed. You can
put addtional code logic in that method(just like an updating event).



#Understanding the DataContext
http://blogs.msdn.com/charlie/archiv...-the-dataconte
xt.aspx

#LINQ to SQL Tips 8: How to (and why) create a partial class in the
designer to augment generated code
http://blogs.msdn.com/dinesh.kulkarn...to-sql-tips-8-
how-to-and-why-create-a-partial-class-in-the-designer-to-augment-generated-c
ode.aspx


2. Also, have you considered creating another database table(in database)
which contains the same columns fields(schema). Thus, you can add LINQ
class to perform updating against that table. And for the inserting, you
can create a server-side database trigger to insert a new record into the
"insert only table". This maybe a pure database side solution(not quite
LINQ involved).

3. Or maybe we can consider separate this updating approach out and use
dedicated SQL+ ADO.NET command based method to perform the updating. Just
like traditional ADO.NET code.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


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:
msdnmg@xxxxxx.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

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://support.microsoft.com/select/...tance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.







--------------------propertiesmappingbeing

The extension methods are great, but there's still the problem of when we go
do to the update, it still is performing a SQL update operation, and we don't
want the table updated. How can we change it to an insert operation? We don't
need to "perform an extra action" after/before the update, and I'd rather not
use the trigger approach. This whole thing should work in a single table
easily enough.

We have a dataobject that came from the DB. we want to increment the
"revision" field. Right now, LINQ throws an exception because we are
modifying the primary key. If we call InsertOnSubmit, then it throws a
different exception, something about the row already being attached.


--
Eric Falsken
Technical Evangelist: db4o
http://www.db4o.com

Basically, the only way to make this happen is to move the historical records
into a separate table by copying on updating. I guess we'll go down that
road. I guess it's better this way since the history will be in a separate
table and not slowing down the regular operations against live (current) data.

--
Eric Falsken
Technical Evangelist: db4o
http://www.db4o.com

Thanks for your reply Eric,

yes, using two tables will resolve this be involves much more overhead. Is
it convenient for you to create a simplified project(maybe use a sqlexpress
database file) and the necessary code to demonstrate the problem. Thus, I
can perform some further research locally against on it.

Sincerely,

Steven Cheng
Microsoft MSDN Online Support Lead


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:
msdnmg@xxxxxx.


--------------------recordsthatdata.