static void Main(string[] args) { DevExpress.Xpo.Logger.LogManager.SetTransport(new ConsoleLogger()); XpoDefault.ConnectionString = MSSqlConnectionProviderWithCte.GetConnectionString(@"(local)", "CTE_Test"); //fill structure and data using (UnitOfWork uow = new UnitOfWork()) { uow.ClearDatabase(); new BaseTable(uow) { Str = "Aa" }; new BaseTable(uow) { Str = "bB" }; new BaseTable(uow) { Str = "CCccc" }; uow.CommitChanges(); } //working with cte using (UnitOfWork uow = new UnitOfWork()) { uow.RegisterCte("CteStructure_1", "(ID, String, Math) AS (select OID, Str, sin(OID) from BaseTable)"); try { Console.WriteLine("=== Count for sine CTE ==="); Console.WriteLine("Count: {0}", uow.Evaluate <CteStructure_1>(new AggregateOperand(null, null, Aggregate.Count, null), null)); Console.WriteLine("=== Filtered count for sine CTE ==="); Console.WriteLine("Count: {0}", uow.Evaluate <CteStructure_1>(new AggregateOperand(null, null, Aggregate.Count, null), new OperandProperty("ID") > 1)); Console.WriteLine("=== Select all with sine CTE ==="); foreach (var cteS in new XPCollection <CteStructure_1>(uow)) { Console.WriteLine(cteS.ToString()); } } finally { uow.UnregisterCte("CteStructure_1"); } } using (UnitOfWork uow = new UnitOfWork()) { uow.RegisterCte("CteStructure_1", "AS (select OID as ID, 'MyString: ' + coalesce(Str, '') as String, cos(OID) as Math from BaseTable where len(Str) < 5)"); try { Console.WriteLine("=== Count for filtered CTE ==="); Console.WriteLine("Count: {0}", uow.Evaluate <CteStructure_1>(new AggregateOperand(null, null, Aggregate.Count, null), null)); Console.WriteLine("=== Filtered count for filtered CTE ==="); Console.WriteLine("Count: {0}", uow.Evaluate <CteStructure_1>(new AggregateOperand(null, null, Aggregate.Count, null), new OperandProperty("ID") > 1)); Console.WriteLine("=== Select all with filtered CTE ==="); foreach (var cteS in new XPCollection <CteStructure_1>(uow)) { Console.WriteLine(cteS.ToString()); } Console.WriteLine("=== Linq2XPO ==="); foreach (var cteS in uow.Query <CteStructure_1>().Where(q => q.Math >= 0)) { Console.WriteLine(cteS.ToString()); } } finally { uow.UnregisterCte("CteStructure_1"); } } Console.ReadLine(); }