QuerySpec > FetchScaler > BadAlias?

Posts   
 
    
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 23-Feb-2014 14:33:25   

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, List1& pkSideParameters, List1& 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, UniqueList1& 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!

Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 23-Feb-2014 15:48:08   

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... disappointed

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)

cry

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 24-Feb-2014 07:49:35   

Please describe the inheritance involved and make sure you are using the latest v4.1 build.

David Elizondo | LLBLGen Support Team
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 24-Feb-2014 14:53:31   

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.

Attachments
Filename File size Added on Approval
llblgen-inheritance-sourceadvisory.png 136,563 24-Feb-2014 14:53.40 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-Feb-2014 20:17:06   

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?

Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 24-Feb-2014 23:07:37   


                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?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 25-Feb-2014 06:48:55   

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);
David Elizondo | LLBLGen Support Team
Isz
User
Posts: 108
Joined: 26-Jan-2006
# Posted on: 25-Feb-2014 14:13:07   

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!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-Feb-2014 18:12:52   

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?