Hacker News Reader

Comment by red_admiral | Hacker News Reader

Indeed. My understanding of LINQ is that a where clause, for example, creates a matching clause in the SQL it generates, so the database only fetches the elements that you want. In contrast, the Java solution seems to "select *" in SQL then iterate over the results in the application itself.

View on HN

LINQ itself just creates expression trees. The expression tree is then translated into code by another provider (for lack of a better term). The code generated can either operated on an in memory collection or generate any other type of query.

var seniors = customers.Where(c => c.age > 65)

Could either represent C# IL, a MongoQuery, a SQL query, an ElasticSearch query, etc. depending on what type of variable “customer” is.

Just to be a little pedantic and to inform those that might not be familiar, LINQ only creates an expression tree when the type of the source object is IQueryable. If the type of the source object is IEnumerable only (such as an in-memory list), it will not create an expression tree and instead will use the Enumerable static class methods passing in the lambdas as delegates (aka function pointers). In the latter case, there is no "code generated", it will directly enumerate the IEnumerable and do things like an "if" statement inside a "foreach" loop in the case of "where", for example.

Also, you don't need a better term than "provider" as that's what they're called. :-) The IQueryable interface includes a property called Provider that returns an IQueryProvider which can operate on an expression tree.

> LINQ only creates an expression tree when the type of the source object is IQueryable

To be further pedantic: it creates an expression when the method accepts an Expression<T>, which IQueryable happens to do.

Also, in my experience knowing when you will be working with an IQueryable and knowing when you will be working with a realized IEnumerable is a very important thing to be learned when working with Linq and datastores. You can easily load full tables into memory if you aren't careful.

This times 100. Ended up having a week of intermittent outages when a Dev on my team created a single helper method which took in an Func<> not an Expression<Func<>>. Having the same interface for both is imo dangerous

> Indeed. My understanding of LINQ is that a where clause, for example, creates a matching clause in the SQL it generates, so the database only fetches the elements that you want. In contrast, the Java solution seems to "select *" in SQL then iterate over the results in the application itself.

That's the difference between IQueryable and IEnumerable. If you use IEnumerable, it'll filter on the client side rather than the server side. Potentially a performance and resource and even security/privacy problem. With IQueryable, the filtering is done on the server side.

Personally, the best thing to do is to use stored procedures. ORMs and query generation on the client side is such a bad idea. But it's easy, so people go for the easy solution.

Stored procedures? No. It makes it much more difficult to keep the code and the stored procedures in sync. Also unit testing is more difficult and slower. With linq, you can unit test your queries without ever touching a database by mocking out your context and using Lists (https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....)

Also, it’s a lot easier and less error prone to compose dynamic queries with Linq - ie keep chaining Where clauses and OrderBy’s based on a condition.

You can also do things like create methods that take predicates.

CustomerRepo.Find( c=>c.age > 65)

And define the Find method.

List<Customer> Find(Expression<Func<Customer,T>> predicate).

I’ve even had one project where the backing store could either be SQL Server or Mongo. We were able to use the same interface with methods like the Find method above and the repos translated the predicates appropriately.

Just because I don't want bad syntax to live on for eternity on HN...

List<Customer> Find(Expression<Func<Customer,T>> predicate)

Should be

List<Customer> Find(Expression<Func<Customer,bool>> predicate)

> Also, it’s a lot easier and less error prone to compose dynamic queries with Linq

That's the general assumption of people not familiar with SQL, RDBMs and full stack architecture. One of my issues is that ORMs and LINQ have become crutches. Also, you are ignoring the major problem of security and auditing. If you are working on a local toy project, then sure, you can just do whatever you want. But if it is an enterprise project with real security concerns, the idea of giving read/write access to web server accounts is amateur hour. Trust me, I've seen it with my own eyes. People who spend 2 days reading "Learn web dev in 21 days" and "Learn SQL in 21 days" and putting today something akin to "Hello world" projects because their managers didn't know any better.

> - ie keep chaining Where clauses and OrderBy’s based on a condition.

You can use LINQ, chaining, lazy evaluation and all that on data/Enumerables/sequences retrieved from a stored procedure.

But hey, what do I know.

Well, it depends. Linq can certainly operate on in-memory lists.

Reply on news.ycombinator.com