Net 9.0 LINQ Performance Improvements

Re:Net 9.0 LINQ Performance Improvements by mythz on Friday October 18, 2024 @10:15AM (#41879691)

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/

[ Parent | on HN ]

Re:Net 9.0 LINQ Performance Improvements by Pxtl on Friday October 18, 2024 @10:39AM (#41879863)

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.

[ Parent | on HN ]

Re:Net 9.0 LINQ Performance Improvements by mythz on Friday October 18, 2024 @10:49AM (#41879948)

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.*'"));

[ Parent | on HN ]

Re:Net 9.0 LINQ Performance Improvements by Pxtl on Friday October 18, 2024 @03:08PM (#41882485)

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.

[ Parent | on HN ]