CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entities
Alexander Derck
Alexander Derck

Reputation: 14488

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?

Upvotes: 3

Views: 174

Answers (3)

mrsargent
mrsargent

Reputation: 2442

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

Upvotes: 2

Jon Hanna
Jon Hanna

Reputation: 113222

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).

Upvotes: 2

Avinash Jain
Avinash Jain

Reputation: 7606

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

Upvotes: 1

Related Questions