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?

I usually use a string join. You can make the output of the linq a ToArray() method. So this usually works : string.Join(",", ......ToArray()). I used a comma to make the output CSV but you can use any character even a space in the join. — jdweng
– jdweng, Commented Jun 23, 2015 at 8:41
Can't you simply save the ToString() of the IQueryable<>? Entity Framework shows in the ToString() the query it will execute. — xanatos
– xanatos, Commented Jun 23, 2015 at 8:57
@jdweng are you sure that ToArray() doesn't return the result set into an array? That is not what I want — usr-local-ΕΨΗΕΛΩΝ
– usr-local-ΕΨΗΕΛΩΝ, Commented Jun 23, 2015 at 8:58
@xanatos crap! I didn't know that ToString() returns the exact query with marked parameters and altered table names. It should be a candidate answer — usr-local-ΕΨΗΕΛΩΝ
– usr-local-ΕΨΗΕΛΩΝ, Commented Jun 23, 2015 at 9:09

Community · Accepted Answer · 2020-06-20 09:12:55Z

8

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 23, 2015 at 9:14

usr-local-ΕΨΗΕΛΩΝ

27.1k36 gold badges173 silver badges330 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xanatos Over a year ago

table names are almost all redacted I don't have this problem on some test queries I've written... The only problem I have with .ToString() is that the query generated isn't good for "human use"... It is often quite complex, often based on a SELECT * FROM SELECT * FROM SELECT * FROM ... UNION SELECT * FROM, so multilevel with various UNION sprinkled around

usr-local-ΕΨΗΕΛΩΝ Over a year ago

@xanatos I have fixed my answer. I have used the wrong wording I believe

xanatos Over a year ago

No, that is an alias... EF gives alias to all the table names... You go to the FROM and you'll see that it is FROM [dbo].[SomeTable] AS [Extent1]

Alex Over a year ago

Yeah EF tends to use aliases everywhere - you could do some regex parsing and apply some find-and-replace to simplify things

Collectives™ on Stack Overflow

Getting "source" of LINQ query and serialize to string

Wrapping it up

1 Answer 1

Option 1

Option 2

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Wrapping it up

1 Answer 1

Option 1

Option 2

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related