Showing SQL that will be executed before saving

[Jonah]

I am running into a situation where I am crashing because of a null value it is trying to put in a FK field. I am doing similar inserts or adds in many other places and I can't figure out why in this instance the save is crashing. So I am wondering if the ORM is trying to insert the child item first or if it is not updating the PK/FK after it inserts the parent item?

Here is my code which is in a background worker

int index = 0;
foreach (var prod in pasteList.OrderBy(p => p.SortOrder))
{
string pcString = "Processing " + (index + 1).ToString() + " of " + pasteList.Count.ToString();
InvokeOnUIThread(() => { this.BusyMessage = pcString; });
index++;
FcoProduct p = new FcoProduct();
Utility.CopyObject.CopyValues(prod, p);
p.SortOrder = SelectedStyleGroup.FcoProducts.Count;
p.FcoStyleGroup = SelectedStyleGroup;
SelectedStyleGroup.FcoProducts.Add(p);
Dc.Add(p);
p.CtlgProduct = (from x in Dc.CtlgProducts
where x.ProductID == prod.CatalogProductID
select x).First();


foreach (var opt in prod.FcoOptions)
{
FcoOption newOption = new FcoOption();
Utility.CopyObject.CopyValues(opt, newOption);
newOption.FcoProduct = p;
newOption.ProductID = p.ProductID;
p.FcoOptions.Add(newOption);
Dc.Add(newOption);
newOption.CtlgOption = (from x in Dc.CtlgOptions
where x.OptionID == opt.CatalogOptionID
select x).First();
}
foreach (var sk in prod.FcoSketches)
{
FcoSketch newSk = new FcoSketch();
Utility.CopyObject.CopyValues(sk, newSk);
newSk.FcoProduct = p;
newSk.ProductID = p.ProductID;
p.FcoSketches.Add(newSk);
Dc.Add(newSk);
foreach (var sd in sk.FcoSketchDetails)
{
FcoSketchDetail sdNew = new FcoSketchDetail();
sdNew.FcoSketch = newSk;

Utility.CopyObject.CopyValues(sd, sdNew);
newSk.FcoSketchDetails.Add(sdNew);
Dc.Add(sdNew);
}
}
//Add a location for each quantity this item has
for (int i = 0; i < p.Quantity; i++)
{
FcoProductLocation pl = new FcoProductLocation();
pl.CheckPointID = 27;
pl.DoorsCompleted = false;
pl.FramesCompleted = false;
pl.ManuallyLocated = false;
pl.SortOrder = i;
pl.FcoProduct = p;
p.FcoProductLocations.Add(pl);
Dc.Add(pl);
}      

[Viktor Zhivkov]

Hi Jonah,

If you want to check the SQL statements that are executed during the SaveChanges() method you can use the following code extension method that wraps it in a nice package:

01.public static string SaveChangesAndGetSQL(this OpenAccessContext ctx)
02.{
03.    var originalLog = ctx.Log;
04.    StringWriter logWriter = new StringWriter();
05.    try
06.    {
07.        ctx.Log = logWriter;
08.        ctx.SaveChanges();
09.        ctx.Log = originalLog;
10.        string sql = logWriter.ToString();
11.        return sql;
12.    }
13.    finally
14.    {
15.        ctx.Log = originalLog;
16.        logWriter.Dispose();
17.    }
18.}

Same trick can be applied also for FlushChanges().

If you are curious what is SQL statement(s) of a LINQ query you can obtain that without actually executing the query by calling .ToString() over the IQueryable<T> instance.

Please note that all parameters in the SQL statements will be without values unless you set the logging level to All. My advice is to use that Logging level only during Debug sessions as it will affect negatively the performance of the Telerik Data Access runtime. Here you can find more information how to change the logging level of your application.

Regards,
Viktor Zhivkov
Telerik