3 ms·
Absolutely, there are times where programmers try to do way too much in LINQ and don't realize that the query being generated to back up their chain of LINQ ope
by rspeele 3y ago
Absolutely, there are times where programmers try to do way too much in LINQ and don't realize that the query being generated to back up their chain of LINQ operations is a monstrosity of thousands of lines of SQL that will grind the server to a hault. Or the abstraction leaks badly due to the programmer using something EF doesn't know how to translate to SQL, so it ends up loading a much larger dataset and trying to complete the rest of the filtering in memory.
As developers get more acclimated to the .NET ecosystem they usually pick up a good spidey sense for what query "shapes" are appropriate to express in LINQ and which are asking for trouble.
However, for every one of those awful LINQ queries in a codebase, I think there are 40 to 50 run-of-the-mill queries that make it worthwhile. The biggest win that it delivers over a simpler, "stringier" ORM is the ability to return structured data. I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying.
Say I want to load some Foos with their Bars. If I use straight SQL with a row mapper, I define a type to represent each FooBar row, load a List<FooBarRow> into memory from my simple left join, then I'll have to do a GroupBy in memory on that List to get the actual structure that I want: a list of Foos where each one has its own list of Bars. Notice that I also have to handle the empty-list case specially.
var fooBarRows = await connection.QueryAsync<FooBarRow>("select f.FooId, f.Name as FooName, b.BarId, b.Name as BarName from Foos f left join Bars b on b.FooId = f.FooId where whatever");
// re-shape result set manually from flat query results
var foos = fooBarRows
.GroupBy(fbr => fbr.FooId)
.Select(g => new Foo
{
FooId = g.Key,
FooName = g.First().Name,
Bars = g.First().BarId == null ? new List<Bar>() : g.Select(b => new Bar { BarId = b.BarId.Value, BarName = b.BarName }).ToList()
});
In EF it's just:
var foos = await db.Foos.Where(whatever).Include(f => f.Bars).ToListAsync();
Saving that clutter -- both the code and the otherwise useless intermediate FooBarRow type -- really adds up because the apps I write have a zillion queries very much like this.
So I'll take the occasional shitty query that has to be tracked down and optimized in exchange.
- tester756 3y agoReally really well said. It is weird that people jump into strange extremas when you can just combine best of both worlds.
- fendy3002 3y agoIt's sad that in programming world, something is either anti-pattern and you must not use it at all, or something is so awesome that you need to use it everywhere.
- deleted 3y ago[deleted]
- rjbwork 3y ago>I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying. I'm pretty sure Dapper lets you do this fairly easily. You've got a couple options - you can either provide a mapping function, or you can have your query/sprocs return multiple result sets. Then you can assemble the result sets into the object structure. It's not quite as magical as LINQ, but it's also not quite as annoying and fraught with so much marshalling code as your example.
- marklubi 3y agoWas waiting for Dapper to get brought up. Fantastic micro ORM.
- Atotalnoob 3y agoYou made a strong argument, up until you forced client side execution of the query by calling `ToListAsync()` https://learn.microsoft.com/en-us/ef/core/querying/client-eval?source=recommendations#explicit-client-evaluation https://learn.microsoft.com/en-us/ef/core/querying/client-ev...
- rspeele 3y agoWhen they say it forces client evaluation, what they mean is that any LINQ operations you run after the ToListAsync() will run on the client side - since it's just a normal list in memory at that point and no longer has any knowledge of the database. So if you want to make sure some code will execute client-side, you should ToList or AsEnumerable your query first, then do your additional client-side operations on that list. The IQueryable operations composed before to the ToListAsync() will run as SQL on the server. In the case of my example it will look pretty similar in structure to the string SQL I wrote before it: a straightforward left join with a where clause. Performance will be similar too. It will not load the entire universe of Foos and Bars and then filter them down on the client. One of the habits I have developed from working with EF for the past 10 years is to be explicit and as local as possible about forcing the query to evaluate. It lets me pretty reliably predict what the SQL is going to look like. You can return IQueryables from methods and pass them around through many layers of your program, adding complexity to them as you go, and lazily getting the results at the last possible moment, five layers up the call stack from where the query first started. It seems at first like that would be good, because that way the maximum amount of logic will run on the DB server, and letting the server do stuff is better, right? But that's also where you open yourself up to being very surprised about what your SQL looks like by the time it executes, with multiple layers of your program each tacking complexity onto the query. It can get ugly. Also you can have problems if you're hanging onto IQueryables that you still haven't executed after you've Dispose()d your DbContext. For this reason I try not to let IQueryables travel very far through my program before forcing evaluation.