Converting our existing Stored Procedures to LINQ

2 posts, 0 answers
  1. Vincent
    Vincent avatar
    1 posts
    Member since:
    Mar 2013

    Posted 13 Mar 2013 Link to this post


    My company is looking for a solution to allow us to move away from using stored procedures and to substitute them with functionality we can employ from the code. OpenAccess looks like the right tool for this job. 

    We would look at using LINQ to convert the existing stored procedures but even going through the OpenAccess documentation it still seems pretty difficult how we would elegantly rewrite some of these stored procedures without doing multiple selects.

    If we could maybe just have a pointer in the right direction by means of a simple example that'll be a great help.

    My questions would be:

    1. How many times would the following procedure need to execute against the database with select statements?
    2. Is there any performance issues calling multiple selects via LINQ to construct this statement when compared to the sql stored procedure?
    3. Is there a way to update a value in a table without having to load the record first?

    For example this procedure:

          FROM _POSPaymentsDetail
          WHERE Branch = @Branch
          AND UniqueId = @UniqueId
          AND PostingStatus = 'COMPLETE'
          AND PostingError = '') = (SELECT COUNT(1)
                                          FROM _POSPaymentsDetail
                                          WHERE Branch = @Branch
                                          AND UniqueId = @UniqueId))
         UPDATE _POSTransactionHeader
         SET PostingStatus      = 'COMPLETE'
         WHERE Branch       = @Branch
         AND   UniqueId     = @UniqueId

    Thank you for your help with getting us going on this road!
  2. Doroteya
    Doroteya avatar
    498 posts

    Posted 18 Mar 2013 Link to this post

    Hello Trevor,

    Thank you for your interest in Telerik OpenAccess ORM.

    Generally, an example that may help you in the process you are starting is Telerik OpenAccess ORM 101 Linq Samples. It is distributed with our Samples Kit. There you can find a variety of Linq queries with a short description about their purpose, the SQL they produce and their result. 

    Regarding the stored procedure provided in the code snippet, I would suggest you the following translation:
    using (EntitiesModel dbContext = new EntitiesModel())
        //Statement 1
      bool comparison = dbContext.POSPaymentsDetail
            .Where(paymentDetail => paymentDetail
    .Branch == <value> && paymentDetail
    .UniqueId == <value>)
            .All(pDetail => pDetail.PostingStatus == "COMPLETE" && pDetail.PostingError == String.Empty);
        if (comparison)
             //Statement 2
            IQueryable<POSTTransactionHeader> someHeaders = dbContext.POSTTransactionHeader.Where(th => th.Branch == <value> && th.UniqueID == <value>);
            foreach (POSTTransactionHeader tHeader in someHeaders)
                    tHeader.PostingStatus = "COMPLETE";

                           //Statement 3
    For this stored procedure you will have three statements executed against the database. The first one would check whether the required condition is met, the second one would load the objects that need to be updated in the memory and the third one will persist the changes to the database.

    Regarding your performance considerations, generally the LINQ queries are expected to be slower compared to the SQL ones, but those in the given stored procedure are relatively simple and the time for their execution should not become an issue.

    Regarding your third question, about the update of the object, currently, OpenAccess requires the updated object to be loaded in memory (here you can find details). However, we are working on a feature that will allow updates without prior loading and we are planning to release in the midst of this year with it.

    I hope that works for you. If you have other questions or experience difficulties, do not hesitate to get back to us. 

    the Telerik team
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
  3. DevCraft banner
Back to Top