locked
How to use ServerApplicationContext to do a remote query across multiple entities RRS feed

  • Question

  • Hello everybody,

    My data model is like this:

    "Case" -> one-to-many -> "Document"


    Document has  the fields:

    - "IsFinalised" (boolean)

    -  File (large binary)

    My business rule is: 'a Case is closed if all its documents are finalised'.

    I am trying to do a query which returns whether a case is closed using ServerApplicationContext, but I don't want the query to load the large binaries for each Document into memory for given case.

    Does this do the job?

    int IsCaseClosed(int caseId)
    {
    
    using (var sac = ServerApplicationContext.CreateContext())
    {
        var relevantCase = sac.DataWorkspaces.ApplicationData.Cases.FirstOrDefault(c => c.Id = caseId);
    
       return relevantCase.Documents.All(d => d.IsFinalised);
    }
    
    }

    Monday, February 2, 2015 11:09 PM

Answers

  • You can see the generated sql by running SQL Profiler against the SQL server. Profiler logs all sql sent to the server, with settings available for filtering if it's a busy server. I usually run Profiler on a local development server, since the SQL will presumably be identical on the production server.


    Paul

    • Marked as answer by Angie Xu Thursday, February 12, 2015 7:13 AM
    Tuesday, February 3, 2015 12:06 AM

All replies

  • I think what you have is pretty close.  I would probably write it like this:

    bool IsCaseClosed(int caseId)
    {
    bool isClosed = false;
    if caseId > 0
    {
    using (var sac = ServerApplicationContext.Current ?? ServerApplicationContext.CreateContext())
    {
    isClosed = sac.DataWorkspace.ApplicationData.Cases_Single(caseId).Documents.All(d => d.IsFinalised);
    }
    }
    return isClosed;
    }


    Monday, February 2, 2015 11:31 PM
  • Thanks, that looks like an improvement because Cases_Single method will throw an exception if there is not a result.

    Why is it better to reuse an existing ServerApplicationContext?  (This method will be exposed via a WebApi controller.)

    Do you know any way I can see the SQL that this query generates?

    Monday, February 2, 2015 11:55 PM
  • You can see the generated sql by running SQL Profiler against the SQL server. Profiler logs all sql sent to the server, with settings available for filtering if it's a busy server. I usually run Profiler on a local development server, since the SQL will presumably be identical on the production server.


    Paul

    • Marked as answer by Angie Xu Thursday, February 12, 2015 7:13 AM
    Tuesday, February 3, 2015 12:06 AM