Linq SubmitChanges and DataLoadOptions

[Chuck P]

I have some code that has a DataLoadOption to load Master/Detail records.
When I update the Master table, the object gets returned but the Details
records are not loaded. Is their a work around for this?

using (MyDataContext dc = new MyDataContext())
{
DataLoadOptions lo = new DataLoadOptions();
lo.LoadWith<Job>(s => s.JobSections);

var query = from obj in dc.Jobs
where obj.JobID == 1
select obj;

job = query.Single(); //yep job object has detail
section data

dc.SubmitChanges();
//nope job does not have detail section data.
}

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,

I'm not sure I have understood you issue completely. It seems you want
details records be loaded when querying Master records, correct?

Creating a DataLoadOptions is not enough, you need to assign it into
datacontext object.
For example:
DataLoadOptions lo = new DataLoadOptions();
lo.LoadWith<Job>(s => s.JobSections);
* dc.LoadOptions = lo;
Then, JobSections object will be loaded when you query job object.

However, I cannot understood why do you want to re-load jobsections objects
after dc.SubmitChanges()? It seems you didn't make any change in your
details records. Could you please clarify this for us? Thereby we can
understand your issue more clearly. Thanks.

Have a great day,
Best regards,
Wen Yuan

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.

 

[Chuck P]

I did do dc.LoadOptions = lo; just didn't post all the code.

I want to reload the detail section data after submit because I need to
display all the master/detail data.

When I first load the master/detail data, I put it in a form and make it
available for editing.

The user may or may not edit the master or the detail sections.
In any case I need to redisplay the data after the edit, so I need to have
the section data present.

Get Master/Detail Data
Display Master/Detail Data
Allow User Edit of data
Update changes to database
Display Master/Detail data

What happens when you do a dc.SubmitChanges? I know any edited objects are
reloaded with Identity values or calculated values. The load options are
ignored?
If I edit one record in the detail section will it return all the detail
records or just the edited one?

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,
Thanks for your reply.

Actually, dc.SubmitChanges() method doesn't reload the edited objects. This
method just generates Update Query for SQL Database. It doesn't query
edited objects from database after updating. This behavior is different
from DBAdapter. In DBAdapter senarior, Adapter will retrieve rows from
database after updating. In Ling to SQL scenario, if you want to reload
objects after SubmitChanges method, you can use dc.Refresh() methd.Because
you have set load options for dataContext, Child rows will be reloaded
automatilly.

For Example:
dc.SubmitChanges();
dc.Refresh(RefreshMode.OverwriteCurrentValues, dc.jobs);

But, this method maybe very slow, because it refreshs row by row.

Hope this helps. Please feel free to let us know if you have any more
concern. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan
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]

I did a dc.Object.InsertOnSubmit(object);
dc.SubmitChanges()

When I look at my object it has Identity values.
The SQL trace showed that after the insert statement a select statement
occurred:
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 var ....

So I would say on an insert you do get your object reloaded.


On my Update without any calculated columns I did not see a select.

Also on my update if I have detail records loaded but with only a change in
the master table, after the dc.SubmitChanges the detail records are gone.
This seems very strange since they were not edited.

If I leave the dc open, it will load the records when I access the object.
It looks like leaving the dc open is better than a refresh because I won't
be requering the master and detail records.

I certainly don't understand the design logic or reasons for nulling the
detail records when the master table is updated.

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,
Thanks for your reply.

If you have set Auto-Sync on the columns, LINQ to SQL will fetch them back
after UpdateChanges() method. For Identity Columns or Calculated Columns,
those columns will be set as "Auto-Sync" in LINQ to SQL. Please check
"Auto-Sync" property on columns in .dbml file. You will found the reason
why LINQ to SQL retrieve Identity Columns in UpdateChanges() method. If you
have columns that are also generated at the database, set Auto-Sync on
those columns and LINQ to SQL will fetch them back. Moreover, when tracing
query on SQL server, you can found the generated select query only fetch
the related column from database, rather than fetch the whole row from
database. Thus, child rows aren't loaded from database.

In general, updating Master Table shouldn't clear the Detailed records. How
did you edit the master records? I tried the code which you pasted in
thread on my side. It seems fine. HasLoadedOrAssignedValues property
returns True after I Submitted Changes to database.

using (DataClasses1DataContext dc = new DataClasses1DataContext())
{
dc.Log = Console.Out;
DataLoadOptions lo = new DataLoadOptions();
lo.LoadWith<Table_1>(s => s.Table_11s);
dc.LoadOptions = lo;
var query = from obj in dc.Table_1s
where obj.c1 == 2
select obj;
Table_1 job = query.Single();
job.c2 = "10";
dc.SubmitChanges();

System.Console.WriteLine(job.Table_11s.HasLoadedOrAssignedValues);
}

Am I missing anything here? Please don't hesitate to correct me. I will try
again on my side. We are glad to assist you.

Have a great day,
Best regards,
Wen Yuan

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.

 

[Cor Ligthert[MVP]]

Wen,

The ADONET DBUPDATE does not refresh data in the datasets beside setting
things as the correct timestamps and keys with the Insert.

Before somebody get other ideas.

Cor