Getting "source" of LINQ query and serialize to string

Question

This question is related to a currently live application that is undergoing evolutionary maintenance. Please allow me to spend a few lines explaining how it works.

The application scans large datasets imported from a big text file using 182 SQL queries that, by design, are stored in the database itself, encrypted. The main disadvantage is that we are maintaining separate queries for SQL Server and MySQL due to their syntax differences.

Currently the root user is allowed to view and edit the SQL text. Viewing the SQL in order to understand how the query works for some particular cases (we are rarely asked to modify them) is a requirement.

In order to overcome the DBMS differences I am trying to convert those queries to LINQ, which is a difficult task per se due to their complexity. But my question is another.

Given that I am creating an IQuery interface that runs the specific query from the EF DbContext and scan parametrization, I still want to keep a readable, and possibly formatted, string representation of the predicate to show the root user (otherwise our functional analyst will have to expect my mail with LINQ source code).

I have done some homework. I have read about the possibility to stringify an Expression tree, which is a great starting point. The problem is that creating an interface method Expression<Func<?,bool>> GetWhereExpression(), to be used later to build the query, is not viable because queries span across 6 main entities and some lookup tables. Let me say that clearly: my idea was to build the IQueryable like this:

IQuery theQueryToRun;
var query = from SectionA a in dataContext.sectionA
            join SectionD b in dataContext.sectionB on a.A03 equals b.A03
            .... join ....

query = query.Where(theQueryToRun.GetWhereClause(...));
query = query.OrderBy(theQueryToRun.GetOrderByClause());

return query.ToList();

But this approach will never work because each query runs on different joins of the dataset (some inner, some left join, not all 6 entities are joined in all queries).

The best I can do is to create an interface method IQueryable<NormalizedRow> DoQuery(DbContext dbContext,...). Now, from here, does anyone know a way to stringify the IQueryable object before running it? I could use the same object to either run query and get dataset or to display its "source".

Another option is to create a string Source{ get; } property in which I copy&paste the C# source code of the query, which will work definitely. But we are creative developers, aren't we?

Following is an example of SQL query (formatting courtesy of Instant SQL Formatter), with original column names and placeholders to replace (no need to redact anything):

SELECT a.a01,a.a01a,a.a01b,a.a02,a.a03,a11,a12,a12a,a12b,a12c,a21,a22,a23,a24,
       a25,a31,
       a31a,a31b,a32,a33,a33a,a33b,a33c,a34,a41,a42,a43,a51,a52,a53,a54,a54a,
       a54b,a54c,b11,b12,b13,b14,b15,z0,a.prog,a.utente,c11,d11,d13,d14,d14a,
       d14b,d14c,d15,d16,d17,d18,d19,d21,d22,d23,d31,d32,d41,d42,d43,d44,d45,z1
FROM   sezione_a a
       INNER JOIN sezione_d d
               ON a.a03 = d.a03
                  AND a.utente = d.utente
WHERE  ( ( a.a42 IN ( '0', '1', '' )
           AND d.d21 IN(SELECT codice
                        FROM   sottogruppi
                        WHERE  flagpf = 1) )
         AND ( d.d11 IS NOT NULL
               AND d.d11 <> '' )
         AND d.d45 IN( '1', '2' )
         AND ( ( d.d18 >= '19000101'
                 AND d.d18 <= a.a21 )
               AND ( d.d43 >= '19000101'
                     AND d.d43 <= a.a21 )
               AND d.d43 >= d.d18 )
         AND ( Date_add(Str_to_date(d.d43, '%Y%m%d'), INTERVAL 10 year) >
                     Str_to_date(a.a21, '%Y%m%d')
               AND Date_add(Str_to_date(d.d43, '%Y%m%d'), INTERVAL 10 year) <
                   Now() ) )
   AND ( ( a.a21 BETWEEN '@@datamin' AND '@@datamax' )
         AND a.utente = @@user
         AND a.a52 NOT IN @@a52 )
ORDER  BY a.a11 ASC,d.d11 ASC 

C# code that returns list (believe it or not, it will do the same thing):

    public IList<QueryRow> Run(Models.auitool2014Entities dataContext, int aUserId, DateTime? dataInizioControllo, DateTime? dataFineControllo, string[] a52Exclude, bool codifiche2014, out long totalCount)
    {
        string sysdate10String = DateTime.Now.AddYears(-10).ToString("yyyyMMdd", CultureInfo.InvariantCulture);

        var inner = codifiche2014 ?
            from Sottogruppo sg in dataContext.sottogruppi where sg.flagpf select sg.codice :
            from Sottogruppo2015 sg in dataContext.sottogruppi2015 where sg.flagpf select sg.codice;

        var q = dataContext.sezione_a.Join(
                    dataContext.sezione_d,
                    a => new { A03 = a.A03, User = a.utente },
                    d => new { A03 = d.A03, User = d.utente },
                    (a, d) => new SezioneJoin { A = a, D = d }
                )
            .Where(x =>
                x.A.utente == aUserId && //Flusso utente
                new string[] { "0", "1", String.Empty }.Contains(x.A.A42) && // A42 IN (0,1,'')
                inner.Contains(x.D.D21) && //D.D21 IN (SELECT CODICE FROM SOTTOGRUPPPI.A..A. WHERE FLAGPF = 1)
                (x.D.D11 != null && x.D.D11 != String.Empty) && //D11 IS NOT NULL AND D11 <> ''
                new string[] { "1", "2" }.Contains(x.D.D45) && //D45 IN ('1','2')
                (
                    (x.D.D18.CompareTo("19000101") >= 0 && x.D.D18.CompareTo(x.A.A21) <= 0) && //D18 >= '1900101' AND D18 <= A21
                    (x.D.D43.CompareTo("19000101") >= 0 && x.D.D43.CompareTo(x.D.D18) >= 0) // D43 >= '19000101' AND D43 >= D18
                ) &&
                x.D.D43.CompareTo(sysdate10String) < 0 // D43 <= (SYSDATE() - 10 YEARS)
            );

        if (dataInizioControllo != null)
        {
            string dataInzio = dataInizioControllo.Value.ToString("yyyyMMdd");
            q = q.Where(x => x.A.A21.CompareTo(dataInzio) >= 0);
        }
        if (dataFineControllo != null)
        {
            string dataFine = dataFineControllo.Value.ToString("yyyyMMdd");
            q = q.Where(x => x.A.A21.CompareTo(dataFine) <= 0);
        }
        if (a52Exclude != null)
            q = q.Where(x => !a52Exclude.Contains(x.A.A52));

        q = q
            .OrderBy(x => x.A.A11)
            .OrderBy(x => x.D.D11);

        totalCount = q.Count();

        return q.Take(Parameters.ROW_LIMIT).Select(j => new QueryRow
        {
            A01 = j.A.A01,
            A01a = j.A.A01a,
            A01b = j.A.A01b,
            A02 = j.A.A02,
            A03 = j.A.A03,
            A11 = j.A.A11,
            A12 = j.A.A12,
            A12a = j.A.A12a,
            A12b = j.A.A12b,
            A12c = j.A.A12c,
            ....... redacted for brevity
            D43 = j.D.D43,
            D44 = j.D.D44,
            D45 = j.D.D45,
            Z1 = j.D.Z1
        }).ToList();

    }

I have an almost identical query joining SectionA and SectionE, for example

Wrapping it up

I want, from an IQueryable<>, to produce an output text that is at least readable and contains at least, if not all, the following clauses

         (x)=>
                new string[] { "0", "1", String.Empty }.Contains(x.A.A42) &&
                inner.Contains(x.D.D21) &&
                (x.D.D11 != null && x.D.D11 != String.Empty) &&
                new string[] { "1", "2" }.Contains(x.D.D45) &&
                (
                    (x.D.D18.CompareTo("19000101") >= 0 && x.D.D18.CompareTo(x.A.A21) <= 0) && //D18 >= '1900101' AND D18 <= A21
                    (x.D.D43.CompareTo("19000101") >= 0 && x.D.D43.CompareTo(x.D.D18) >= 0) // D43 >= '19000101'
                ) &&
                x.D.D43.CompareTo("20050623") < 0

How can an IQueryable in LINQ to Entities be stringified?

Answer 1

Found the answer myself. Trivial once I found it.

Option 1

Credit to @xanatos

Use IQueryable.ToString() method in LINQ to Entities to get the exact SQL query, already formatted.

• Pro: it is the exact query that will be launched
• Con: the query is less readable due to the use of several aliases or combinations of nested SELECTs

Option 2

Get the query expression using IQueryable.Expression property. Then the topic of how to stringify an Expression<Func<?,?>> is widely discussed in Stackoverflow

• Pro: if you use a good formatter you get better readability
• Con: it may differ from the exact SQL query run on dataset