- your code doesn't show where you open/close the connection; but the reader here will actually only be open while you are iterating the data. deferred execution, etc. the only bit of your code that does this is the
.tolist(), so it'll be fine. in the more general case, yes: the reader will be open for the amount of time you take to iterate it; if you do a
.tolist()that will be minimal; if you do a
foreachand (for every item) make an external http request and wait 20 seconds, then yes - it will be open for longer.
- both have their uses; the non-buffered approach is great for huge results that you want to process as a stream, without ever having to load them into a single in-memory list (or even have all of them in memory at a time); returning a list keeps the connection closed quickly, and makes it easy to avoid accidentally using the connection while it already has an open reader, but is not ideal for large results
if you return an iterator block, the caller can decide what is sane; if you always return a list, they don't have much option. a third way (that we do in dapper) is to make the choice theirs; we have an optional
bool parameter which defaults to "return a list", but which the caller can change to indicate "return an iterator block"; basically:
bool buffered = true
in the parameters, and:
var data = queryinternal<t>(...blah...); return buffered ? data.tolist() : data;
in the implementation. in most cases, returning a list is perfectly reasonable and avoids a lot of problems, hence we make that the default.
this answer ignores flaws in the shown implementation and covers the general idea.
it is a tradeoff - it is impossible to tell whether it is a good idea without knowing the constraints of your system - what is the amount of data you expect to get, the memory consumption you are willing to accept, expected load on the database, etc
how long data reader’s connection will be opened?
the connection will remain open until the
reader is dismissed, which means that it would be open until the iteration is over.
when i consider code performance factor only, is this a good idea to use
yield returninstead of adding record into a list and returning the whole list?
this depends on several factors:
- if you are not planning to fetch the entire result,
yield returnwill help you save on the amount of data transferred on the network
- if you are not planning to convert returned data to objects, or if multiple rows are used to create a single object,
yield returnwill help you save on the memory used at the peak usage point of your program
- if you plan to iterate the enture result set over a short period of time, there will be no performance penalties for using
yield return. if the iteration is going to last for a significant amount of time on multiple concurrent threads, the number of open cursors on the rdbms side may become exceeded.
- I am wondering about the state of connection and impact on code performance by 'yield' while iterating over data reader object
- Why does Resharper suggest a code change and then complain about the change?
- What is the Efficiency and Performance of LINQ and Lambda Expression in .Net?
- How to count the number of code lines in a C# solution, without comments and empty lines, and other redundant stuff, etc?
- Enumerable.Empty<T>().AsQueryable(); This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code
- Refactor Linq code and "LINQ to Entities does not recognize the method"
- Performance tuning C# permutations and SHA1 code
- What is the difference between ((IEnumerable)source).OfType<T>() and source as IEnumerable<T>
- increase Performance of the code
- Why not reveal the type and identity of the source to the client?
- How to iterate at elements from a sub list and then remove the sub list from the list? With great performance
- How to change the precision and the scale of decimal globally through code first?
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- Why are stored procedures, functions, and views put into a .dbml file instead of the code file?
- Comparing performance between custom implemented LINQ methods and the original
- scala -> use .net (linq) and java code base in the same program?
- How to add a checkbox column to a ListView in WPF and get the state of it
- Impact of using AsParallel() and AsSequential() in the same query? C#
- Error : The ObjectContext instance has been disposed and can no longer be used for operations that require a connection
- The ObjectContext instance has been disposed and can no longer be used for operations that require a connection
- Comparing two lists of Strings and counting the matches, possible performance problem
- Best way to find if a value is present in the array, and if so execute code
- How to assign LINQ Query to a variable and then use it later in the code
- vb. net search about sentence and return the first word index
- I want to union together four queries and set this to be the repeater's data source
- Querying the database using EF Code First and Linq
- Linq to xml with missing nodes in the source XML and null-coalescing operator won't work
- How to insert a record in lambda expression and possible way to shorten the length of code
- how the below cases execute and how can i do it more efficient into the memory and performance related?
- What is the difference between IQueryable<T> and IEnumerable<T>?
More Query from same tag
- Why is ReSharper telling me my expression is always true?
- Entity framework where, order and group
- DataTable: Get Max Value Using LINQ With Criteria Field (GroupBy)
- SelectListItem and linq any and select statement
- Can or should I join two Where clauses together in a LINQ Query?
- Linq making very inefficient Entity Framework query
- Remove From Duplicate Starting Names From List Linq
- Composing a where clause in LINQ query at runtime
- Extract node from XML document
- LINQ-ify Comparision Check
- C# Linq .Contains list of string
- Order a query based on a field pointing to the same table
- Weird timeout when using linq/entity, MVC2
- Entity Framework Core nested expressions
- Putting an Anonymous collection into a Class
- Acquiring all values of nested dictionaries that contain the same key using Linq
- Most efficient way to select a specific object type in Dictionary and delete it
- LINQ expression to extract only two word phrases
- Linq query for selecting an item from tree structure, but to look in the whole depth
- How to Custom Group By multiple records according some criteria
- converting dictionary string string to json string
- Convert sql select to LINQ
- When to use the asynschronus methods with EF Core?
- From Eric Lippert's blog: "don't close over the loop variable"
- C# : How to get anonymous type from LINQ result
- Convert VBNet LiNQ to C#
- Select Specific Elements from XML
- Getting distinct not null dates from datetime collection
- C# LINQ unable to enumerate matrix
- Binding the multiple result set from store procedure into one list