How does .Net handle the connection in LINQ-To-Entity inside using context?

Question

I am using Linq-To-Entity in my project. But, I am hesitating to use Stored Procedure or LINQ to Table or View in some situations. But, I generally prefer to use LINQ, because of the pleasent syntax. I have searched google, but not found detailed asnwer to my question.

Let's consider this code:

using (NorthwindEntities db = new NorthwindEntities())
{
    var custs = from c in db.Customers where c.City == "London" select c;
    var edus = from c in db.Educations where c.Education != "2" select c;
    // ... and so on
}

Questions:
1. Does it open a new connection for each query? If it does, then it is not adviced to use above queries seperately?
2. Also, could you advice me is there any situations that I must use Stored Procedure instead of LINQ?

I have used sproc instead of linq when the query gets too complicated(unreadable) for linq or when there is no support for some things like spatial types in EF. also read this: http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures — DotNetWala, May 20 '14 at 16:39
The only time you MUST use a stored procedure in EF is when you want to use something that is not supported by EF. One example would be using the Full Text Search commands such as CONTAINS or if you need to write an XPath query on an xml column. — Dismissile, May 20 '14 at 19:17

D Stanley · Accepted Answer · 2014-05-20T19:14:03.490

5

Does it open a new connection for each query?

Sort of.

According to the documentation

When a query method is called, the connection is opened, and it remains open until the ObjectResult has been completely consumed or disposed.

However, connections are pooled by .NET, so the same connection object will be reused (and I assume re-opened if necessary).

Note that a query is not executed until you enumerate over it (using foreach, ToList, ToArray, Single, First, etc.). Until then it's just a query. What that means is you must do something with the query before the context is disposed, or you'll get an exception.

Are there any situations where I must use Stored Procedure instead of LINQ?

If your queries are too complex to be constructed using Linq, then a stored procedure is a reasonable alternative.

edited May 20 '14 at 19:14

answered May 20 '14 at 17:01

D Stanley

149,601
11
178
240

Small nitpick but `AsEnumerable` does not execute the query. – Dismissile May 20 '14 at 19:12
@Dismissile True - I have removed it. – D Stanley May 20 '14 at 19:14
@DStanley Thanks, for the asnwer. Then does it mean that other two answers to my question are wrong? Also, from the docmentation it seems that I can explicitly open and close connection. Do you have information about that? `context.Connection.Open();` and `context.Connection.Close();` – Farhad Jabiyev May 21 '14 at 04:43
Yes you can, but why would you? EF is designed to manage connections effectively - unless you _know_ there's a problem, don't try to fix it yourself. – D Stanley May 21 '14 at 12:34
@DStanley Okay. I got you. Then, lets say I will use the sample code from my question to execute 10 query. From what you have said, it means that using SP will be faster than LINQ. Because, LINQ will re-open the connection. And the difference will be much, if I will execute 100 query. Am I true? – Farhad Jabiyev May 21 '14 at 15:42
@FarhadJabiyev Possibly, but you'd have to measure it to be certain. Even if it is, is the speed increase worth the overhead of maintaining sprocs outside of your main code? – D Stanley May 21 '14 at 15:59

score 2 · Answer 2 · answered May 20 '14 at 16:33

2

It does that in the constructor, when initializing the new context.

NorthwindEntities db = new NorthwindEntities();

answered May 20 '14 at 16:33

Parimal Raj

20,189
9
73
110

score 2 · Answer 3 · answered May 20 '14 at 16:33

2

LINQ opens a connection when you initialize your context.

NorthwindEntities db = new NorthwindEntities()

And it will be closed when your context is disposed.

answered May 20 '14 at 16:33

Loetn

3,832
25
41

2

Are you certain about when they're opened? The documentation I found states that there opened when a query is executed. – D Stanley May 20 '14 at 17:02
@DStanley After reading your answer, I do have my doubts. I guess what I thought about this is wrong. – Loetn May 21 '14 at 07:53

How does .Net handle the connection in LINQ-To-Entity inside using context?

3 Answers3

Linked