Hello,

I'm trying to create a calculated field on an entity collection from a query like this;

select

( select MIN(ActualDateTime) FROM blotterTrade where blotterid = a.blotterid and blotterTradeid = a.blotterTradeid ) created, *

from blotterTrade a

How can I create the 'created' field?

Thanks,

kza

Create a view is the only way I know how.

There's a way (of course!) .

See: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

The article is fairly long, but it illustrates how to obtain a calculated scalar query inside an entity using a special factory.

Exactly what I needed. Thanks much Frans.

kza

I've run into another problem. I need to build the field to work as follows;

SELECT   *,
                          (
                            SELECT    ActualDateTime
                            FROM          BlotterTrade a
                            WHERE (ActualDateTime IN    
                                          (SELECT    MIN(ActualDateTime) AS ActualDateTime
                                            FROM          BlotterTrade AS bt
                                            WHERE   
                            (BlotterTradeID = BlotterTrade.BlotterTradeID) AND (BlotterID = 'hedge') 
                                            )
                                    )
                            ) created

FROM         BlotterTrade
WHERE    (ActualDateTime IN
                          (SELECT    MAX(ActualDateTime) AS ActualDateTime
                            FROM          BlotterTrade AS bt
                            WHERE     (BlotterTradeID = BlotterTrade.BlotterTradeID) AND (BlotterID = 'hedge') AND (BlotterTrade.ActualDateTime >
                                                       (SELECT   MAX(CutoffActualDateTime) AS CutoffActualDateTime
                                                         FROM         BlotterDate
                                                         WHERE    (BlotterID = 'hedge') AND (BlotterDate < '12/14/2006'))) AND (BlotterTrade.ActualDateTime <=
                                                       (SELECT   MAX(CutoffActualDateTime) AS CutoffActualDateTime
                                                         FROM         BlotterDate
                                                         WHERE    (BlotterID = 'hedge') AND (BlotterDate = '12/14/2006'))))) OR
                      (ActualDateTime IN
                          (SELECT    MAX(ActualDateTime) AS ActualDateTime
                            FROM          BlotterTrade AS bt
                            WHERE     (BlotterTrade.BlotterDate = '12/14/2006') AND (BlotterTrade.BlotterTradeID = BlotterTradeID) AND (BlotterTrade.BlotterID = 'hedge')))
ORDER BY BlotterDate DESC, ActualDateTime DESC

I'm having problems writing an expression for the 'created' field. Can you get me started on the expression I need for this?

Thanks,

kza

That's a ScalarQueryExpression, where you use a FieldCompareSetPredicate in the filter. What did you try yourself ? Perhaps you made a small error we can correct.

Thanks again Frans. I'm still trying to master the predicate system... this is what I came up with for the 'created' field;

public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);

            IEntityField2 blotterId = EntityFieldFactory.Create(BlotterTradeFieldIndex.BlotterId);
            IEntityField2 blotterTradeId = EntityFieldFactory.Create(BlotterTradeFieldIndex.BlotterTradeId);
            IEntityField2 actualTime1 = EntityFieldFactory.Create(BlotterTradeFieldIndex.ActualDateTime);
            IEntityField2 actualTime2 = EntityFieldFactory.Create(BlotterTradeFieldIndex.ActualDateTime);

            actualTime1.ObjectAlias = "a";
            actualTime2.ObjectAlias = "b";
            blotterId.ObjectAlias = "b";
            blotterTradeId.ObjectAlias = "b";

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add((PredicateExpression)(BlotterTradeFields.BlotterTradeId == blotterTradeId 
                & BlotterTradeFields.BlotterId == blotterId));

            FieldCompareSetPredicate subQuery = new FieldCompareSetPredicate(actualTime1, null,
                actualTime2, null, SetOperator.In, predicate);

            IEntityField2 scalarField = new EntityField2("Created", new ScalarQueryExpression(actualTime1, subQuery));

            toReturn.DefineField(scalarField, toReturn.Count - 1);
            return toReturn;
        }

Last question. Can I sort by this newly added field?

Thanks,

Kza

Sure, on the db: you pass a SortExpression, where you create a new SortClause based on the scalar field you obtain from the factory: create an entityfields object for this entity and simply use the Scalar query field from that entityfields collection

on the client, you can sort the entityview like you'd sort on any other field.

I was looking at modifying the FieldIndex enum and Fields collection etc... Thanks again for the direction.

kza