Hi I have an oracle query

select * from 
( select id, name || ' ' || sure_name as "fullname" from xf_firms_uzivs) 
where "fullname" like 'test t%';

I'd like to create this query using the queryspec api.

The problem I have is I don't know how to create the where statement as the fullname column is dynamic.

Did you try projection?

Please check the following doc links: Projections Typed Dynamic Queries

My two cents: I have an example of this already:

var qf = new QueryFactory();
var adapter = new DataAccessAdapter();

// inner query
var q1 = qf.Create("q1")
            .Select(() => new
            {
                EmployeeId = EmployeeFields.EmployeeId.ToValue<int>(),
                Name = StringFunctions.Concat(EmployeeFields.FirstName, EmployeeFields.LastName).As("FullName").ToValue<string>()
            });

// ourter query
var q2 = qf.Create()
                .SelectFrom(q1)
                .Where(qf.Field(q1.Alias, "FullName").Like("A b%"));
            
// fetch
var results = adapter.FetchQuery(q2); 

Generated SQL:

SELECT "LPA_q1"."EmployeeId",
       "LPA_q1"."FullName"
FROM   (SELECT "HR"."EMPLOYEES"."EMPLOYEE_ID"   AS "EmployeeId",
               ("HR"."EMPLOYEES"."FIRST_NAME"
                || "HR"."EMPLOYEES"."LAST_NAME") AS "FullName"
        FROM   "HR"."EMPLOYEES") "LPA_q1"
WHERE  (((("LPA_q1"."FullName" LIKE :p1)))) 

Thank you very much, the example helped a lot.