Net 9.0 LINQ Performance Improvements

Having used it since its inception, I've come to the conclusion that the SQL translator is kind of a misfeature. It creates so many weird bugs and edge-cases and tedium.

I love LINQ, I love having a typesafe ORM as a standard feature of C#, but the convenience of being able to reuse my Pocos and some expressions for both in-memory and in-SQL don't outweigh the downsides.

If I were designing SQL/LINQ today, I'd keep the in-memory record classes and in-database record classes distinct and use some kind of codegen/automapping framework for keeping them synched up. Maybe allow predicate operators to return things other than booleans so we could make `a == b` return some kind of expression tree node.

For ad-hoc queries using anonymous classes? Support defining an interface inline in a generic so you can say

    public T MyQuery<interface {string Firstname{get;set;}; string Lastname{get;set:}} T>();

Like, to elaborate, if you were doing some kind of JSON-based codegen (alternately you could do something where you have a separate hand-written POCO Model assembly and use reflection against it to generate your DbModel classes so it's still Code First). Yes, I know MS tried and abandoned this approach, I used LinqToSQL and EF3.5 and whatnot and suffered all that pain.

like, your master datatable file would be something like

    ```json
    "tables" : [
      "persons" : {
        "dataRecordClass" : "DataRecordsNamespace.DbPerson",
        "objectClass" : "PocosNamespace.Person"
      },
      "columns : {
        "PKID" : {
          "type" = "integer",
          "isPrimaryKey" = true,
          "isAutoGenerated" = true,
        }
        "firstName" : {
          "type" : "nvarchar(255)",
          "allowNull" : true,
        }
        "lastName" : {
          "type" : "nvarchar(255)"
          "allowNull" : false
        }
      }
    ]
    ```

which would generates something like

    ```cs
    public class DataRecordsNamespace.DbPerson : DbRecord {
      public DbPerson() { throw ThisIsAFakeClassException(); }
      public DbInt PKID{
        get => throw ThisIsAFakeClassException();
        set => throw ThisIsAFakeClassException();
      }
      public DbNVarChar {
        get => throw ThisIsAFakeClassException();
        set => throw ThisIsAFakeClassException();
      }
    }

    public partial class PocosNamespace.Person {
      public AutoGenerated<int> PKID{ get; init; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
    }

    public class MyDbModel : DbModel {
      public DbTable<DbPerson> Persons => DoSomeLazyStuff();
    }

    public static class MyDbContextExtensions {
      public static List<Person> Resolve(this DbQuery<DbPerson> dbPersons) 
      {
        //call code to execute the actual query.
      }
    }
    ```

Am I making sense? Then you wouldn't have the problem of "oops I used an untranslateable method or member of Person", because MyDbModel can't have any of those. You'd lose the ability to to switch from whether a query is in-memory or in-database just by removing the ToList(), but I'd argue that's a misfeature, and better-handled by having some kind of InMemory implementation. Like, having DbQuery have a simple `.ToLocalMemory()` function that is a hint that the next part should be done locally instead of in the database would be a better way to do that. Then you could still do

    ```cs
    List<Person> myPersons = connection.MyDbModel
      .Persons
      .DoSomeInDatabaseQueryStuff()
      .ToLocalMemory()
      .DoSomeLocalMemoryStuffToOffloadItFromDatabase()
      .Resolve()
      .DoSomeDotNetStuff()
      .ToList();
    ```

edits: fix some of the HN pseudomarkdown

Guess everyone has their preferred style, I personally avoid code-gen data models like the plague and much prefer code-first libraries.

Here's how you'd do something similar in our OrmLite ORM [1]:

    public class Person
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string? FirstName { get; set; }
        [Required]
        public string LastName { get; set; }
    }

Create Table:

    var db = dbFactory.Open(); // Resolve ADO.NET IDbConnection
    db.CreateTable<Person>();  // Create RDBMS Table from POCO definition

Execute Query:

    // Performs SQL Query on Server that's returned in a List<Person>
    var results = db.Select<Person>(x => x.FirstName.StartsWith("A") && x.LastName == "B");

    // Use LINQ to further transform an In Memory collection
    var to = results.Where(MemoryFilter).OrderBy(MemorySort).ToList();

Everything works off the POCO, no other external tools, manual configuration mapping, or code gen needed.

[1] https://docs.servicestack.net/ormlite/

My problem with this approach is that this falls apart if you write:

    db.Select<Person>(x => Regex.IsMatch(x.FirstName, "^A.*"));

This would fail at run-time instead of compile-time.

That's why I'd rather see the DB classes auto-generated with a mapper to convert them. Having the "master" be POCOs instead of JSON/XML/YAML/whatever isn't something I'm convinced on in either direction, but imho the in-database classes being not real POCOs is the important part because it reduces the the problem of somebody writing Person.MyMethod() and then blowing up because it's not a SQL function.

Isn't this just `.StartsWith("A")`?

How would you perform this regex query with your code generated solution? What would have to be code generated and what would the developer have to write?

As there's a lot more features available in different RDBMS's than what's available in C# expression syntax, you can use SQL Fragments whenever you need to:

    var results = db.Select(db.From<Person>()
        .Where(x => x.LastName == "B")
        .And("FirstName ~ '^A.*'"));

Saw EF now supports custom SQL queries, so been considering that once we've moved to MSSQL (old db server isn't supported by EF).

We're quite accustomed to writing our own SQL select statements and would like to continue doing that to have known performance, but the update, insert and delete statements are a chore to do manually, especially for once you're 4-5 parent child levels deep.

Yes, it's a trivial example. I'm not looking to support it, I'm looking to catch it at compile-time.

if "Person.FirstName" is a string, then that encourages users to use string-operations against it, which will fail if this expression is being translated to SQL for executing in the DB.

if "Person.FirstName" is some other type with no meaningful operations supported on it (which will get converted into a string when the query is executed) then it prevents many many classes of logic errors.

Quick word of advice when dealing with deep parent/child reloationships:

Do not use lazy loading feature. That way lies madness.

We're not doing that where we come from. All child tables have the main id so we can load the data for all child rows with just one query per child table, and we load everything at once.

We were planning on sticking with this, it has worked well so far, but good to know to avoid getting tempted by the alternative.