What collection should I use in a linq-to-sql query? Queryable vs Enumerable vs List

Imagine the following classes:

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

class Underage
{
    public int Age { get; set; }
}

And I do something like this:

var underAge = db.Underage.Select(u => u.Age)   .ToList()/AsEnumerable()/AsQueryable() 

var result = db.Persons.Where(p => underAge.Contains(p.Age)).ToList();

What is the best option? If I call ToList(), the items will be retrieved once, but If I choose AsEnumerable or AsQueryable will they be executed everytime a person gets selected from database in the Where() (if that's how it works, I don't know much about what a database does in the background)?

Also is there a bigger difference when Underage would contain thousands of records vs a small amount?

Answers


None.

You definitely don't want ToList() here, as that would load all of the matching values into memory, and then use it to write a query for result that had a massive IN (…) clause in it. That's a waste when what you really want is to just get the matching values out of the database in the first place, with a query that is something like

SELECT *
FROM Persons
WHERE EXISTS(
  SELECT NULL FROM 
  FROM Underage
  WHERE Underage.age = Persons.age
)

AsEnumerable() often prevents things being done on the database, though providers will likely examine the source and undo the effects of that AsEnumerable(). AsQueryable() is fine, but doesn't actually do anything in this case.

You don't need any of them:

var underAge = db.Underage.Select(u => u.Age);

var result = db.Persons.Where(p => underAge.Contains(p.Age)).ToList();

(For that matter, check you really do need that ToList() in the last line; if you're going to do further queries on it, it'll likely hurt them, if you're just going to enumerate through the results it's a waste of time and memory, if you're going to store them or do lots of in-memory operations that can't be done in Linq it'll be for the better).


If you just leave your first query as

  var underage = db.Underage.Select(u => u.Age);

It will be of type IQueryable and it will not ping your database ( acutaly called deferred execution). Once you actually want to execute a call to the database you can use a greedy operator such as .ToList(), .ToArray(), .ToDictionary(). This will give your result variable an IEnumerable collection.

See linq deferred execution


Your should use join to fetch the data.

var query = (from p in db.Persons
             join u in db.Underage
              on u.Age equals p.Age
             select p).ToList();

If you not use .ToList() in above query it return IEnumerable of type Person and actual data will be fetch when you use the object.

All are equally good ToList(), AsEnurmeable and Querable based on the scenario you want to use. For your case ToList looks good for me as you just want to fetch list of person have underage


Need Your Help

Leaflet map not displayed properly inside tabbed panel

javascript css twitter-bootstrap leaflet

I'm trying to use Leaflet.js to display a map inside a tabbed panel from Twitter Bootstrap, but is behaving in a strange way:

Port doesn't close when I run ListenAndServe() in a test function, how do I fix this?

http go server

When I call http.ListenAndServe() in a test function the port does NOT close even after the tests have finished and the process has terminated. So the next time I run the tests I get the error "