Showing SQL that will be executed before saving

Thread is closed for posting
2 posts, 0 answers
  1. Jonah
    Jonah avatar
    28 posts
    Member since:
    Oct 2012

    Posted 26 Feb 2015 Link to this post

    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; });
    FcoProduct p = new FcoProduct();
    Utility.CopyObject.CopyValues(prod, p);
    p.SortOrder = SelectedStyleGroup.FcoProducts.Count;
    p.FcoStyleGroup = SelectedStyleGroup;
    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;
    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;
    foreach (var sd in sk.FcoSketchDetails)
    FcoSketchDetail sdNew = new FcoSketchDetail();
    sdNew.FcoSketch = newSk;

    Utility.CopyObject.CopyValues(sd, 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;
  2. Viktor Zhivkov
    Viktor Zhivkov avatar
    324 posts

    Posted 27 Feb 2015 Link to this post

    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)
    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.    }

    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.

    Viktor Zhivkov
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top