Click here to Skip to main content
15,882,315 members
Please Sign up or sign in to vote.
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
Updated 16-Jul-14 1:40am
v2

1 solution

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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900