11 ms·
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 a
by Pxtl 2y ago
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
- mythz 2y agoGuess 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/ https://docs.servicestack.net/ormlite/
- Pxtl 2y agoMy 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.
- mythz 2y agoIsn'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.*'"));