-4

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.

  • 1
    "implemented a library which parses SQL to an object model" - the question is what purpose this is designed to serve? Mainstream OO languages lack the power in their type system to statically reason about an SQL query, and platforms don't just differ in syntax but also in actual functionality. And what does "decoupling of querying from the data layer" even mean? The relational model already logically decouples querying from the physical storage - the "data layer" is the query function, so what would it mean to decouple querying from the layer whose very purpose is querying? – Steve Jul 19 at 1:38
  • 2
    @ChristianFindlay This rather sounds like a solution looking for a problem to me in that case. Why would you want to switch EF to another ORM if doing so didn't give you any different capabilities? EF already decouples querying from the data layer, allows easy unit testing and works against a dozen different database providers. Keep in mind the drawbacks of the new abstraction only being able to use a subset of EF's capabilities, so fewer opportunities to optimise a query, added maintenance cost of an extra layer of complexity, and seemingly a more verbose and less intuitive syntax too. – Ben Cottrell Jul 19 at 23:09
  • 1
    @ChristianFindlay I'm asking to try to clarify the problem you're trying to solve. As per the help centre for this site: softwareengineering.stackexchange.com/help "You should only ask practical, answerable questions based on actual problems that you face" -- I may be wrong, but it rather sounds to me as if what you're asking isn't based on an actual problem. Otherwise, if it is based on an actual problem, then it seems that EF already solves it? – Ben Cottrell Jul 20 at 14:05
  • 2
    @ChristianFindlay, the problem is you have to be prepared to accept that people have explored this, and as a result they consider it "a bad idea that should not be entertained". As I'd already pointed out, normal OO languages do not have sufficiently powerful type systems to model an SQL query in terms of objects (your stated "goal"). The best can do (as you show) is model the structure of the SQL source code, which just increases the overall length of code necessary to express a query, and is (for that reason and others) a practically worthless activity. (1/2) – Steve Jul 20 at 15:33
  • 2
    A number of us now, as well as addressing your question as it is, have asked further questions designed to identify whether we are misunderstanding something or looking at your question in the wrong way, and you mostly respond in a shirty tone. If you already know this is a brilliant and totally workable idea, then why are you asking for others' opinions? (2/2) – Steve Jul 20 at 15:49
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.