Congratulations on the beta of Linq to LLBLGen Pro. While converting an existing Linq to Sql project I stumbled on issue I can not get to work. In Linq to Sql you can set DataLoadOptions to filter on related tables. It also filters Sum() etc. I tried to set a filter using the WithPath extension, but it does not seem to filter the Sum() values. Is there a way to do this in Linq to LLBLGen Pro?

Thanks

Please give an example. LoadWith<T> doesn't allow filters, only AssociateWith does in Linq to Sql, so please give a query you want to convert.

If I understand your post correctly, you want to filter the related entities on a filter on Sum(), which should work.

I'm trying to convert this:

      DataLoadOptions dlo = new DataLoadOptions();
      dlo.AssociateWith<Item>(i => i.Stocks.Where(s => s.WarehouseID == ddlWarehouse.SelectedValue));
      ctx.LoadOptions = dlo;

    var result = from i in ctx.Items
                 select new
                 {
                   i.ID,
                   i.ProductNo,
                   i.Description,
                   i.MinStockLevel,
                   i.MaxStockLevel,
                   QtyInStock = (int)(i.Stocks.Sum(s => s.Quantity) ?? 0)
                 };

QtyInStock should be set to the total quantity in stock for the selected warehouse.

Thanks.

That's a fetch of a list of anonymous types, not entities, so 'AssociatedWith' isn't used (that's used when fetching hierarchies of entities).

What have you tried with linq to llblgen pro which failed? (so you don't need withpath here, as you're fetching a list of anonymous types, not entities)

I dont understand you. The code I posted does filter the Stock entity, so the AssociateWith does do something.

I tried this same construction with Linq to LLBLGen Pro, but since I could not find DataLoadOptions I tried WithPath, but the code below shows the total value for QtyInStock instead of zero (Warehouse xxx obviously does not exist).

      LinqMetaData metaData = new LinqMetaData();

      var items = from item in metaData.Item.WithPath(new PathEdge<StockEntity>(ItemEntity.PrefetchPathStock, w => w.WarehouseId == "xxx"))
                   select new
                   {
                     item.Id,
                     item.ProductNo,
                     item.Description,
                     item.MinStockLevel,
                     item.MaxStockLevel,
                     QtyInStock = item.Stock.Sum(s => s.Quantity)
                   };

      ObjectDumper.Write(items);

I also tried (without the WithPath)

QtyInStock = item.Stock.Where(s => s.WarehouseId == "xxx").Sum(s => s.Quantity)

but that resulted in exceptions.

WithPath fetches prefetch paths, and as you're fetching a new type (or better: a dynamic list in v2.x terms of LLBLGen Pro) you're not fetching entities, so withpath / prefetch paths are ignored, as you don't fetch entities, and prefetch paths are only used when fetching entities.

If you want to filter on a related entity, you have to place the filter you have in AssociateWith inside the linq query itself. so from item in metaData.Item where item.Stocks.Contains(s => s.WarehouseID == ddlWarehouse.SelectedValue) select {...}

or something like that (from my bare head, so no testing here)

Shouldnt this

      LinqMetaData metaData = new LinqMetaData();

      var items = from item in metaData.Item
                  select new
                  {
                    item.Id,
                    item.ProductNo,
                    item.Description,
                    item.MinStockLevel,
                    item.MaxStockLevel,
                    QtyInStock = item.Stock.Where(s => s.WarehouseId == "xxx").Sum(s => s.Quantity)
                  };

run without this exception?

Onverwerkte uitzondering: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.ItemID" could not be bound.
The multi-part identifier "LPLA_2.WarehouseID" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "LPLA_2.Ite
mID" could not be bound.
The multi-part identifier "LPLA_2.WarehouseID" could not be bound.
   bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   bij System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   bij System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   bij System.Data.SqlClient.SqlDataReader.get_MetaData()
   bij System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bij System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   bij System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   bij System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bij System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bij System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bij System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   bij SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   --- Einde van intern uitzonderingsstackpad ---
   bij SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   bij SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
   bij SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute)
   bij SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItem
sToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProvider.cs:regel 165
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:regel 234
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:regel 92
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProProviderBase.cs:regel 66
2
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProQuery.cs:regel 84
   bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\RuntimeLibraries 2.6 .NET 2.x\LinqSupportClasses\LLBLGenProQuery.cs:regel 148
   bij ObjectDumper.WriteObject(String prefix, Object o) in G:\Projects\VS2008\Projects\Test\LLBLGenPro\LLBLGenPro\ObjectDumper.cs:regel 62
   bij ObjectDumper.Write(Object o, Int32 depth, TextWriter log) in G:\Projects\VS2008\Projects\Test\LLBLGenPro\LLBLGenPro\ObjectDumper.cs:regel 21
   bij ObjectDumper.Write(Object o, Int32 depth) in G:\Projects\VS2008\Projects\Test\LLBLGenPro\LLBLGenPro\ObjectDumper.cs:regel 15
   bij ObjectDumper.Write(Object o) in G:\Projects\VS2008\Projects\Test\LLBLGenPro\LLBLGenPro\ObjectDumper.cs:regel 11
   bij LLBLGenPro.Program.Main(String[] args) in G:\Projects\VS2008\Projects\Test\LLBLGenPro\LLBLGenPro\Program.cs:regel 30

After removing Where the query runs fine.

Thanks.

twaindev wrote:

Shouldnt this

      LinqMetaData metaData = new LinqMetaData();

      var items = from item in metaData.Item
                  select new
                  {
                    item.Id,
                    item.ProductNo,
                    item.Description,
                    item.MinStockLevel,
                    item.MaxStockLevel,
                    QtyInStock = item.Stock.Where(s => s.WarehouseId == "xxx").Sum(s => s.Quantity)
                  };

run without this exception?

It should.

item - Stock is 1:n relation?

Will look into it.

Yes, Item - Stock is 1:n relation. And I was using yesterday's build.

Reproduced:

LinqMetaData metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
        select new
        {
            o.OrderId,
            o.OrderDate,
            AmountProductsBought = o.OrderDetails.Where(od=>od.UnitPrice<2.0M).Sum(od=>od.Quantity)
        };

edit: looks like the alias isn't specified for the derived table inside the scalar query in the select (the source of the sum, if you look closely at the query). Will look into it.

Great! When this is solved I can convert the whole project and do further testing.

I've fixed it. Was an alias passing issue when a projection was created for a derived table: it always passed an empty alias, which was stupid.

I'll attach a new build for you to test. Attached

This runs fine But now I have an issue with TakePage for which I will open a new thread.

Thanks

twaindev wrote:

This runs fine But now I have an issue with TakePage for which I will open a new thread.

Thanks

No problem, I'll close this one.