4 ms·

Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness

Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness of C# to get things done. Whats needed is a really good object database for C# to eliminate the impedance mismatch. Don't get me wrong SQL is great, but to be useful it needs to be mixed with a procedural language (pl/SQL, T-SQL, etc) I would just prefer using a more capable typed procedural language instead.

philliphaydon 7y ago
MartenDB. :)
- SigmundA 7y ago
  Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accepts code snippets, compiles runs and returns results. Of course you would need proper sandboxing like .net fiddle. You could also do something in between like serializing Linq expression trees to send them to server for processing. In an ideal world where your code runs (client, app server, db server) is a choice based on locality needs not forced by runtimes (javascript in the browser, .Net on the app server, pg/Sql on the db server). You can pretty much get close to this now with javascript/node/plV8, but again I prefer C#/.Net.
  - philliphaydon 7y ago
    So... Marten DB...
    - SigmundA 7y ago
      As far I know Marten doesn't run .Net in the PG process and just converts .Net objects to JSON and Linq statements into SQL. Am I missing something?
      - philliphaydon 7y ago
        Well you don’t need to worry about creation, and migrations. You only need to tell the store which objects it needs to know about. You declare everything in c#. It’s stores it as jsonb. So you don’t need to worry about weird relationships you only worry about your root aggregate. You can index it. Apply full text search. And persisted columns in the map if for convince or speed. So Yeah it maps the linq to the equiv sql for you. But allows you to work with your object and worry less about persistence. Cos you can add and remove properties. It’s the closest thing I’ve come to, to being able to forget about the database and focus on code. I Guess unless you write your own database from the ground up so it supports the language as a feature without dropping back to something else. It won’t happen.
        SigmundA 7y ago
        Again that's great, its an ORM, that's what they do, try to abstract the DB from your language, and do their best to hide the impedance mismatch. However the mismatch is always there, the overhead of mapping types, inefficient serialization (key names repeated over and over in json vs a real schema), certain statements cannot be transpiled to sql so you have pathologic cases at unexpected times, in ability to run any statement you want in the db tier to take advantage of locality and so on. Most everyone wants a object database since most people are working with objects which is why ORM are so popular. Closet thing I ever saw was db4o.
        philliphaydon 7y ago
        It’s jsonb so you can’t repeat key names. Serialization is what ever you want to use for json serialization. Other than some report type queries I wanted to write I haven’t found anything that I couldn’t do in linq.
  - ilkhan4 7y ago
    I'm with you on this one. I was thinking it would be cool to "bake in" the DB layer of an application and have it run in-process. Then you'd just provide it with block storage and it would handle the rest without having to have a separate server. You could even do compile-time migration and index generation based on your queries. With a distributed actor framework like Akka.NET or Orleans, it could probably be made to scale too. The only downside is that the only interface to your data is through the application, but there's certainly a use-case for something like that.
  - ako 7y ago
    This is basically what most rdbmses have been offering for the last 20 years. Oracle rdbms is in fact an application server, and best practice is to have an app layer built using plsql package through which you access all the data. More than 20 years ago I actually used it as a true application server, where the database application layer would even generate all the HTML code required to displayed your app ui. And with database schemas you have proper sandboxing.
zapov 7y ago
Postgres is object-relational database, so it can certainly be used in such a way (and I use it like that most of the time). But this is quite a niche usage of PG and there are little to zero tools supporting it.
- ken 7y ago
  There's a huge list of caveats in Postgres's inheritance documentation [1], and even the obvious issues haven't been touched in decades. I've never heard of anyone using this feature. At this point, I would say Postgres itself is a tool that doesn't support Postgres inheritance. [1]: https://www.postgresql.org/docs/current/ddl-inherit.html https://www.postgresql.org/docs/current/ddl-inherit.html
- SigmundA 7y ago
  Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.
  - zapov 7y ago
    The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that. I personally do not find useful to execute .NET code in PG, but technically you could do that. As long as you can translate LINQ expressions to PG as you can most of them with Revenj you can avoid the pitfalls of mainstream ORMs.
    - SigmundA 7y ago
      Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key. The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use. The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.
viblo 7y ago
You might want to take a look at starcounter. https://starcounter.io/ https://starcounter.io/ I haven't used it myself, but saw a demo in their office a couple of years ago.

philliphaydon 7y ago

MartenDB. :)

SigmundA 7y ago
Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accepts code snippets, compiles runs and returns results. Of course you would need proper sandboxing like .net fiddle. You could also do something in between like serializing Linq expression trees to send them to server for processing. In an ideal world where your code runs (client, app server, db server) is a choice based on locality needs not forced by runtimes (javascript in the browser, .Net on the app server, pg/Sql on the db server). You can pretty much get close to this now with javascript/node/plV8, but again I prefer C#/.Net.
- philliphaydon 7y ago
  So... Marten DB...
  - SigmundA 7y ago
    As far I know Marten doesn't run .Net in the PG process and just converts .Net objects to JSON and Linq statements into SQL. Am I missing something?
    - philliphaydon 7y ago
      Well you don’t need to worry about creation, and migrations. You only need to tell the store which objects it needs to know about. You declare everything in c#. It’s stores it as jsonb. So you don’t need to worry about weird relationships you only worry about your root aggregate. You can index it. Apply full text search. And persisted columns in the map if for convince or speed. So Yeah it maps the linq to the equiv sql for you. But allows you to work with your object and worry less about persistence. Cos you can add and remove properties. It’s the closest thing I’ve come to, to being able to forget about the database and focus on code. I Guess unless you write your own database from the ground up so it supports the language as a feature without dropping back to something else. It won’t happen.
      - SigmundA 7y ago
        Again that's great, its an ORM, that's what they do, try to abstract the DB from your language, and do their best to hide the impedance mismatch. However the mismatch is always there, the overhead of mapping types, inefficient serialization (key names repeated over and over in json vs a real schema), certain statements cannot be transpiled to sql so you have pathologic cases at unexpected times, in ability to run any statement you want in the db tier to take advantage of locality and so on. Most everyone wants a object database since most people are working with objects which is why ORM are so popular. Closet thing I ever saw was db4o.
        philliphaydon 7y ago
        It’s jsonb so you can’t repeat key names. Serialization is what ever you want to use for json serialization. Other than some report type queries I wanted to write I haven’t found anything that I couldn’t do in linq.
- ilkhan4 7y ago
  I'm with you on this one. I was thinking it would be cool to "bake in" the DB layer of an application and have it run in-process. Then you'd just provide it with block storage and it would handle the rest without having to have a separate server. You could even do compile-time migration and index generation based on your queries. With a distributed actor framework like Akka.NET or Orleans, it could probably be made to scale too. The only downside is that the only interface to your data is through the application, but there's certainly a use-case for something like that.
- ako 7y ago
  This is basically what most rdbmses have been offering for the last 20 years. Oracle rdbms is in fact an application server, and best practice is to have an app layer built using plsql package through which you access all the data. More than 20 years ago I actually used it as a true application server, where the database application layer would even generate all the HTML code required to displayed your app ui. And with database schemas you have proper sandboxing.

zapov 7y ago

Postgres is object-relational database, so it can certainly be used in such a way (and I use it like that most of the time). But this is quite a niche usage of PG and there are little to zero tools supporting it.

ken 7y ago
There's a huge list of caveats in Postgres's inheritance documentation [1], and even the obvious issues haven't been touched in decades. I've never heard of anyone using this feature. At this point, I would say Postgres itself is a tool that doesn't support Postgres inheritance. [1]: https://www.postgresql.org/docs/current/ddl-inherit.html https://www.postgresql.org/docs/current/ddl-inherit.html
SigmundA 7y ago
Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.
- zapov 7y ago
  The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that. I personally do not find useful to execute .NET code in PG, but technically you could do that. As long as you can translate LINQ expressions to PG as you can most of them with Revenj you can avoid the pitfalls of mainstream ORMs.
  - SigmundA 7y ago
    Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key. The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use. The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.

viblo 7y ago

You might want to take a look at starcounter. https://starcounter.io/ https://starcounter.io/ I haven't used it myself, but saw a demo in their office a couple of years ago.