I got two complicated queries and could not figure out. Please help, Frans!

I am using adapter mode.

1. What is the equivalent in LLBLGen for the following query?

select count(*) from if_file where file_id in
(
    select max(file_id) from if_file where access_by=4 and record_type_id in(3,7) group by mime_type
)

1. I am using paging:

IfDownloadViewTypedList ifView=new IfDownloadViewTypedList();
adapter.FetchTypedList(ifView.GetFieldsInfo(), ifView, bucket,int.MaxValue,SortOperator.Descending,false,null,pageNumber,pageSize); 

the data for each page comes from this query

select * from if_file where file_id in
(
    select max(file_id) from if_file where access_by=4 and record_type_id in(3,7) group by mime_type
)

So what's the equivalent for the bucket?

Thanks in advance, Frans.

Fred

wangchao1687 wrote:

I got two complicated queries and could not figure out. Please help, Frans!

I am using adapter mode.

1. What is the equivalent in LLBLGen for the following query?

select count(*) from if_file where file_id in
(
    select max(file_id) from if_file where access_by=4 and record_type_id in(3,7) group by mime_type
)

You should use a DataAccessAdapter.GetDbCount(fields, bucket). Fields, you get by using: IfFileEntityFactory.CreateFields();

and the bucket, you need to create a FieldCompareSetPredicate. It's a bit verbose, I admit. Something like:

RelationPredicateBucket filter = new RelationPredicateBucket();
IEntityField2 setField = EntityFieldFactory.Create(IfFileFieldIndex.FileId);
setField.AggregateFunctionToApply = AggregateFunction.Max;
PredicateExpression setFilter = new PredicateExpression();
setFilter.Add(PredicateFactory.CompareValue(IfFileFieldIndex.AccessBy, ComparisonOperator.Equal, 4));
setFilter.AddWithAnd(PredicateFactory.CompareRange(IfFileFieldIndex.RecortTypeId, 3, 7));
GroupByCollection setGroupBy = new GroupByCollection();
setGroupBy.Add(EntityFieldFactory.Create(IfFileFieldIndex.MimeType));
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
            EntityFieldFactory.Create(IfFileFieldIndex.FileId), null,
            setField, null,
            SetOperator.In,
            setFilter.PredicateExpression,
            setFilter.Relations,
            string.Empty;
            0, 
            null,
            false,
            setGroupBy));

Then use 'filter' for the bucket to GetDbCount.

1. I am using paging:

IfDownloadViewTypedList ifView=new IfDownloadViewTypedList();
adapter.FetchTypedList(ifView.GetFieldsInfo(), ifView, bucket,int.MaxValue,SortOperator.Descending,false,null,pageNumber,pageSize); 

the data for each page comes from this query

select * from if_file where file_id in
(
    select max(file_id) from if_file where access_by=4 and record_type_id in(3,7) group by mime_type
)

So what's the equivalent for the bucket?

See above

I got 'SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression' does not contain a definition for 'PredicateExpression' and 'SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression' does not contain a definition for 'Relations' exceptions. What version are you talking about? I am using 1.0.2004.1 Final.

Do I have to upgrade to the version you are talking about?

wangchao1687 wrote:

I got 'SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression' does not contain a definition for 'PredicateExpression' and 'SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression' does not contain a definition for 'Relations' exceptions. What version are you talking about? I am using 1.0.2004.1 Final. Do I have to upgrade to the version you are talking about?

GetDbCount is supported in 1.0.2004.2, but you can execute a GetScalar query on adapter in 1.0.2004.1, it should be ok.

The error you get might come from the fact that I defined 'filter' as a RelationPredicateBucket object, and in your code it's perhaps a PredicateExpression, and thus it doesn't work. Could you check that for me please?

I changed to use PredicateExpression instead of RelationPredicateBucket, but one more compiler exception 'SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression' does not contain a definition for 'PredicateExpression' raised.

I will try to upgrade to 1.0.2004.2 and re-generate code, then try the bucket.

Thank you for fast reply!

Fred

I upgraded to 1.0.2004.2 and regenerated code, but got lots of errors in EntityFieldFactory class.

new EntityField2("Phone", "AddressEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 22, 0, 0, false);

FactoryClasses\EntityFieldFactory.cs(49): No overload for method 'EntityField2' takes '10' arguments

the constructor expects 12 or 11 parameters but the generated code tries to use 10 parameters, I have no idea what is going on?

Fred

I rolled back to 1.0.2004.1. Because the migration involves all my coleagus and I do not think it is worth to do that.

So Can you tell me how can i make it work under 1.0.2004.1?

Fred

RelationPredicateBucket filter = new RelationPredicateBucket();
IEntityField2 setField = EntityFieldFactory.Create(IfFileFieldIndex.FileId);
setField.AggregateFunctionToApply = AggregateFunction.Max;
PredicateExpression setFilter = new PredicateExpression();
setFilter.Add(PredicateFactory.CompareValue(IfFileFieldIndex.AccessBy, ComparisonOperator.Equal, 4));
setFilter.AddWithAnd(PredicateFactory.CompareRange(IfFileFieldIndex.RecortTypeId, 3, 7));
GroupByCollection setGroupBy = new GroupByCollection();
setGroupBy.Add(EntityFieldFactory.Create(IfFileFieldIndex.MimeType));
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
            EntityFieldFactory.Create(IfFileFieldIndex.FileId), null,
            setField, null,
            SetOperator.In,
            setFilter.PredicateExpression,
            setFilter.Relations,
            string.Empty;
            0, 
            null,
            false,
            setGroupBy));

I changed to :

            RelationPredicateBucket filter = new RelationPredicateBucket();

            IEntityField2 setField = EntityFieldFactory.Create(IfFileFieldIndex.FileId);
            setField.AggregateFunctionToApply = AggregateFunction.Max;

            RelationPredicateBucket setFilter = new RelationPredicateBucket();
            //PredicateExpression setFilter = new PredicateExpression();
            setFilter.PredicateExpression.Add(PredicateFactory.CompareValue(IfFileFieldIndex.AccessBy, ComparisonOperator.Equal, 4));
            setFilter.PredicateExpression.AddWithAnd(PredicateFactory.CompareRange(IfFileFieldIndex.RecordTypeId, 3, 7));

            GroupByCollection setGroupBy = new GroupByCollection();
            setGroupBy.Add(EntityFieldFactory.Create(IfFileFieldIndex.FileName));

            filter.PredicateExpression.Add(
                new FieldCompareSetPredicate(
            EntityFieldFactory.Create(IfFileFieldIndex.FileId), null,
            setField, null,
            SetOperator.In,
            setFilter.PredicateExpression,
            setFilter.Relations,
            string.Empty,
            0, 
            null,
            false,
            setGroupBy));

Got this:

No overload for method 'FieldCompareSetPredicate' takes '12' arguments.

I checked the method signatures of FieldCompareSetPredicate, no way to put group by clause.

Could you please revise it for me?

wangchao1687 wrote:

I upgraded to 1.0.2004.2 and regenerated code, but got lots of errors in EntityFieldFactory class.

new EntityField2("Phone", "AddressEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 22, 0, 0, false);

FactoryClasses\EntityFieldFactory.cs(49): No overload for method 'EntityField2' takes '10' arguments

the constructor expects 12 or 11 parameters but the generated code tries to use 10 parameters, I have no idea what is going on? Fred

Make sure you have made the files writable. If some files are readonly and they couldn't be written, they will be listed in red in the generator log shown at the end of the generation process. I think some files weren't overwritten.

Group by in FieldCompareSetPredicate isn't supported in 1.0.2004.1, it was added in 1.0.2004.2.

I know you'll now say: "catch 22!", though upgrading to 1.0.2004.2 should be painless, and the error you got is caused by some files not being overwritten. If you retry, make sure the generated code files are writable and you'll see the code will compile.

I upgraded to 1.0.2004.2, and it works.

RelationPredicateBucket filter = new RelationPredicateBucket();

        IEntityField2 setField = EntityFieldFactory.Create(IfFileFieldIndex.FileId);
        setField.AggregateFunctionToApply = AggregateFunction.Max;

        RelationPredicateBucket setFilter = new RelationPredicateBucket();
        if(!"88".Equals(affiliateOrPartnerId))
            setFilter.PredicateExpression.Add(PredicateFactory.CompareValue(IfFileFieldIndex.AccessBy, ComparisonOperator.Equal, int.Parse(affiliateOrPartnerId)));
        setFilter.PredicateExpression.AddWithAnd(PredicateFactory.CompareRange(IfFileFieldIndex.RecordTypeId, (int)RecordType.GENERATED_EXAM_MARKS_EXPORT_FILE, (int)RecordType.GENERATED_PDF_REPORT_FILE));
        GroupByCollection setGroupBy = new GroupByCollection();
        setGroupBy.Add(EntityFieldFactory.Create(IfFileFieldIndex.FileName));

        filter.PredicateExpression.Add(
            new FieldCompareSetPredicate(
            EntityFieldFactory.Create(IfFileFieldIndex.FileId), null,
            setField, null,
            SetOperator.In,
            setFilter.PredicateExpression,
            setFilter.Relations,
            string.Empty,
            0, 
            null,
            false,
            setGroupBy));

adapter.FetchTypedList(ifView.GetFieldsInfo(), ifView, GetFilter(affiliateOrPartnerId),int.MaxValue,sorter,false,null,pageNumber,pageSize);

Thanks! Frans.

Fred