Stored procs and Linq-to-sql

Question

0.00/5 (No votes)

See more:

Can some one please tell me how linq-to-sql's execution plan works when using stored procedures.
In the following example,what would the generated SQL statements look like?

The SP to use

C#

CREATE PROCEDURE dbo.TestLINQ
AS
BEGIN
    SELECT PartID, Description FROM Part
END

The C# that calls it

C#

using (DataContextX db = new DataContext())
{ 
       var qry = db.TestLINQ();
       someCtrl.DataSource = qry.Where(x => x.PartID < 100).OrderBy(x => x.Description);
       someCtrl.DataBind();
}

Would this first call EXEC dbo.TestLINQ and load it into some temporary table (or CTE), and then filter the results, then sort the results? Or what?

I've got a bunch of existing stored procedures that need additional filtering on the fly. So I'm trying to establish if LINQ is an efficient method to use. Or should I take the long road and add a stack of if statements and new parameters to the stored procedures?

Posted 16-Jul-14 0:43am

TheRedEye

Updated 16-Jul-14 1:40am

Thanks7872

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

[Praveen] · Answer 1 · 2014-07-16T01:06:00

Solution 1

When you call the procedure using the EF it will load the result in collection of may be some anonymous object.
You can then use the filter on the result set as per the conditions using linq preferably

Posted 16-Jul-14 1:06am

[Praveen]

Comments

TheRedEye 16-Jul-14 10:27am

Yes, but specifically, what SQL statements are generated. I'm trying to see if I can get a performance improvement here. Does my example make 3 round trips to the server or just one?

Linq will normally build a sql statement and only execute that statement against the database when the collection is iterated. However I cannot find any documentation on when/how linq-to-sql stored proc execute the built sql statement.

I've also tried using LINQPad to see the sql generated, but strangely stored procs in LINQPad seem to return a 'ReturnDataSet' data type as opposed to visual studio which returns ISingleResult<testlinqresult> as the anonymous type.