I am using v2.0 (March 31st 2007). I wanted to know what the process would be in order to add a calculated property to a custom entity class. Example:

Company -> Orders (1->*)

Say I wanted to have a SumOfOrders(Cost) calculated property in the Company Entity. What are the steps to make that kind of addition?

I understand this can be done using typed views, but it is simply better for me in this project to use the actual entity class.

-Seth

As far as I know you have 2 options here:

1.** Add a property to your Entity**. You can do this at the CustomCode section or in a partial class. (Ref. LLBLGenPro Help - Using generated code - Adding your own code to the generated classes). The prerequisite is that the orders collection bust be prefetched.

/// <summary>
/// Return the total cost of the orders of this customer.
/// Note: CustomerEntity.Orders must be prefeched so this value return a real one.
/// </summary>
public Nullable<int> NumberOfOrders
{
    get
    {
        // assume that the orders have been prefetched, if not, you can fetch them here...
        decimal sumOfOrders = 0;
        foreach (OrdersEntity order in this.Orders)
        {
            // here I assumed OrdernEntity has a Total field that have the total of the items.
            sumOfOrders += order.Total;
        }

        return sumOfOrders;
    }
}

2. If you use the entity only to show the data (no save) you can create a custom EntityFactory extending your entityFactory, here you put this custom field so when the data will fetched, your custom field as well. This article illustrates (among other things) how to obtain a calculated scalar query inside an entity using a special factory. http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Thanks for the reply. Here is the code I used in the extended entity factory:

        public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            int fieldCount = toReturn.Count;
            toReturn.Expand(4);
            IEntityField2 totalBilled = new EntityField2("TotalBilled",
                    new ScalarQueryExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(totalBilled, fieldCount);

            IEntityField2 lastBilled = new EntityField2("LastBilled",
                    new ScalarQueryExpression(BillingFields.Date.SetAggregateFunction(AggregateFunction.Max),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(lastBilled, ++fieldCount);

            IEntityField2 expenses = new EntityField2("Expenses",
                    (ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost),
                    AggregateFunction.Sum);

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(expenses,
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalExpense, ++fieldCount);

            IEntityField2 totalPayments = new EntityField2("TotalPayments",
                    new ScalarQueryExpression(PaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == PaymentFields.JobId)));

            toReturn.DefineField(totalPayments, ++fieldCount);

            return toReturn;
        }

When using the new entity factory there is an error:

An exception was caught during the execution of a retrieval query: Invalid column name 'dbo'. Invalid column name 'dbo'. Invalid column name 'dbo'. Invalid column name 'dbo'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I am guessing that the following code is the source of the problem:

            IEntityField2 expenses = new EntityField2("Expenses",
                    (ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost),
                    AggregateFunction.Sum);

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(expenses,
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalExpense, ++fieldCount);

Am I missing something in the aggregate?

-Seth

Hi Seth. Some test in that scenario reflects that if you reuse such a fieldexpression in another field, the generated sql duplicates the expression inside the another expression (.... as expenses, ....(as expenses)...as totalExpenses). So I recommend that you rewrite the expression in the TotalExpenses field.

Thanks for the reply! I tried this, but to no avail; what do you suggest?

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(
                    ((IEntityField2)(ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost)).SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));

-Seth

I tried this, but to no avail; what do you suggest?

Same exception or what?

Would you please examine the generated SQL Query and post it here? Thanks.

The error in this instance was a Casting Exception; could not cast the expression to IEntityField2. -Seth

Please try the following code:

            IEntityField2 expenses = ExpenseFields.LaborHours; // any field from the ExpenseFields
            expenses.Alias = "Expenses";
            expenses.SetExpression(new Expression((ExpenseFields.LaborHours * ExpenseFields.LaborRate + ExpenseFields.MaterialCost));
            expenses.SetAggregateFunction(AggregateFunction.Sum);

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(expenses,
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalExpense, ++fieldCount);

If this doesn't work, would you please post the corresponding generated query.

There was a compile time error with the following code:

            IEntityField2 expenses = ExpenseFields.LaborHours; // any field from the ExpenseFields
            expenses.Alias = "Expenses";
            Expression exp = new Expression((ExpenseFields.LaborHours * ExpenseFields.LaborRate) + ExpenseFields.MaterialCost);
            expenses.SetExpression(exp);
            expenses.SetAggregateFunction(AggregateFunction.Sum);

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(expenses,
                    (JobFields.JobId == ExpenseFields.JobId)));

The error:

Warning 1 Type in the data at line 132, position 5, cannot be loaded because it threw the following exception during construction: Object must implement IConvertible. Line 132, position 5. 132 0

I tried playing with the parentheses to no avail on the Expression object.

-Seth

I didn't test it. Is the following line that's causing the exception?

            Expression exp = new Expression((ExpenseFields.LaborHours * ExpenseFields.LaborRate) + ExpenseFields.MaterialCost);

If so, try the following instead:

            Expression innerExpression  = new Expression(ExpenseFields.LaborHours * ExpenseFields.LaborRate);
            Expression exp = new Expression(innerExpression + ExpenseFields.MaterialCost);

Thanks for the reply! Here is the code I used:

            IEntityField2 expenses = ExpenseFields.LaborHours; // any field from the ExpenseFields
            expenses.Alias = "Expenses";
            Expression innerExpression = new Expression(ExpenseFields.LaborHours * ExpenseFields.LaborRate);
            Expression exp = new Expression(innerExpression + ExpenseFields.MaterialCost);
            expenses.SetExpression(exp);
            expenses.SetAggregateFunction(AggregateFunction.Sum);

            IEntityField2 totalExpense = new EntityField2("TotalExpenses",
                    new ScalarQueryExpression(expenses,
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalExpense, ++fieldCount);

Here is the compile time error:

Error 1 The best overloaded method match for 'SD.LLBLGen.Pro.ORMSupportClasses.Expression.Expression( SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore)' has some invalid arguments C:\Projects\JobTracker\JobTracker.Data\CustomDatabaseGeneric\ExtendedJobEntityFactory.cs 35 42 JobTracker.Data

and

Error 2 Argument '1': cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.Expression' to 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore' C:\Projects\JobTracker\JobTracker.Data\CustomDatabaseGeneric\ExtendedJobEntityFactory.cs 35 57 JobTracker.Data

Thanks.

-Seth

Please change this

Expression innerExpression = new Expression(ExpenseFields.LaborHours * ExpenseFields.LaborRate);
Expression exp = new Expression(innerExpression + ExpenseFields.MaterialCost);

to this:

Expression exp = new Expression((ExpenseFields.LaborHours * ExpenseFields.LaborRate) + ExpenseFields.MaterialCost);

I did try that. Hmm. Can I add two fields: One for sum of Labor (Labor * Hours) and another for Sum of Materials?

-Seth

I'm confused, do you mean you tried that and it didn't work? If so what error occurred?

What I meant was that it did not compile. I tried to simplify the whole thing by adding two more columns to the Expense table (LaborCost and TotalCost) which are calculated in the app. I re-did the ExtendedEntityFactory to:

using System;
using System.Collections.Generic;
using System.Text;
using JobTracker.Data.FactoryClasses;
using JobTracker.Data.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace JobTracker.Data.FactoryClasses
{
    [Serializable]
    public class ExtendedJobEntityFactory : JobEntityFactory
    {
        /// <summary>
        /// Create the fields for the job entity and adjust the fields collection
        /// with the entity field object which contains the scalar expressions
        /// </summary>
        /// <returns></returns>
        public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            int fieldCount = toReturn.Count;
            toReturn.Expand(5);

            #region TotalBilled
            IEntityField2 totalBilled = new EntityField2("TotalBilled",
                    new ScalarQueryExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(totalBilled, fieldCount);
            #endregion

            #region LastBilled
            IEntityField2 lastBilled = new EntityField2("LastBilled",
                    new ScalarQueryExpression(BillingFields.Date.SetAggregateFunction(AggregateFunction.Max),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(lastBilled, ++fieldCount);
            #endregion

            #region TotalLabor
            IEntityField2 totalLabor = new EntityField2("TotalLabor",
                    new ScalarQueryExpression(ExpenseFields.LaborCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalLabor, ++fieldCount);
            #endregion

            #region TotalMaterialCost
            IEntityField2 totalMaterialCost = new EntityField2("TotalMaterialCost",
                    new ScalarQueryExpression(ExpenseFields.MaterialCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));
            #endregion

            #region TotalPayments
            IEntityField2 totalPayments = new EntityField2("TotalPayments",
                    new ScalarQueryExpression(PaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == PaymentFields.JobId)));

            toReturn.DefineField(totalPayments, ++fieldCount);
            #endregion

            return toReturn;
        }
    }
}

and added the following code to the partial class of the JobEntity

            get
            {
                object value = this.Fields["TotalBilled"].CurrentValue;
                if (value != null)
                {
                    return Convert.ToDecimal(value);
                }
                else
                {
                    return null;
                }
            }
        }

        public Nullable<DateTime> LastBilled
        {
            get
            {
                object value = this.Fields["LastBilled"].CurrentValue;
                if (value != null)
                {
                    return Convert.ToDateTime(value);
                }
                else
                {
                    return null;
                }
            }
        }

        public Nullable<decimal> TotalLabor
        {
            get
            {
                object value = this.Fields["TotalLabor"].CurrentValue;
                if (value != null)
                {
                    return Convert.ToDecimal(value);
                }
                else
                {
                    return null;
                }
            }
        }

        public Nullable<decimal> TotalMaterialCost
        {
            get
            {
                object value = this.Fields["TotalMaterialCost"].CurrentValue;
                if (value != null)
                {
                    return Convert.ToDecimal(value);
                }
                else
                {
                    return null;
                }
            }
        }

        public Nullable<decimal> TotalPayments
        {
            get
            {
                object value = this.Fields["TotalPayments"].CurrentValue;
                if (value != null)
                {
                    return Convert.ToDecimal(value);
                }
                else
                {
                    return null;
                }
            }
        }

This is where I am implementing the extended entity factory:

            Console.WriteLine("Loading adapter...");
            IDataAccessAdapter adapter = DataAccessAdapterFactory.Create();
            Console.WriteLine("Opening Adapter...");

            IEntityFactory2 jobFactory = new ExtendedJobEntityFactory();
            EntityCollection<JobEntity> jobCollection = new EntityCollection<JobEntity>(jobFactory);
            EntityCollection<ExpenseEntity> expenseCollection = new EntityCollection<ExpenseEntity>(new ExpenseEntityFactory());
            adapter.OpenConnection();
            adapter.FetchEntityCollection(jobCollection, null);
            adapter.FetchEntityCollection(expenseCollection, null);
            AppSettingsObject o = AppSettingsObject.Default;

            Console.WriteLine("Setting Expense Fields");
            foreach (ExpenseEntity expense in expenseCollection)
            {
                expense.LaborCost = expense.LaborHours.GetValueOrDefault(0) * expense.LaborRate.GetValueOrDefault(0);
                expense.TotalCost = (expense.LaborHours.GetValueOrDefault(0) * expense.LaborRate.GetValueOrDefault(0)) + expense.MaterialCost.GetValueOrDefault(0);
                adapter.SaveEntity(expense, true);
                Console.WriteLine("Setting Aggregates for Expense id: " + expense.ExpenseId.ToString());
            }

            Console.WriteLine("Setting Aging on Jobs");
            foreach (JobEntity job in jobCollection)
            {
                job.Overhead = o.Overhead.getPercentByRate(job.TotalExpenses);
                job.IsAging = (job.TotalBilled.GetValueOrDefault(0) > job.TotalPayments.GetValueOrDefault(0));
                adapter.SaveEntity(job, true);
                Console.WriteLine("Job: " + job.Name + "\n\t[Overhead: \t" + job.Overhead.GetValueOrDefault(0).ToString("p") + ", IsAging\t" + job.IsAging + "]");
            }

            Console.WriteLine("Closing Connection!");
            adapter.CloseConnection();
            Console.WriteLine("Done!");
            Console.ReadKey();

There is a runtime error at this line:

adapter.FetchEntityCollection(jobCollection, null);

The error is a NullReferenceException with the following output:

System.NullReferenceException was unhandled Message="Object reference not set to an instance of an object." Source="SD.LLBLGen.Pro.DQE.SqlServer.NET20" StackTrace: at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, 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, IDbConnection 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, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket) at JobTrackerNormalizer.Program.Main(String[] args) in C:\Projects\JobTracker\JobTrackerNormalizer\Program.cs:line 27 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

I don't understand what I am missing.

-Seth

public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            int fieldCount = toReturn.Count;
            toReturn.Expand(5);

            #region TotalBilled
            IEntityField2 totalBilled = new EntityField2("TotalBilled",
                    new ScalarQueryExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(totalBilled, fieldCount);
            #endregion

            #region LastBilled
            IEntityField2 lastBilled = new EntityField2("LastBilled",
                    new ScalarQueryExpression(BillingFields.Date.SetAggregateFunction(AggregateFunction.Max),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(lastBilled, ++fieldCount);
            #endregion

            #region TotalLabor
            IEntityField2 totalLabor = new EntityField2("TotalLabor",
                    new ScalarQueryExpression(ExpenseFields.LaborCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalLabor, ++fieldCount);
            #endregion

            #region TotalMaterialCost
            IEntityField2 totalMaterialCost = new EntityField2("TotalMaterialCost",
                    new ScalarQueryExpression(ExpenseFields.MaterialCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));
            #endregion

            #region TotalPayments
            IEntityField2 totalPayments = new EntityField2("TotalPayments",
                    new ScalarQueryExpression(PaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == PaymentFields.JobId)));

            toReturn.DefineField(totalPayments, ++fieldCount);
            #endregion

            return toReturn;
        }

You are missing a DefineField() call. You are expanding the fields y 5 more fields, and you only add 4. TotalMaterialCost is not added.

Thanks! I added the correct code (I can't believe I missed it!). When saving the JobEntity with the extended factory I get the following exception on one of the Jobs:

System.IndexOutOfRangeException was unhandled Message="Index was outside the bounds of the array." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.ConstructFieldsToUpdateList(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, List1& fieldsToUpdate, List1& persistenceInfoFieldsToUpdate) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSingleTargetUpdateDQ(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate updateFilter) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateUpdateDQ(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, List1 pkFilters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateUpdateDQ(IEntity2 entityToSave, IFieldPersistenceInfo[] persistenceInfoObjects, List1 pkFilters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave) at JobTrackerNormalizer.Program.Main(String[] args) in C:\Projects\JobTracker\JobTrackerNormalizer\Program.cs:line 45 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

Here is the corrected code:

        public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            int fieldCount = toReturn.Count;
            toReturn.Expand(5);

            #region TotalBilled
            IEntityField2 totalBilled = new EntityField2("TotalBilled",
                    new ScalarQueryExpression(BillingFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(totalBilled, fieldCount);
            #endregion

            #region LastBilled
            IEntityField2 lastBilled = new EntityField2("LastBilled",
                    new ScalarQueryExpression(BillingFields.Date.SetAggregateFunction(AggregateFunction.Max),
                    (JobFields.JobId == BillingFields.JobId)));

            toReturn.DefineField(lastBilled, ++fieldCount);
            #endregion

            #region TotalLabor
            IEntityField2 totalLabor = new EntityField2("TotalLabor",
                    new ScalarQueryExpression(ExpenseFields.LaborCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalLabor, ++fieldCount);
            #endregion

            #region TotalMaterialCost
            IEntityField2 totalMaterialCost = new EntityField2("TotalMaterialCost",
                    new ScalarQueryExpression(ExpenseFields.MaterialCost.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == ExpenseFields.JobId)));

            toReturn.DefineField(totalMaterialCost, ++fieldCount);
            #endregion

            #region TotalPayments
            IEntityField2 totalPayments = new EntityField2("TotalPayments",
                    new ScalarQueryExpression(PaymentFields.Amount.SetAggregateFunction(AggregateFunction.Sum),
                    (JobFields.JobId == PaymentFields.JobId)));

            toReturn.DefineField(totalPayments, ++fieldCount);
            #endregion

            return toReturn;
        }
    }

and here is the line where it fails (on a particular job):

adapter.SaveEntity(job);

I appreciate your help!

-Seth

Seth, I put it at my first reply:

daelmo wrote:

1. If you use the entity only to show the data (no save) you can create a custom EntityFactory extending your entityFactory, here you put this custom field so when the data will fetched, your custom field as well. This article illustrates (among other things) how to obtain a calculated scalar query inside an entity using a special factory. http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

only to show the data (no save) The article shows only the fetch functionality. I'm afraid no save is possible at that scenario.

I understand that. But I am not trying to save any of the extended entity fields. I just want to save the regular properties while at the same time fetching the extended properties. Is this not possible?

Additionally, this worked for some of the entities, why would it not work for others?

-Seth

I just want to save the regular properties while at the same time fetching the extended properties. Is this not possible?

Then you might need to use 2 entities, one for saving with the normal factory, and the other for fetching the newly saved entity with the extended factory.

Then you might need to use 2 entities, one for saving with the normal factory, and the other for fetching the newly saved entity with the extended factory

If I modify the entityfactory as per Frans' post, will that render my entire entitycollection as read-only?

Or was this a coding error?

JRR wrote:

If I modify the entityfactory as per Frans' post, will that render my entire entitycollection as read-only?

The entityCollection wont be read-only, however entityCollection wont be two-way ready. The article is intended to show a one-way extended functionallity.

Thanks for the quick reply, Daelmo!

entityCollection wont be two-way ready

This would be cool, though, a way to extend an entity by adding a virtual field as an sql expression - without giving up two-way data access.

I've had to settle for calculated columns in SQL Server to get this functionality, so it's not all bad.

Is it possible to achieve the same using the current LLBLGen lib and Linq? Let's say I want to know about the amount of orders a customer has but not fetch them from the database.

Regards, André

Hi Andre

First - can we ask you to open new threads rather than re-opening old ones, it makes life simpler for us

Second - The only way to know how many orders a customer has without querying it from the database is to store the calculated value in the database against the customer - is this what you mean ?

Or do want to perform a "SELECT COUNT(*) FROM ORDERS WHERE CustomerID = x" at the same time the customer entity is retrieved...?

Matt