- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
QuerySpec > FetchScaler > BadAlias?
Joined: 26-Jan-2006
Hello,
I am working on getting a count from a query using QuerySpec & LLBLGen 4.1. The code has been trimmed down to something fairly basic, however I am getting an exception with what appears to be a predicate on a field located in a target per entity (inherited) table.
[TestMethod]
public void TestBiz2()
{
int count = 0;
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
var q = qf.SourceAdvisory
.From(QueryTarget.LeftJoin(qf.SolutionStatus)
.On(SolutionStatusFields.SolutionStatusId == SourceAdvisoryFields.SolutionStatusId))
.Where(p)
.Select(() => new
{
// Base
SourceAdvisoryId = SourceAdvisoryFields.SourceAdvisoryId.ToValue<int>(),
// Extended
AdvisoryId = SourceAdvisoryExtendedFields.AdvisoryId.ToValue<int?>(),
}
)
.WithProjector(t =>
{
SourceAdvisoryMetaData s = new SourceAdvisoryMetaData { };
return s;
}
)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
Assert.IsTrue(count > 0);
}
The above code throws the following exception:
SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias? Result StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.ORMSupportClasses.DerivedTableDefinition.ToQuery() at SD.LLBLGen.Pro.ORMSupportClasses.DerivedTableDefinition.SD.LLBLGen.Pro.ORMSupportClasses.IDerivedTableDefinition.ToQuery() at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference, List
1& pkSideParameters, List
1& fkSideParameters) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.ScalarQueryExpression.ToQueryText(Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.ScalarQueryExpression.SD.LLBLGen.Pro.ORMSupportClasses.IExpression.ToQueryText(Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendResultsetFields(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRelationCollection relationsToWalk, DelimitedStringList projection, Boolean sortClausesSpecified, Boolean allowDuplicates, Boolean allowAliasesInSubQuery, UniqueList1 distinctViolatingTypes, IRetrievalQuery query, UniqueList
1& fieldNamesInSelectList, Boolean& distinctViolatingTypesFound, Boolean& pkFieldSeen) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(QueryParameters parameters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(QueryParameters parameters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQueryAsProjection(IDataAccessAdapter adapter, DynamicQuery query, IGeneralDataProjector projector) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery(IDataAccessAdapter adapter, DynamicQuery query) at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchScalar[TValue](IDataAccessAdapter adapter, DynamicQuery query)
When I comment out the predicate on the inherited table, I get the expected number for the scalar query.
IPredicateExpression p = new PredicateExpression();
//p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
Also, if I leave the predicate, but remove the join (just for the sake if it) the query succeeds but returns 0 for the count.
Lastly, changing the predicate expression to include a field on the base table works as expected.
Shouldn't a predicate on an inherited table work as shown?
Thanks!
Joined: 26-Jan-2006
Taking a small break, and of course reading another thread of mine (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21009) got me to think why a select projection would be needed in getting a scalar, since I think all scalar needs is really a predicate, and any relations required to satisfy the predicate.
So I stripped it down even further to just:
[TestMethod]
public void TestBiz2()
{
int count = 0;
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
var q = qf.SourceAdvisory
.Where(p)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
Assert.IsTrue(count > 0);
}
This works, thankfully. But it does make me wonder why I can't use a Select, since I also need to do some CorrelatedOver stuff, and apply filtering to those nested results.
Hmmm...
UPDATE
This is definitely unexpected...
[TestMethod]
public void TestBiz2()
{
int count = 0;
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
p.AddWithAnd(SourceAdvisoryFields.SolutionStatusId == 3);
var q = qf.SourceAdvisory
.Where(p)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
Assert.IsTrue(count > 0);
}
The FetchScalar above should return 1 for the count, but it returns 0. The SQL emitted is:
SELECT TOP(@p4) (SELECT COUNT(*) AS [LPAV_] FROM [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] [LPA_L2] WHERE ( ( ( [LPA_L2].[AdvisoryId] = @p1 AND [LPA_L1].[SolutionStatusId] = @p2)))) AS [LLBLV_1] FROM ( [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory] [LPA_L1] LEFT JOIN [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] [LPA_L2] ON [LPA_L1].[SourceAdvisoryId]=[LPA_L2].[SourceAdvisoryId])
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 56653.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1
My intent is something like this:
select COUNT(*)
from pma.SourceAdvisory sa
inner join pma.SourceAdvisoryExtended sas on sa.SourceAdvisoryId = sas.SourceAdvisoryId
where 1=1
and sas.AdvisoryId = 56653
and sa.SolutionStatusId = 3
Not sure how much more basic it can get (and still filter on an inherited field)
Joined: 26-Jan-2006
Attached is a export of the schema from the model viewer. The two selected (red) tables are inherited from SourceAdvisory.
ORM Support Classes: v4.1.13.1213, but I upgraded to v4.1.14.219 and same results.
Thanks for any help!
UPDATE
I found some more odd behavior around this. When I perform a FetchQuery on the QueryFactory I constructed, the list count is 1 as expected. When I follow the next line of code with a FetchScalar, the count is also 1. But if I just do a FetchScalar by itself, the count is 0.
So take this code (which passes):
[TestMethod]
public void TestBiz3()
{
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
p.AddWithAnd(SourceAdvisoryFields.SolutionStatusId == 3);
var q = qf.SourceAdvisory
.Where(p)
.Select(() => new
{
// Base
SourceAdvisoryId = SourceAdvisoryFields.SourceAdvisoryId.ToValue<int>(),
// Extended
AdvisoryId = SourceAdvisoryExtendedFields.AdvisoryId.ToValue<int?>(),
}
)
.WithProjector(t =>
{
SourceAdvisoryExtendedMetaData s = new SourceAdvisoryExtendedMetaData { };
s.SourceAdvisoryId = t.Get<int>("SourceAdvisoryId");
s.AdvisoryId = t.Get<int>("AdvisoryId");
return s;
}
)
;
IList<SourceAdvisoryExtendedMetaData> list = app.Adapter.FetchQuery(q);
int count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
Assert.IsTrue(list.Count == 1); // Passes
Assert.IsTrue(count == 1); // Passes
}
}
But this code (which fails):
[TestMethod]
public void TestBiz3()
{
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
p.AddWithAnd(SourceAdvisoryFields.SolutionStatusId == 3);
var q = qf.SourceAdvisory
.Where(p)
.Select(() => new
{
// Base
SourceAdvisoryId = SourceAdvisoryFields.SourceAdvisoryId.ToValue<int>(),
// Extended
AdvisoryId = SourceAdvisoryExtendedFields.AdvisoryId.ToValue<int?>(),
}
)
.WithProjector(t =>
{
SourceAdvisoryExtendedMetaData s = new SourceAdvisoryExtendedMetaData { };
s.SourceAdvisoryId = t.Get<int>("SourceAdvisoryId");
s.AdvisoryId = t.Get<int>("AdvisoryId");
return s;
}
)
;
//IList<SourceAdvisoryExtendedMetaData> list = app.Adapter.FetchQuery(q);
int count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
//Assert.IsTrue(list.Count == 1); // Passes
Assert.IsTrue(count == 1); // Fails
}
}
The executed SQL from the first test looks like this:
SELECT TOP(@p4) (SELECT COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L4].[SourceAdvisoryId], [LPA_L5].[AdvisoryId] FROM ( [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory] [LPA_L4] LEFT JOIN [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] [LPA_L5] ON [LPA_L4].[SourceAdvisoryId]=[LPA_L5].[SourceAdvisoryId]) WHERE ( ( ( [LPA_L5].[AdvisoryId] = @p1 AND [LPA_L4].[SolutionStatusId] = @p2)))) [LPA_L3]) AS [LLBLV_1] FROM ( [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory] [LPA_L1] LEFT JOIN [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] [LPA_L2] ON [LPA_L1].[SourceAdvisoryId]=[LPA_L2].[SourceAdvisoryId])
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 56653.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Whereas the executed SQL from the second looks like this:
SELECT TOP(@p4) (SELECT COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L1].[SourceAdvisoryId], [LPA_L2].[AdvisoryId] FROM ( [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory] LEFT JOIN [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] ON [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory].[SourceAdvisoryId]=[SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended].[SourceAdvisoryId]) WHERE ( ( ( [LPA_L2].[AdvisoryId] = @p1 AND [LPA_L1].[SolutionStatusId] = @p2)))) [LPA_L3]) AS [LLBLV_1] FROM ( [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisory] [LPA_L1] LEFT JOIN [SVCS_Patch_Mgmt_TestHarness].[pma].[SourceAdvisoryExtended] [LPA_L2] ON [LPA_L1].[SourceAdvisoryId]=[LPA_L2].[SourceAdvisoryId])
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 56653.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
I am guessing the first example works because 'q' has been modified somewhere in the first execution of FetchQuery, prepping it for it to work with the FetchScalar. But a FetchScalar should be able to stand on its own.
All this behavior aside seems like a separate, but possibly related issue to an inability to filter on a inherited field with a join present (bad alias?) using FetchScalar--which is what started this thread.
Filename | File size | Added on | Approval |
---|---|---|---|
llblgen-inheritance-sourceadvisory.png | 136,563 | 24-Feb-2014 14:53.40 | Approved |
Joined: 21-Aug-2005
Let's tackle this one at a time.
Your first code:
var qf = new QueryFactory(); IPredicateExpression p = new PredicateExpression(); p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653); var q = qf.SourceAdvisory .From(QueryTarget.LeftJoin(qf.SolutionStatus) .On(SolutionStatusFields.SolutionStatusId == SourceAdvisoryFields.SolutionStatusId)) .Where(p) .Select(() => new { // Base SourceAdvisoryId = SourceAdvisoryFields.SourceAdvisoryId.ToValue<int>(), // Extended AdvisoryId = SourceAdvisoryExtendedFields.AdvisoryId.ToValue<int?>(), } ) .WithProjector(t => { SourceAdvisoryMetaData s = new SourceAdvisoryMetaData { }; return s; } ) ; count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
The query needs a join to the SourceAdvisoryExtended entity. Could you please try this out?
Joined: 26-Jan-2006
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
var q = qf.SourceAdvisory
.From(QueryTarget.InnerJoin(qf.SourceAdvisoryExtended).On(SourceAdvisoryExtendedFields.SourceAdvisoryId == SourceAdvisoryFields.SourceAdvisoryId)
)
.Where(p)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
Thanks Walaa... but, same error.
Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?
Joined: 28-Nov-2005
Looking again in your first post I think that the approach is not correct. As you expect some type in the results, you should use the subtype in the query, you can filter on parent's fields, the relation is added by LLBLGen in SQL. Example:
// query
var qf = new QueryFactory();
var p = new PredicateExpression(ClerkFields.JobDescription == "something");
var q = qf.Clerk.Where(p);
// fetch
var count = 0;
using (var adapter = new DataAccessAdapter())
{
count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
// test
Assert.AreEqual(1, count);
You also could cast to the subtype. The following example shows how, doing a select and fetch that, then test the count in-memory.
// query
var qf = new QueryFactory();
var p = new PredicateExpression(ClerkFields.JobDescription == "something");
var q = qf.Employee.CastTo<ClerkEntity>()
.Where(p)
.Select(ClerkFields.WorksForDepartmentId);
// fetch
var count = 0;
using (var adapter = new DataAccessAdapter())
{
var results = adapter.FetchQuery(q);
count = results.Count;
}
// test
Assert.AreEqual(1, count);
Joined: 26-Jan-2006
Thanks for the suggestion daelmo, however casting to a subtype, and including a join fails also.
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
var q = qf.SourceAdvisory.CastTo<SourceAdvisoryExtendedEntity>()
.From(QueryTarget.InnerJoin(qf.SolutionStatus).On(SolutionStatusFields.SolutionStatusId == SourceAdvisoryExtendedFields.SolutionStatusId)
)
.Where(p)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
And, as shown previously, the query works without a cast, but with no join.
using (AppService app = new AppService())
{
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(SourceAdvisoryExtendedFields.AdvisoryId == 56653);
var q = qf.SourceAdvisory
.Where(p)
;
count = app.Adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}
Also, thinking about your point of view, isn't Target Per Enitity all about polymorphism? In that a specific cast shouldn't be necessary except if fields from the subtype are needed after the fetch has been made--in memory? Granted, this is getting a scalar, so there is no resultset to cast, but ideally the same query used to to populate a grid, for example, would be desirable to reuse to tell it how many _true _records there are (with filtering, if applicable, and irrespective of paging).
Thanks!
Joined: 21-Aug-2005
The following works with me:
var qf = new QueryFactory();
IPredicateExpression p = new PredicateExpression();
p.AddWithAnd(ProductExFields.ExtraField == "CC");
var q = qf.Product
.From(QueryTarget.LeftJoin(qf.Category)
.On(ProductFields.CategoryId == CategoryFields.Id))
.Where(p)
;
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
It produces the following:
SELECT TOP(@p3) (SELECT COUNT(*) AS [LPAV_] FROM ( [LLBLTest].[dbo].[Product] [LPA_L1] LEFT JOIN [LLBLTest].[dbo].[Category] ON [LPA_L1].[CategoryId] = [LLBLTest].[dbo].[Category].[Id]) WHERE ( ( ( [LPA_L2].[ExtraField] = @p1)))) AS [LLBLV_1] FROM ( [LLBLTest].[dbo].[Product] [LPA_L1] LEFT JOIN [LLBLTest].[dbo].[ProductEx] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[ProductId])
Parameter: @p1 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "CC".
Parameter: @p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Also I can replace ProductFields.CategoryId to ProductExFields.CategoryId in the On clause.
What should I do differently to reproduce the issue?