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.