Hello:

I am trying to accomplish writing functions that will generate expression trees that will be run on sql server, and am unsure of the best (or any) way to do this.

I would like to sum of a property in a sub table without fetching that subtable.

as an example, I'd like to do something like this:

var query = from def in linqMetaData.Deficiciency select new { id = def.id, total = def.DeficiencyCostDetail.Sum( dcd => TotalCost(dcd)), }

where....

public static System.Linq.Expressions.Expression <Func<DeficiencyCostDetailEntity, decimal>> TotalCost =(dcd) => (dcd.LaborHours * dcd.LaborQuantity);

I've seen the predicateBuilder... that helps, but I'm having a hard time relating it to my goal.

I've also seen this post: http://tomasp.net/articles/dynamic-linq-queries.aspx

I'm not sure if it's specific to linqToSQL, but looks nice if parameters wouldn't have to be specified explicitly.

I apologize for what I'm sure is a duplicate question.

Thanks, -Ian

Hi Ian,

Have you tried with Function Mappings?

You need function mappings, as David suggested, as the method call is INSIDE a Sum call, which results in a nested scalar query. This means that the method's logic has to be ran inside the DB system so the .NET code of the function is never called (as the linq query is converted to a DB query)

If you call a .NET method directly inside the projection (select), it would work, but in this situation it doesn't: Linq's design is that way, unfortunately.

Thanks for the responses.

That's good to know, on both accounts.

Function mappings seem like a convenient way to run stored procs or direct sql using the LinqMetaData. This definitely helps, but please allow me to ask a followup.

Let's say I would overload (or just create many) totaling methods (just moving the sum into the function): from def in linkMetaData.Deficiencies select new { id = def.id, total = TotalCost(def), }

where... public static System.Linq.Expressions.Expression <Func<DeficiencyEntity, decimal>> TotalCost =(d) => (d.DeficiencyCostDetail.Sum(dcd => dcd.LaborHours + dcd.LaborQuantity));

without fetching DeficiencyCostDetail entities...

Is this possible? What kind of approach would be taken?

Thanks so much, -Ian

That kind of helpermethods will work regardless I think, as you don't really 'map' things, you simply make things easier to write: the expression tree will simply contain the Expression you return from that method, and will be converted into SQL as if you've written the expression inside the query. (so nothing extra is fetched to the client, as it's 1 query).

With function mappings, you can map a given .NET method onto a DB construct, so that's something else than this .

You tried that construct and it failed?

That is exactly what I am trying to accomplish. Just a set of helper calculations that will be used throughout the biz layer.

As is from last post, with TotalCost being of type System.Linq.Expressions.Expression <Func<DeficiencyEntity, decimal>> , I have to do TotalCost.Compile.Invoke(def)

Alternatively, I can have TotalCost being of type Func<DeficiencyEntity, decimal>, and just do TotalCost.Invoke(def)

Either way, I get a stack overflow exception (which I see has probably been fixed), so I guess I will try upgrading to the latest build. But that is what I got when I just called an in memory .NET function within the sum(), so I'm a wee bit skeptical that the expression tree would still have the .NET delegate.

seems related: http://www.atrevido.net/blog/2007/09/06/Complicated+Functions+In+LINQ+To+SQL.aspx

Thanks a lot.

-Ian

If I remove the Invoke(), and just do ... select new { Total = TotalCost.Compile()(def) }

I get (w/ 3.5 version:2.6.8.710; 2.0 version:2.6.8.709) Unable to cast object of type 'System.Int32' to type 'System.String'.

[InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.] lambda_method(ExecutionScope , Object[] , Int32[] ) +507 SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList1.AddRowToResults(IList projectors, Object[] rawProjectionResult) +103 SD.LLBLGen.Pro.LinqSupportClasses.DataProjectorToObjectList1.SD.LLBLGen.Pro. ORMSupportClasses.IGeneralDataProjector.AddProjectionResultToContainer(List1 valueProjectors, Object[] rawProjectionResult) +9 SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List1 valueProjectors, IGeneralDataProjector projector, IDataReader datasource, Int32 maxNumberOfItemsToReturn, Int32 pageNumber, Int32 pageSize, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging, UniqueList1 stringCache, Dictionary2 typeConvertersToRun) +821 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) +120 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) +181 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) +300 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +191 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +20 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +14 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1. System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +369 System.Linq.Enumerable.ToList(IEnumerable`1 source) +54 Tec.TecAms.UI.Web.Secure.Analysis.Default.get_AnalysisQuery() in C:\Projects\GT_Clients\Tec\TecAms\Code\Tec.TecAms\Tec.TecAms.UI.Web\Secure\Analysis\Default.aspx.cs:223 Tec.TecAms.UI.Web.Secure.Analysis.Default.LinqServerModeDataSourceAnalysis_Selecting(Object sender, LinqServerModeDataSourceSelectEventArgs e) in C:\Projects\GT_Clients\Tec\TecAms\Code\Tec.TecAms\Tec.TecAms.UI.Web\Secure\Analysis\Default.aspx.cs:304 DevExpress.Data.Linq.LinqServerModeDataSourceView.OnSelecting(LinqServerModeDataSourceSelectEventArgs e) +108 DevExpress.Data.Linq.LinqServerModeDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +110 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 DevExpress.Web.ASPxClasses.Internal.DataHelper.PerformSelect() +144 DevExpress.Web.ASPxGridView.GridViewDataHelper.PerformSelect() +137 DevExpress.Web.ASPxClasses.Internal.DataContainer.PerformSelect() +117 DevExpress.Web.ASPxClasses.ASPxDataWebControlBase.DataBindCore() +36 DevExpress.Web.ASPxClasses.ASPxDataWebControlBase.DataBind() +143 DevExpress.Web.ASPxGridView.ASPxGridView.DataBind() +204 DevExpress.Web.ASPxClasses.Internal.DataHelperBase.EnsureDataBound(Boolean ensureChildControls) +106 DevExpress.Web.ASPxClasses.Internal.DataHelperBase.EnsureDataBound() +29 DevExpress.Web.ASPxClasses.Internal.DataContainer.EnsureDataBound() +120 DevExpress.Web.ASPxClasses.ASPxDataWebControlBase.OnPreRender(EventArgs e) +39 System.Web.UI.Control.PreRenderRecursiveInternal() +86 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

Compiling makes it a Func<>, not an Expression<Func<>> so that will make it not be part of the expression tree, so that's not what should be done. You also should use the latest runtime lib/linq classes, as you're using an old build.

Ok, yeah... that makes sense.

So, it seems that I have to use an expression in the form of an expression tree to execute on the db server. But, I don't know how else to "invoke" using a parameter (so the func would have something as an argument to use to compute a total).

Therefore, it seems that the helper method would have to be contained in a linq function (like sum). ... select new { Total= inventory.Deficiency.Sum(def => def.DeficiencyCostDetail.Sum<DeficiencyCostDetailEntity>(CalculatorExtensionMethods.TotalCostdcd)), }

I get 3 compile errors: -'Tec.TecAms.DataEntities.Generic.HelperClasses.EntityCollection< Tec.TecAms.DataEntities.Generic.EntityClasses.DeficiencyCostDetailEntity>' does not contain a definition for 'Sum' and the best extension method overload 'System.Linq.Queryable.Sum<TSource>(System.Linq.IQueryable<TSource>, System.Linq.Expressions.Expression<System.Func<TSource,int>>)' has some invalid arguments C:\Projects\GT_Clients\Tec\TecAms\Code\Tec.TecAms\Tec.TecAms.UI.Web\Secure\Analysis\Default.aspx.cs 221

-Instance argument: cannot convert from 'Tec.TecAms.DataEntities.Generic.HelperClasses.EntityCollection< Tec.TecAms.DataEntities.Generic.EntityClasses.DeficiencyCostDetailEntity>' to 'System.Linq.IQueryable<Tec.TecAms.DataEntities.Generic.EntityClasses.DeficiencyCostDetailEntity>' C:\Projects\GT_Clients\Tec\TecAms\Code\Tec.TecAms\Tec.TecAms.UI.Web\Secure\Analysis\Default.aspx.cs 221

-Argument '2': cannot convert from 'System.Linq.Expressions.Expression<System.Func< Tec.TecAms.DataEntities.Generic.EntityClasses.DeficiencyCostDetailEntity,decimal>>' to 'System.Linq.Expressions.Expression<System.Func< Tec.TecAms.DataEntities.Generic.EntityClasses.DeficiencyCostDetailEntity,int>>' C:\Projects\GT_Clients\Tec\TecAms\Code\Tec.TecAms\Tec.TecAms.UI.Web\Secure\Analysis\Default.aspx.cs 221

We upgraded to the october 15(?) build (2.6.8.1001)

I can do inventory.Deficiency.AsQueryable().Sum(...), but that doesn't seem correct, and it also throws a 'cannot convert call to set..." exception when run.

Would a sample project be useful?

Thanks for the patience, -Ian

The first error makes sense, because an entity doesn't have a Sum method of course. If I'm not mistaken, your initial request query was something like "Get all customerIDs and the total sum of their orders", correct?

I've a similar unittest, which looks like:

/// <summary>
/// Gets the maximum order total. This is a test to see if Max works in a single value return setting.
/// </summary>
[Test]
public void GetTheMaximumOrderTotalUsingAnonymousType()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = (from c in metaData.Customer
                select new { c.CustomerId, TotalAllOrders = c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice)) }
                ).Max(n=>n.TotalAllOrders);

        Assert.AreEqual(112804.1900, q);
    }
}

Here, the 1:n relation of customer-order is utilized to issue the Sum. In your situation however, the relation between inventory and Deficiency seems to be 1:1/m:1 ?

the full ddl for this part is:

hierarchy 1:m asset 1:m inventory 1:m deficiency 1:m deficiencyCostDetail

but, for the purpose of the troubleshooting, I simplified earlier tests to total the deficiencyCostDetail for a deficiency.

Your unit test seems exactly like totaling for an inventory.

The first error would make sense if def.DeficiencyCostDetail was an entity, rather than an entity collection.

Sorry for the confusion.

I have attached an image of something that is in between what I need an your predicateBuilder, but for linq to sql.

C# in a nutshell page 316

If I start with the datasource (rather that the from deficiency entity variable), it compiles, but also appears to convert it to a .Net delegate

TotalCost= linqMetaData.DeficiencyCostDetail.Where(dcd => dcd.Deficiency.Inventory.InventoryId == inventory.InventoryId).Sum(CalculatorExtensionMethods.TotalCostdcd),

Unable to cast object of type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.InMemoryEvalCandidateExpression' to type 'System.Linq.Expressions.LambdaExpression'.

Thanks, -Ian

Calling 'Compile' will make it a delegate so not something convertable to an Expression tree later on. I'm a little confused now what the exact query is that you have.

Could you post the full query you're trying to achieve and try first without this method trick and with a query which contains the expression you'd like to wrap inside a method?

sorry. I meant compile in the sense of building a dll. I'm not calling compile or invoke anymore.

abridged queries for testing...

//this compiles and executes var querylinqNormal = from inventory in linqMetaData.Inventory select new { ID = inventory.InventoryId, Total = inventory.Deficiency.Sum(def => def.DeficiencyCostDetail.Sum(dcd => dcd.LaborHours * dcd.LaborQuantity)), };

                int countNormal = querylinqNormal.Count();

                // this does not compile, and gives the 3 errors in previous post
                //var querylinqWithHelper = from inventory in linqMetaData.Inventory
                //               select new
                //               {
                //                   ID = inventory.InventoryId,
                //                   Total = inventory.Deficiency.Sum(def => def.DeficiencyCostDetail.Sum(CalculatorExtensionMethods.TotalCostdcd)),
                //               };

                //int countWithHelper = querylinqWithHelper.Count();


                // this compiles, but throws exception upon execution
                var querylinqWithHelperAndTrick = from inventory in linqMetaData.Inventory
                                                  select new
                                                  {
                                                      ID = inventory.InventoryId,
                                                      Total = linqMetaData.DeficiencyCostDetail.Where(dcd => dcd.Deficiency.Inventory.InventoryId == inventory.InventoryId).Sum(CalculatorExtensionMethods.TotalCostdcd),
                                                  };

                int countWithHelper = querylinqWithHelperAndTrick.Count();

.... public static System.Linq.Expressions.Expression<Func<DeficiencyCostDetailEntity, decimal>> TotalCostdcd = (dcd) => (dcd.LaborHours * dcd.LaborQuantity);

The compile errors might be cause by the fact that you explicitly defined the result of 'Sum' as being decimal, I'm not sure that's the case.

the other error might be caused by an issue in our code: your expression tree is referring to a static member TotalCostdcd (which contains an expression). This is detected as an in-memory candidate (all lambda's which aren't referring to outside elements as well as constants are compiled into code). These candidates are then invoked which produce the actual result, in this case the Expression<Func<>>. In your situation, that's apparently not happening as you're getting an error with an unexpected InMemory candidate expression which hasn't been handled.

I'll try to reproduce it here and look at the expression tree in the various stages to see if we missed a spot.

(edit) the thing is that the .Sum() call is the IEnumerable<T> one, not the IQueryable<T> one. The IEnumerable<T> one accepts a Func<>, the IQueryable accepts an Expression<Func<>>.

Appending .AsQueryable() (or the generic variant) to the source of Sum() makes it crash, as the Call is unexpected (as it can't convert it to something useful, because the member (the entity collection) has to be used as-is).

When I do:

[Test]
public void GetCustomerIDsWithTotalOfOrders()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        System.Linq.Expressions.Expression<Func<OrderDetailEntity, decimal?>> totalFunc = (od) =>od.Quantity * od.UnitPrice;
        var q = from c in metaData.Customer
                select new
                {
                    ID = c.CustomerId,
                    //Total = c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice))
                    Total = c.Orders.Sum(o => o.OrderDetails.Sum(totalFunc))
                };

        foreach(var v in q)
        {
        }
    }
}

I get a simple compile error that the 'Sum''s types can't be inferred and I should specify them, but when I do I run into the problem that it's the IEnumerable one.

The one which does compile but crashes compiles because the 'Where' returns an IQueryable, so the Sum is the IQueryable one. (lovely isn't it, that MS decided that separation of concerns wasn't something they should be paying attention to). The crash comes from the fact that the inmemory candidate expression isn't handled before it's used. This is a special case as normally in that phase of the tree handling, elements are looked at and then handled, but in THIS case, the expression first has to be handled (i.e. called so it returns its true value, the expression<func<>>) and then the result has to be looked at.

(edit). It turns out to be rather deep to get this fixed. Another is that even though we add code for this, you aren't helped that much, because the normal usage query still doesn't work (gives compile errors). So please use the expressions inside the query.