Converting our existing Stored Procedures to LINQ

[Vincent]

Hi,

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:

IF((SELECT COUNT(1)
      FROM _POSPaymentsDetail
      WHERE Branch = @Branch
      AND UniqueId = @UniqueId
      AND PostingStatus = 'COMPLETE'
      AND PostingError = '') = (SELECT COUNT(1)
                                      FROM _POSPaymentsDetail
                                      WHERE Branch = @Branch
                                      AND UniqueId = @UniqueId))
  BEGIN

     UPDATE _POSTransactionHeader
     SET PostingStatus      = 'COMPLETE'
     WHERE Branch       = @Branch
     AND   UniqueId     = @UniqueId

  END

Thank you for your help with getting us going on this road!

[Doroteya]

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

        dbContext.SaveChanges();

    }

}

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. 


Greetings,
Doroteya
the Telerik team