The problem you're having is that the initial start query is a wrapping query around the actual query you want to augment.
Say I have your query in an example:
using(var adapter = new DataAccessAdapter())
{
var qf = new QueryFactory();
var q = qf.Create().Select(() => new {CustomerId = CustomerFields.CustomerId.Source("a").ToValue<string>()})
.From(qf.Customer.Select(CustomerFields.CustomerId).As("a"));
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
Assert.AreEqual(91, count);
q = q.Where(CustomerFields.Country.Source("a").Equal("USA"));
var results = adapter.FetchQuery(q);
Assert.AreEqual(13, results.Count);
}
At first glance this should work right? but if we look at the sql generated we see that this isn't going to work:
the count query:
SELECT TOP(1 /* @p2 */) (SELECT COUNT(*) AS [LPAV_]
FROM (SELECT [LPA_a2].[CustomerId]
FROM (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
FROM [Northwind].[dbo].[Customers]) [LPA_a2]) [LPA_L1]) AS [LLBLV_1]
FROM [Northwind].[dbo].[Customers]
this works, but the fetch with the where fails:
SELECT [LPA_a1].[CustomerId]
FROM (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
FROM [Northwind].[dbo].[Customers]) [LPA_a1]
WHERE (([LPA_a1].[Country] = 'USA' /* @p1 */))
This is because the projection you formulated, the
.Select(tbl.id).Distinct().As("a"));
part, only contains the 'id' part, and the predicate expression added to the query thus also can only work with that field. If I change it to also contain the field I need to filter on, it will work:
using(var adapter = new DataAccessAdapter())
{
var qf = new QueryFactory();
var q = qf.Create().Select(() => new {CustomerId = CustomerFields.CustomerId.Source("a").ToValue<string>()})
.From(qf.Customer.Select(CustomerFields.CustomerId, CustomerFields.Country).As("a"));
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
Assert.AreEqual(91, count);
q = q.Where(CustomerFields.Country.Source("a").Equal("USA"));
var results = adapter.FetchQuery(q);
Assert.AreEqual(13, results.Count);
}
You also see I just append the where to the query, no need to wrap it again in a query with a select.
So in your case, all fields the predicateexpression works on has to be in the projection of the initial query's source, namely here: .Select(tbl.id).Distinct().As("a"));
and have to reference the fields by the alias 'a' as the derived table you're creating here is aliased as such.