Hi All,

I've been having a problem getting some DataSets over the wire using a number of methods including loading up a NET typed DataSet, a DataTable, and now a custom DTO.

Every so often I get an exception that the service has terminated unexpectedly, and the exception states that the service endpoint is not configured correctly for HTTP. However this is not the case, the service working well for other operations.

So after some time investigating the issue there seems to be a problem when I include a certain memo field (from an MS Access DB). Now that I have created some DTOs I use DataProjectorToCustomClass and project the fields onto a DTO collection. When I pass this over the wire and drop it into a report, invariably the operation fails and an exception is raised - but not in the method which fetches the projection. If the transfer works successfully I sometimes see corruption with the data in the memo field. And this corruption spreads to all records with the memo field.

I know that the memo field in question contains non-ASCII characters and so i'm wondering if the data is being serialized correctly.

Firstly, with projections, is string type correct for projecting memos?

I would like to perform some more tests by cleaning up the data before wiring it to the client. I see that the DataValueProjector object has a delegate parameter. Can I call a custom function before the data gets loaded into the DTO? Or should I just perform the cleanup operation within the DTO property? What i'm looking to do is filter out all non-ASCII data to see if I can clear the corruption problem.

Thanks.

Firstly, with projections, is string type correct for projecting memos?

Yes

I would like to perform some more tests by cleaning up the data before wiring it to the client. I see that the DataValueProjector object has a delegate parameter. Can I call a custom function before the data gets loaded into the DTO? Or should I just perform the cleanup operation within the DTO property? What i'm looking to do is filter out all non-ASCII data to see if I can clear the corruption problem.

First of all could you please post the exception text and stack trace.

Hi Walaa,

I'll capture the exception data shortly...

In the meantime i've experimented by nulling all memo fields in the database.

Now instead of checking at the client end i now step into the WCF Service to see what is being returned after the projection. I have this code:

public IList<PendingOrder> GetPendingOrders(int accountNumber)
        {
            ResultsetFields fields = new ResultsetFields(23);
            fields[0] = RequisitionFields.RequisitionNumber;
            fields[1] = RequisitionFields.RequisitionDate;
            fields[2] = RequisitionFields.Reference;
            fields[3] = RequisitionFields.CustomerReference;
            fields[4] = RequisitionFields.OrderPickup;
            fields[5] = RequisitionFields.RequisitionNotes;
            fields[6] = AccountFields.AccountName;
            fields[7] = FreightCarrierFields.FreightCarrierName;
            fields[8] = AccountCustomerFields.CustomerName;
            fields[9] = AccountCustomerFields.ContactName;
            fields[10] = AccountCustomerFields.PostalAddress1;
            fields[11] = AccountCustomerFields.PostalAddress2;
            fields[12] = AccountCustomerFields.PostalSuburb;
            fields[13] = AccountCustomerFields.PostalState;
            fields[14] = AccountCustomerFields.PostalPostalCode;
            fields[15] = AccountCustomerFields.Country;
            fields[16] = AccountCustomerFields.PhoneNumber;
            fields[17] = RequisitionLineFields.ProductNumber;
            fields[18] = RequisitionLineFields.Quantity;
            fields[19] = RequisitionLineFields.LineNotes;
            fields[20] = ProductFields.AccountProductNumber;
            fields[21] = ProductFields.ExaltDescription;
            fields.DefineField(LicencePlateFields.StockOnHand, 22, "StockOnHand", AggregateFunction.Sum);

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(RequisitionEntity.Relations.AccountEntityUsingAccountNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.FreightCarrierEntityUsingFreightCarrierNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.AccountCustomerEntityUsingAccountCustomerNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.RequisitionLineEntityUsingRequisitionNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionLineEntity.Relations.ProductEntityUsingProductNumber, JoinHint.Left);
            bucket.Relations.Add(ProductEntity.Relations.LicencePlateEntityUsingProductNumber, JoinHint.Left);

            if (accountNumber > 1)
            {
                bucket.PredicateExpression.Add(RequisitionFields.AccountNumber == accountNumber);
            }
            bucket.PredicateExpression.Add(RequisitionFields.TransactionTypeNumber == (int)TransactionType.OrderIssueGoods);
            bucket.PredicateExpression.Add(new FieldCompareNullPredicate(RequisitionFields.FinalisedDate, null, false));

            IGroupByCollection groupByClause = new GroupByCollection();
            for (int i = 0; i < 22; i++)
            {
                groupByClause.Add(fields[i]);
            }

            // Setup projection
            List<PendingOrder> pendingOrderList = new List<PendingOrder>();
            DataProjectorToCustomClass<PendingOrder> projector = new DataProjectorToCustomClass<PendingOrder>(pendingOrderList);
            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector("OrderNumber", 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector("RequisitionDate", 1, typeof(DateTime?)));
            valueProjectors.Add(new DataValueProjector("Reference1", 2, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Reference2", 3, typeof(string)));
            valueProjectors.Add(new DataValueProjector("OrderPickup", 4, typeof(bool)));
            valueProjectors.Add(new DataValueProjector("OrderNotes", 5, typeof(string)));
            valueProjectors.Add(new DataValueProjector("AccountName", 6, typeof(string)));
            valueProjectors.Add(new DataValueProjector("CarrierName", 7, typeof(string)));
            valueProjectors.Add(new DataValueProjector("CustomerName", 8, typeof(string)));
            valueProjectors.Add(new DataValueProjector("ContactName", 9, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Address1", 10, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Address2", 11, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Suburb", 12, typeof(string)));
            valueProjectors.Add(new DataValueProjector("State", 13, typeof(string)));
            valueProjectors.Add(new DataValueProjector("PostCode", 14, typeof(int)));
            valueProjectors.Add(new DataValueProjector("Country", 15, typeof(string)));
            valueProjectors.Add(new DataValueProjector("PhoneNumber", 16, typeof(string)));
            valueProjectors.Add(new DataValueProjector("ProductNumber", 17, typeof(string)));
            valueProjectors.Add(new DataValueProjector("OrderQuantity", 18, typeof(int?)));
            valueProjectors.Add(new DataValueProjector("LineNotes", 19, typeof(string)));
            valueProjectors.Add(new DataValueProjector("AccountProductNumber", 20, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Description", 21, typeof(string)));
            valueProjectors.Add(new DataValueProjector("StockOnHand", 22, typeof(double)));

            using (DataAccessAdapter adapter = GetDataAccessAdapter())
            {
                try
                {
                    adapter.FetchProjection(valueProjectors, projector, fields, bucket, 20, null, groupByClause, true, 0, 0);
                }
                catch (ORMQueryExecutionException qryex)
                {
                    logging.Log.Error(qryex.Message);
                    logging.Log.Error(qryex.QueryExecuted);
                    throw new Exception("failed to get pending orders data", qryex);
                }
                catch (Exception ex)
                {
                    logging.Log.Error(ex.Message);
                    throw new Exception("failed to get pending orders data", ex);
                }
            }

            return pendingOrderList;
        }

PendingOrder is my simple DTO. If I breakpoint on the last line and step through each PendingOrder item I see corruption in each OrderNotes field - which is the only memo field. Data is being returned like "7" and "ᜅ8" and "复8". I've double-checked and the OrderNotes field is definitely null throughout the DB.

Is there anything I can do to monitor the returned query data so I can see what is getting projected onto the DTO?

Note i've restricted to 20 records just for testing.

Thanks.

Waiting for the exception data.

In the mean time:

If you want to isolate the projection, just fetch a DynamicList with only one field (for testing).

            ResultsetFields fields = new ResultsetFields(1);
            fields.DefineField(RequisitionFields.RequisitionNotes, 0);

And examine the fetched data, to see if it's null or not.

Hi Walaa,

The exception was being raised in the service layer and was (i believe) due to a corruption in the SOAP message. I received no exceptions in my service methods. And once I had nulled out the data in the memo fields I stopped receiving exceptions altogether, but the corruption in the DTO still remained. So for now I have statically linked the client & service so I can debug the code.

As per your suggestion I removed all but the memo field in the result fields & projection and this removed the corruption. I then one-by-one added more fields to the projection until the corruption returned. Unfortunately this proved rather inconclusive. A full projection should retrieve around 21 fields. At around 17 or 18 the corruption with the memo field returns. But I can randomly swap in and out different fields and the problem remains.

The only conclusion I came to is that if I include a memo field + more than about 17 fields I get corruption. If I have more than 17 fields but no memo field then it works OK. The corruption itself only applies to the memo field and no other data. If I exchange for a non-memo field then this also works OK.

So finally I reverted my code back to the example with all fields included. I returned to the DB and changed the memo field to a text field. I rebuilt the entity projects and re-run the code. Everything worked. The RequisitionNotes field is now displaying some real data - albeit truncated. I then returned again to the DB and changed back to a memo field, rebuilt the entity projects, re-run without changing anything and the problem returned.

As I said before even after nulling the memo field data, the fact I am including a memo field and a lot of other fields causes corruption in the DTO field that should contain memo field data.

Having said all that, I just thought of something and tried a quick test: remove grouping. I had to remove the aggregate sum field, and this has returned a lot of duplicated data, but the corruption has gone, and any test data I have placed in the RequisitionNotes memo field is now being displayed properly.

So is there a problem grouping on a memo field, even if it is null?

Ok, sorry for wasting your time, this is an Access problem. See screenshot. If I group on the memo field it corrupts the field. I've no idea yet where this data is coming from. I've nulled the RequisitionNotes field and even run a query to return all records where the field is not null - which returns zero. But yet the grouped RequisitionNotes field continues to return corrupted data. Great stuff!!!

So now I am thinking perhaps I should do this another way. Is it possible to create a separate subquery containing the RequisitionNotes and RequisitionNumber field and join this to the main query, so I don't have to group the memo field? I could do this fairly easy with SQL but i'm not quite sure how to achieve this with LLBLGen.

And to follow up see this article about grouping memos bug in MS Access, and this KB article from MS which states: We recommend that you do not use the GROUP BY clause on Memo fields in select queries.

The solution is to use Select .... First(memoField) and not to group the field. Can you use First with LLBLGen? Here's the working partial query:

SELECT
    tblRequisitions.RequisitionNumber,
    First(tblRequisitions.RequisitionNotes) AS FirstOfRequisitionNotes,
    tblRequisitionLines.ProductNumber, Sum(tblLicencePlates.StockOnHand) AS SumOfStockOnHand
FROM 
    tblLicencePlates
    RIGHT JOIN (tblRequisitions
    RIGHT JOIN (tblProducts
    RIGHT JOIN tblRequisitionLines
    ON tblProducts.ProductNumber = tblRequisitionLines.ProductNumber)
    ON tblRequisitions.RequisitionNumber = tblRequisitionLines.RequisitionNumber)
    ON tblLicencePlates.ProductNumber = tblRequisitionLines.ProductNumber
GROUP BY
    tblRequisitions.RequisitionNumber, tblRequisitionLines.ProductNumber;

Perhaps to avoid confusion the title of this thread should be changed to: Microsoft Access memo grouping bug.

Thanks.

I can't reproduce your issue with this similar scenario: - MSAccess 2007 DB and LLBLGen v2.6 latest RTL - 21 fields involved in dynamic list - using groupby - memo field included in field list and groupby - projecting the result

Could you please provide a repro case for us?

Just read your last post. I didn't try it with Access 2k, maybe they fixed that in 2007. Anyway, that advise is wise I think (not grouping in memo fields).

daelmo wrote:

Just read your last post. I didn't try it with Access 2k, maybe they fixed that in 2007. Anyway, that advise is wise I think (not grouping in memo fields).

Thanks daelmo. I believe the problem has not yet been fixed.

So that still leaves me with a problem if and when I need to include a memo field in any query that also contains aggregates. It seems the only fix is to use First - which is Access specific - which I believe is not implemented in LLBLGen.

I am pretty stuck at the moment as i'm trying to produce a range of reports which include totals, averages, summaries etc. along with corresponding notes, i.e. a memo.

It seems the only fix is to use First - which is Access specific - which I believe is not implemented in LLBLGen.

If First is an Access function, then you can use a DbFunctionCall, to call it. Or if you are using linq, you can use function mapping to map a new function to that.

Here's a sample query I knocked up which places the grouped fields inside a subquery which is joined to the non-grouped main table which includes the memo field:

SELECT
    RQ.RequisitionNumber, RQ.ProductNumber, RQ.SumOfStockOnHand, RequisitionNotes
FROM
    (SELECT
        tblRequisitions.RequisitionNumber, tblRequisitionLines.ProductNumber, Sum(tblLicencePlates.StockOnHand) AS SumOfStockOnHand
    FROM
        tblRequisitions
    INNER JOIN
        (tblRequisitionLines
    LEFT JOIN
        tblLicencePlates ON tblRequisitionLines.ProductNumber = tblLicencePlates.ProductNumber)
        ON tblRequisitions.RequisitionNumber = tblRequisitionLines.RequisitionNumber
    GROUP BY
        tblRequisitions.RequisitionNumber, tblRequisitionLines.ProductNumber)  AS RQ
INNER JOIN
    tblRequisitions ON RQ.RequisitionNumber = tblRequisitions.RequisitionNumber;

Unfortunately I can't quite figure out how to produce this with predicates

Walaa wrote:

It seems the only fix is to use First - which is Access specific - which I believe is not implemented in LLBLGen.

If First is an Access function, then you can use a DbFunctionCall, to call it. Or if you are using linq, you can use function mapping to map a new function to that.

Ah, OK thanks Walaa, I will work on this over the weekend.

Kind Regards.

OK, all working. Here's my final method using Access 'First' function which now means the RequisitionNotes memo field doesn't have to be grouped.

        public List<PendingOrder> GetPendingOrdersWithSOH(int accountNumber)
        {
            ResultsetFields fields = new ResultsetFields(23);
            fields[0] = RequisitionFields.RequisitionNumber;
            fields[1] = RequisitionFields.RequisitionDate;
            fields[2] = RequisitionFields.Reference;
            fields[3] = RequisitionFields.CustomerReference;
            fields[4] = RequisitionFields.OrderPickup;
            fields[5] = AccountFields.AccountName;
            fields[6] = FreightCarrierFields.FreightCarrierName;
            fields[7] = AccountCustomerFields.CustomerName;
            fields[8] = AccountCustomerFields.ContactName;
            fields[9] = AccountCustomerFields.PostalAddress1;
            fields[10] = AccountCustomerFields.PostalAddress2;
            fields[11] = AccountCustomerFields.PostalSuburb;
            fields[12] = AccountCustomerFields.PostalState;
            fields[13] = AccountCustomerFields.PostalPostalCode;
            fields[14] = AccountCustomerFields.Country;
            fields[15] = AccountCustomerFields.PhoneNumber;
            fields[16] = RequisitionLineFields.ProductNumber;
            fields[17] = RequisitionLineFields.Quantity;
            fields[18] = RequisitionLineFields.LineNotes;
            fields[19] = ProductFields.AccountProductNumber;
            fields[20] = ProductFields.ExaltDescription;
            fields[21] = RequisitionFields.RequisitionNotes;
            fields[21].ExpressionToApply = new DbFunctionCall( "First", new object[] { RequisitionFields.RequisitionNotes } );
            fields.DefineField(LicencePlateFields.StockOnHand, 22, "StockOnHand", AggregateFunction.Sum);

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(RequisitionEntity.Relations.AccountEntityUsingAccountNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.FreightCarrierEntityUsingFreightCarrierNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.AccountCustomerEntityUsingAccountCustomerNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionEntity.Relations.RequisitionLineEntityUsingRequisitionNumber, JoinHint.Left);
            bucket.Relations.Add(RequisitionLineEntity.Relations.ProductEntityUsingProductNumber, JoinHint.Left);
            bucket.Relations.Add(ProductEntity.Relations.LicencePlateEntityUsingProductNumber, JoinHint.Left);

            if (accountNumber > 1)
            {
                bucket.PredicateExpression.Add(RequisitionFields.AccountNumber == accountNumber);
            }
            bucket.PredicateExpression.Add(RequisitionFields.TransactionTypeNumber == (int)TransactionType.OrderIssueGoods);
            bucket.PredicateExpression.Add(new FieldCompareNullPredicate(RequisitionFields.FinalisedDate, null, false));

            GroupByCollection groupByClause = new GroupByCollection();
            for (int i = 0; i < 21; i++)
            {
                groupByClause.Add(fields[i]);
            }

            // Setup projection
            List<PendingOrder> pendingOrderList = new List<PendingOrder>();
            DataProjectorToCustomClass<PendingOrder> projector = new DataProjectorToCustomClass<PendingOrder>(pendingOrderList);
            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector("OrderNumber", 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector("RequisitionDate", 1, typeof(DateTime)));
            valueProjectors.Add(new DataValueProjector("Reference1", 2, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Reference2", 3, typeof(string)));
            valueProjectors.Add(new DataValueProjector("OrderPickup", 4, typeof(bool)));
            valueProjectors.Add(new DataValueProjector("AccountName", 5, typeof(string)));
            valueProjectors.Add(new DataValueProjector("CarrierName", 6, typeof(string)));
            valueProjectors.Add(new DataValueProjector("CustomerName", 7, typeof(string)));
            valueProjectors.Add(new DataValueProjector("ContactName", 8, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Address1", 9, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Address2", 10, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Suburb", 11, typeof(string)));
            valueProjectors.Add(new DataValueProjector("State", 12, typeof(string)));
            valueProjectors.Add(new DataValueProjector("PostCode", 13, typeof(int)));
            valueProjectors.Add(new DataValueProjector("Country", 14, typeof(string)));
            valueProjectors.Add(new DataValueProjector("PhoneNumber", 15, typeof(string)));
            valueProjectors.Add(new DataValueProjector("ProductNumber", 16, typeof(string)));
            valueProjectors.Add(new DataValueProjector("OrderQuantity", 17, typeof(int)));
            valueProjectors.Add(new DataValueProjector("LineNotes", 18, typeof(string)));
            valueProjectors.Add(new DataValueProjector("AccountProductNumber", 19, typeof(string)));
            valueProjectors.Add(new DataValueProjector("Description", 20, typeof(string)));
            valueProjectors.Add(new DataValueProjector("OrderNotes", 21, typeof(string)));
            valueProjectors.Add(new DataValueProjector("StockOnHand", 22, typeof(double)));

            using (DataAccessAdapter adapter = GetDataAccessAdapter())
            {
                try
                {
                    adapter.FetchProjection(valueProjectors, projector, fields, bucket, 0, null, groupByClause, false, 0, 0);
                }
                catch (ORMQueryExecutionException qryex)
                {
                    logging.Log.Error(qryex.Message);
                    logging.Log.Error(qryex.QueryExecuted);
                    throw new Exception("failed to get pending orders data", qryex);
                }
                catch (Exception ex)
                {
                    logging.Log.Error(ex.Message);
                    throw new Exception("failed to get pending orders data", ex);
                }
            }

            return pendingOrderList;
        }

Thanks guys for your help, and have a great weekend