Calculated Colulmn w/ Lookup

[Mark]

I have a GridView with a combobox (cbo).  The cbo has a query lookup source for DisplayMemberPath and SelectedValueMemberPath, both working great.

<telerik:GridViewComboBoxColumn UniqueName="cbo_RawPack" Width="Auto"
                                                DisplayMemberPath="packDesc"
                                                SelectedValueMemberPath="RawPackID"
                                                DataMemberBinding="{Binding RawPackID, Mode=TwoWay}"
                                                SortMemberPath="RawPackID" ex:ComboBox.Mode="Async" />

I'm trying to also create a calculated column based a field from this query ("packLBs") which is not shown in the GV.  Is there a way I can do this cleanly and without hardcoding it?

So far I have:

//Poundage: unitQty * poundBasis
            System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.unitQty;
            (dg_tblPOLines.Columns["ex_Poundage"] as GridViewExpressionColumn).Expression = exp2;

where "poline.unitQty" is a field in the GV.  I then want to extend the formula to multiply that by the combobox lookup field "packLBs".  This column is a calculated field at the database level so I'm trying to remain loosely coupled.

The 'logic' of the statement would look something like:

System.Linq.Expressions.Expression<Func<tblPOLine, qryRawPack, decimal>> exp2 = (poline, rawLine) => poline.unitQty * rawLine.packLBs WHERE (rawLine.RawPackID == poline.RawPackID);

[Maya]

Hello Mark,

You may take a look at the sample project attached in this forum thread for a reference.
 

Best wishes,
Maya
the Telerik team

[Mark]

Hi Maya,

Unfortunately I wasn't able to resolve what I really need with your reference.  I made some database changes so that I now have values (and no longer need the lookup), however I need the Calculated column to base its calculated value on a condition.  For example, what I'm trying to do is bases a value in my property 'tblPOLine.packKilosID' select the equation I'd like to use for the cell on the particular row.

I've tried

System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp2 = (tblPOLine.packKilosID == 1) ? poline => Math.Round((poline.unitQty * poline.packKilosID * 2.2046226m), 0) : poline => Math.Round((poline.unitQty * poline.packKilosID), 0);

however it rejects the 'tblPOLine.packKilosID ==1' with "An object reference is required for the non-static field, method ..."


This is the RIA created class -

    public partial class tblPOLine
    {

        internal sealed class tblPOLineMetadata
        //public class tblPOLineMetadata
        {

            // Metadata classes are not meant to be instantiated.
            private tblPOLineMetadata()
            {
            }

....

            [Display(Name = "Packaging", Description = "Indicate the size of the packaging.")]
            //[Required(ErrorMessage = "A packaging type selection is required.")]
            public decimal packKilosID { get; set; }

....


        }
    }

any additional help is appreciated.  I'm not sure if you can do such a conditional selection.

[Yavor Georgiev]

Hi Mark,

 I'm afraid that the problem arises because you don't supply the expression in the correct form to the compiler. Could you please try declaring your expression like:

Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.packKilosID == 1 ? Math.Round((poline.unitQty * poline.packKilosID * 2.2046226m), 0) :  Math.Round((poline.unitQty * poline.packKilosID), 0);

All the best,
Yavor Georgiev
the Telerik team

[Mark]

Hello Yavor,

Thanks so very much!  ...  This is a terrific "feature" and I would encourage adding it to the some of the documentation of the calculated column as an example.

I appreciate your help as well as Maya's very much.

Mark

[Mark]

Hi Yavor,

Is there a way to use a lookup value in another table based on the poline.packKilosID?  For example in logic

Get the lookup value of tblRawPack.rawPackID associated with this grid line item (poline below).
Based on poline.packKilosID != 1 select the proper function (that part is working well).

Something close to

System.Linq.Expressions.Expression<Func<tblPOLine, tblRawPack, decimal>> exp2 = (poline, rawPack) => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (rawPack.packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * rawPack.packKilos), 0);

The goal here is to convert all choices from pounds to kilograms unless the user selects the first input choice which should not be converted because it is entered in pounds. tblRawPack is a list of bag sizes (e.g. 15kg, 30kg, 60kg, etc.)
 and the condition 'poline.rawPackID = 1' is the PK of the one entry where input is in pounds.

[Yavor Georgiev]

Hi Mark,

 The expression must be of type Func<TParameter, TReturn>, because it only ever takes one parameter (the data item). In fact, I believe you should receive an exception if you supply a LambdaExpression with more than one parameter.

 That said, you can also reference variables and functions local to the scope you are declaring the expression in. Example:

// using a custom class
IRawPackProvider rPP = GetSomeClassThatFetchesRawPacks();
Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (rPP.Get(poline.rawPackID).packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * rPP.Get(poline.rawPackID).packKilos), 0);

// using an anonymous method that you define for brevity
// supposing that 'dbContext' is an EntityFramework context, for example
Func<int, tblRawPack> getPack = id => dbContext.RawPacks.Where(r => r.Id == id).FirstOrDefault();
Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (getPack(poline.rawPackID).packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * getPack(poline.rawPackID).packKilos), 0);

 The ExpressionColumn only know to call the expression you provide with a single parameter - the data item for the row. There is no way to modify the behavior of the column in this case. You can't tell it how to handle the expression, and you shouldn't. You can do the necessary lookup inside the expression. Think of it as code that gets executed whenever the ExpressionColumn needs to show a cell. It can do everything you like, it' just code.

 However, do note that intensive operations such as web service calls and database lookups can take some time to process. Since the expression in the column is executed whenever a cell is brought into view, if it takes a long time to process it can reduce the scrolling performance of your RadGridView. This is why it's best to perform the actual lookups in the Model where you can cache the data.

Regards,
Yavor Georgiev
the Telerik team

[Mark]

Hi Yagor,

I'll give your sample some try, thank you very much.
In respect to Lamba, it has no difficulty with more than one input paramters, it just takes the form of:

Func<T1, T2, Tn, TResult> exp = (var1, var2, varn) =>

[Mark]

Hi Yagor,

This actually worked very well.  I made a schema database change and now use a convPounds property which will allow me to use other unit of measure conversion if need be and be a bit more loosely coupled.

What I've settled on is

//Lookup tblRawPack for calculated column
RoasterWerksDomainContext ctxt = new RoasterWerksDomainContext();
ctxt.Load(ctxt.GetTblRawPacksQuery());
Func<int?, tblRawPack> getPack = id => ctxt.tblRawPacks.Where(r => r.rawPackID == id).FirstOrDefault();
System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp = poLine => Math.Round((poLine.unitQty * (decimal)(getPack(poLine.rawPackID).convPounds)), 0);
            (dg_tblPOLines.Columns["ex_Poundage"] as GridViewExpressionColumn).Expression = exp;

I now want to aggregate a column sum, which I'm assuming will need a custom aggregate.  Can you please give me a little direction?  As there is a lookup involved I'm not quite certain how to proceed.

Thanks again for your assitance.

[Yavor Georgiev]

Hello Mark,

 You can use our AggregateFunction<TElement, TResult>, which just like our ExpressionColumn, takes an expression telling it how to aggregate over a collection of data.

All the best,
Yavor Georgiev
the Telerik team

[Mark]

Hi Yagor,

Thank you once again.  Is there a sample I can reference to see how this is constructed?  I'm having some difficulty in implementing it.

Mark

[Yavor Georgiev]

Hi Mark,

 I believe this should work for you:

RoasterWerksDomainContext ctxt = new RoasterWerksDomainContext();
ctxt.Load(ctxt.GetTblRawPacksQuery());

Func<int?, tblRawPack> getPack = id => ctxt.tblRawPacks.Where(r => r.rawPackID == id).FirstOrDefault();

AggregateFunction<tblPOLine, decimal> aggregateFunction = new AggregateFunction<tblPOLine, decimal>();
aggregateFunction.AggregationExpression = poLines => poLines.Sum(poLine => Math.Round((poLine.unitQty * (decimal)(getPack(poLine.rawPackID).convPounds)), 0));

column.AggregateFunctions.Add(aggregateFunction);

 These generic aggregate functions work in the same way as the ExpressionColumn, although the parameter they take is a collection, rather than a single data item.

Greetings,
Yavor Georgiev
the Telerik team

[Mark]

Hi Yavor,

Sure did, thanks for your troubles.

Best,
Mark