This issue is related to the current application, which is undergoing evolutionary maintenance. Please allow me a few lines explaining how this works.
The application scans large data sets imported from a large text file using 182 SQL queries, which by design are stored in the database itself, are encrypted. The main disadvantage is that we support separate queries for SQL Server and MySQL due to differences in syntax.
Currently, the root user is allowed to view and edit SQL text. The requirement to look at SQL to understand how the query works for some specific cases (we are rarely asked to change) is a requirement.
To overcome the differences in the DBMS, I am trying to convert these queries to LINQ, which is a difficult task as such because of their complexity. But my question is different.
Given that I am creating an IQuery interface that runs a specific query from EF DbContext and scan parameterization, 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 will be with LINQ source code).
I've done my homework. I read about the possibility of creating an Expression tree, which is a great starting point. The problem is that creating the Expression<Func<?,bool>> GetWhereExpression() interface method, which will be used later to build the query, is not viable, because the queries are distributed across 6 main objects and some lookup tables. Let me say that clearly: my idea was to build IQueryable as follows:
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 is executed in different data set joins (some internal, some left joins, and not all 6 entities are combined into all queries).
The best I can do is create an IQueryable<NormalizedRow> DoQuery(DbContext dbContext,...) interface method IQueryable<NormalizedRow> DoQuery(DbContext dbContext,...) . Now, does anyone know how to pull an IQueryable object before starting it from here? I could use the same object to run a query and retrieve a dataset, or to display its "source".
Another option is to create the string Source{ get; } property string Source{ get; } string Source{ get; } , in which I copy and paste the source code of the C # request, which will work definitely. But we are creative developers, right?
The following is an example of an SQL query (formatting is courtesy of the Instant SQL Formatter ), with source column names and placeholders to replace (no need to change 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 a list (believe it or not, it will do the same):
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 => xAutente == aUserId && //Flusso utente new string[] { "0", "1", String.Empty }.Contains(xAA42) && // A42 IN (0,1,'') inner.Contains(xDD21) && //D.D21 IN (SELECT CODICE FROM SOTTOGRUPPPI.A..A. WHERE FLAGPF = 1) (xDD11 != null && xDD11 != String.Empty) && //D11 IS NOT NULL AND D11 <> '' new string[] { "1", "2" }.Contains(xDD45) && //D45 IN ('1','2') ( (xDD18.CompareTo("19000101") >= 0 && xDD18.CompareTo(xAA21) <= 0) && //D18 >= '1900101' AND D18 <= A21 (xDD43.CompareTo("19000101") >= 0 && xDD43.CompareTo(xDD18) >= 0) // D43 >= '19000101' AND D43 >= D18 ) && xDD43.CompareTo(sysdate10String) < 0 // D43 <= (SYSDATE() - 10 YEARS) ); if (dataInizioControllo != null) { string dataInzio = dataInizioControllo.Value.ToString("yyyyMMdd"); q = q.Where(x => xAA21.CompareTo(dataInzio) >= 0); } if (dataFineControllo != null) { string dataFine = dataFineControllo.Value.ToString("yyyyMMdd"); q = q.Where(x => xAA21.CompareTo(dataFine) <= 0); } if (a52Exclude != null) q = q.Where(x => !a52Exclude.Contains(xAA52)); q = q .OrderBy(x => xAA11) .OrderBy(x => xDD11); totalCount = q.Count(); return q.Take(Parameters.ROW_LIMIT).Select(j => new QueryRow { A01 = jAA01, A01a = jAA01a, A01b = jAA01b, A02 = jAA02, A03 = jAA03, A11 = jAA11, A12 = jAA12, A12a = jAA12a, A12b = jAA12b, A12c = jAA12c, ....... redacted for brevity D43 = jDD43, D44 = jDD44, D45 = jDD45, Z1 = jDZ1 }).ToList(); }
I have an almost identical query connecting SectionA and SectionE, e.g.
Wrapper
I want, from IQueryable<> , to create an output text that is at least readable and contains at least, if not all, the following sentences
(x)=> new string[] { "0", "1", String.Empty }.Contains(xAA42) && inner.Contains(xDD21) && (xDD11 != null && xDD11 != String.Empty) && new string[] { "1", "2" }.Contains(xDD45) && ( (xDD18.CompareTo("19000101") >= 0 && xDD18.CompareTo(xAA21) <= 0) && //D18 >= '1900101' AND D18 <= A21 (xDD43.CompareTo("19000101") >= 0 && xDD43.CompareTo(xDD18) >= 0) // D43 >= '19000101' ) && xDD43.CompareTo("20050623") < 0
How can I link IQueryable in LINQ to Entities?