Express SQL Query As Abstract Object Model

Question

Goal: write code to express SQL queries in C# that get converted to SQL at the data layer level and will be compatible with any common data layer / ORM such as Dapper or Entity Framework (EF).

Edit:

And, ability to dynamically build queries at runtime

Justification: decouple the querying aspect of the data infrastructure away from the data layer itself. Query code would not need to be rewritten another data layer is swapped in.

Here is some example EF query code:

var query = context.Students
                   .where(s => s.StudentName == "Bill")
                   .FirstOrDefault<Student>();

Here is some example Dapper code:

var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();

Here is some example RepoDb code:

var person = connection.Query<Person>(e => e.FirstName == "John" && e.LastName == "Doe").FirstOrDefault()

EF and RepoDb use expressions which are good, but they are not necessarily compatible with each other. The code might be similar, but it wouldn't be trivial to replace one with the other.

I have implemented something similar to this in the past, and it worked very well across three database platforms. There was no need for raw SQL:

var selectStatement = new SelectStatement
{
    TableName = "OrderLine",
    Joins =
    {
        new Join
        {
            JoinType =  JoinType.Inner,
            On = 
            {
                new ComparisonPredicate
                {
                    Colname = "OrderId",
                    RelationalOperator =  RelationalOperator.Equal,
                    Value = "Order.Id"
                }
            }
        }
    },
    Where =
    {
        new ComparisonPredicate 
        { 
            Colname = "Order.Id",
            RelationalOperator =  RelationalOperator.Equal,
            Value = new Guid("c16a0181-f984-48b2-949e-4ce96a323ea8")
        }
    }
};

var orderLines = dataLayerAbstraction.GetAsync<OrderLine>(selectStatement);

You might ask wouldn't IQueryable do the trick?

Perhaps...

IQueryable aims at achieving exactly what I have mentioned here. It is a strongly typed way of expressing a query in C#. However, my real question and point of discussion here is: is IQueryable expressive enough for 99% of SQL queries on any platform in an efficient way? Here is some context. Some explicitly recommend against abstracting queries in favor of writing a method for each possible query. Also, implementing IQueryable is very onerous and if you were to try it, you'd need to implement your own version for each data layer that doesn't already implement it.

I often see code that drops out to raw SQL because expressions are either not suitable to express the query or create bad SQL that is not efficient.

I can say from experience that a similar object model I've pointed out here allows fine-tuning of the query and is independent of the database platform. I never ran into queries that I couldn't express this way, and I was always able to optimize the query to get the best out of the database platforms.

Is there a library out there that takes this approach? Has anyone else achieved some form of decoupling of querying from the data layer?

Note: GraphQL is probably the most notable successful implementation of decoupling the querying of data from the physical data layer. For example GraphQL.EntityFramework maps GraphQL queries to Entity Framework. this approach in the Dapper extensions is also notable.

PS: I'd also be keen to hear if anyone has ever successfully implemented a library which parses SQL to an object model, and then re-renders the SQL for the specific platform.

Answer 1

Language Integrated Query (LINQ)

What is a query and what does it do?

A query is a set of instructions that describes what data to retrieve from a given data source (or sources) and what shape and organization the returned data should have. A query is distinct from the results that it produces.

LINQ is the answer to my question. LINQ allows developers to express queries as an abstract data model. It does

decouple the querying aspect of the data infrastructure away from the data layer itself

In the OP, I asked the question:

Is IQueryable expressive enough for 99% of SQL queries on any platform in an efficient way?

The answer is partially yes. Entity Framework is proof that it mostly works, and other ORMs implement functionality for rendering other types of LINQ expressions to SQL and other query types. There may be some cases where dropping out to SQL is necessary for some reason or another, but these are the exceptions and not the rule.

My Repo DB example above is not compatible with IQueryable as for as I can tell, but the Query method is compatible with the same LINQ expression signature as EF's Where method. The real point is that the LINQ expressions are used by both platforms.

Dynamically Building Queries at Runtime

LINQ supports this. The other goal that was not explicitly stated in the OP, although added it to the OP. It is often necessary to dynamically build query objects. This is no problem for LINQ:

How to use expression trees to build dynamic queries (C#)

Dynamic queries are useful when the specifics of a query are not known at compile time. For example, an application might provide a user interface that enables the end-user to specify one or more predicates to filter the data. In order to use LINQ for querying, this kind of application must use expression trees to create the LINQ query at runtime.

My code example above can easily be expressed as a LINQ expression and can also be easily built at runtime. The code sample is based on code from 2008 when LINQ was only new. It works. If you didn't want to use LINQ, you could take this approach, but LINQ basically makes this approach redundant.

Note on Dapper: Dapper doesn't directly implement IQueryable, but there is no reason why someone couldn't write a provider that renders LINQ queries to SQL that works for the Dapper micro ORM.