This runs fine now:

      var items = from item in metaData.Item
                  select new
                 {
                   item.Id,
                   item.Description,
                   QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                   QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity) ?? 0,
                   QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity) ?? 0
                 } into result
                  where result.QtyInStock > 0
                  select result;

But this

      var items = (from item in metaData.Item
                   select new
                  {
                    item.Id,
                    item.Description,
                    QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                    QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity) ?? 0,
                    QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity) ?? 0
                  } into result
                   where result.QtyInStock > 0
                   select result).TakePage(1,5);

results in this exception

Onverwerkte uitzondering: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: Internal error: QueryExpression of non-entity type without projection encountered without prior source with projection. bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.CreateProjectionForQueryIfNoProjectionAvailable(QueryExpression toExecute) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.IEnumerable.GetEnumerator() 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 31

After eliminating the Where clause this runs fine again

      var items = (from item in metaData.Item
                   select new
                  {
                    item.Id,
                    item.Description,
                    QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                    QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity) ?? 0,
                    QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity) ?? 0
                  } into result
                   //where result.QtyInStock > 0
                   select result).TakePage(1,5);

but taking the second page with .TakePage(2,5) results in this exception

Onverwerkte uitzondering: System.MissingMethodException: Constructor voor type <>f__AnonymousType05[[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, P ublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=2.0.0. 0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] is niet gevonden. bij System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) bij System.Activator.CreateInstance(Type type, Object[] args) bij SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.CreateNewInstance(List1 ctorArguments) bij SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.AddProjectionResultToContainer(IList projectors, Object[] rawProjectionResult) bij SD.LLBLGen.Pro.ORMSupportClasses.DataProjectorToCustomClass1.SD.LLBLGen.Pro. ORMSupportClasses.IGeneralDataProjector.AddProjectionResultToContainer(List1 valueProjectors, Object[] rawProjectionResult) bij SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List1 valueProjectors, IGeneralDataProjector projector, IDataReader datasource, Int32 maxNumberOfItemsToReturn, Int32 pageNumber, Int32 pageSize, Boolean clientSideLimitati on, Boolean clientSideDistinctFiltering, Boolean clientSidePaging) bij SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute) bij SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 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) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() bij SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator() 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 31

After disabling QtyInbound and QtyOutbound this runs fine again

      var items = (from item in metaData.Item
                   select new
                  {
                    item.Id,
                    item.Description,
                    QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                    //QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity) ?? 0,
                    //QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity) ?? 0
                  } into result
                   //where result.QtyInStock > 0
                   select result).TakePage(2,5);

as does .TakePage(3,5) and so on.

I hope this illustrates enough for you to determine what is causing the exceptions.

Thanks

The second exception comes from the fact that a row has 1 or more values of a different type than the parameter in the CTor.

it's a little tricky to debug though, as you need to have a breakpoint in the DataProjectorToCustomClass<T>, which is in the runtime lib. The query executed for the page 2, 5, if you execute it on the DB directly, does it reveal any weird values? The coalesce statements should make sure '0' is returned for nulls.

The first exception is odd, as there's code in place which coerces expressions to queries if they're not yet a query. Will check it out.

I'm thinking of adding a way to specify your own projector class so it's easier to debug. I'll see if I can add type checkers in teh instantiation routine. This will cost performance though..

The query executed for the page 2, 5, if you execute it on the DB directly, does it reveal any weird values? The coalesce statements should make sure '0' is returned for nulls.

After removing '?? 0' this query runs fine for all pages, but I would like to have 0 assigned when null is returned.

twaindev wrote:

The query executed for the page 2, 5, if you execute it on the DB directly, does it reveal any weird values? The coalesce statements should make sure '0' is returned for nulls.

After removing '?? 0' this query runs fine for all pages, but I would like to have 0 assigned when null is returned.

I think '0' should be in the type of the result of 'Sum'. What's the type of Quantity?

Otis wrote:

I think '0' should be in the type of the result of 'Sum'. What's the type of Quantity?

Quantity was defined nullable in sql server, so the type was int?. After unchecking nullable the type is now int and 0 is returned. This is OK. So only the issue with the Where clause remains open.

Thanks.

When I change the query to use this

Description = item.Description ?? ""

it runs into the same problem.

.TakePage(1,5) is ok. .TakePage(2,5) is exception.

Description is also defined nullable, but this one I can not change.

twaindev wrote:

Otis wrote:

I think '0' should be in the type of the result of 'Sum'. What's the type of Quantity?

Quantity was defined nullable in sql server, so the type was int?. After unchecking nullable the type is now int and 0 is returned. This is OK. So only the issue with the Where clause remains open.

Thanks.

Not really, this is an issue. I think the projector class has to have logic which can deal with these kind of situations, as otherwise it's cumbersome for the developer to understand what's wrong.

twaindev wrote:

When I change the query to use this

Description = item.Description ?? ""

it runs into the same problem.

.TakePage(1,5) is ok. .TakePage(2,5) is exception.

You have an item which has a NULL for Description? If not, strange exception, but I think it's reproducable pretty quickly. I'll look into it shortly

You have an item which has a NULL for Description? If not, strange exception, but I think it's reproducable pretty quickly. I'll look into it shortly

Yes, descriptions are nullable. Special requirement.

Reproduced first issue.

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 < 10.0M).Sum(od => od.Quantity)
        } into result
        where result.AmountProductsBought > 1
        select result).TakePage(1,5);

(edit) found the cause, fixed it. Now run into some very weird type issue on the second page ('OrderId' is seen as 'bigint' for the temptable (I'm testing on sqlserver2000)), looking into that now, then your coalesce issue.

Ok finally nailed that one. It's a troubling issue though... I had to go as far as to use 'ntext' and 'image' fields in the temp table if string or byte[] fields are used, as the length is unknown (only the .net type).

Ok, now going to look into the issue brought up with the coalesce statement.

OK, great! Dont forget to eat something We will chat more later this evening.

twaindev wrote:

OK, great! Dont forget to eat something We will chat more later this evening.

I've re-created the database on the sqlserver 2005 box and it now works as in: returns data. I can't repro the second error, so I'll attach the tempbuild again.

Please use all 3 files. Let me know if you still run into the issue. If so, we need more details like table setup, database type etc. etc. so in short: a repro case.

Let me know if you still run into the issue.

Hi, I played with this query

      using (DataAccessAdapter adapter = new DataAccessAdapter())
      {
        LinqMetaData metaData = new LinqMetaData(adapter);

        var items = (from item in metaData.Item
                     where item.CustomerId == "123456"
                     select new
                    {
                      item.Id,
                      Description = item.Description ?? "",
                      QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                      QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity),
                      QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity)
                    } into result
                     where result.QtyInStock > 0
                     select result).TakePage(5,15);

        ObjectDumper.Write(items);
      }

a bit and it runs without any problems now! Great work!

I will now try to bind this to an ASP.NET ListView with DataPager.

Thanks.

Also with the coalesce operators on the sum fields?

(I'll reply more tomorrow, it's getting late )

Yep, after reverting the quantity fields to nullable this also runs fine.

      using (DataAccessAdapter adapter = new DataAccessAdapter())
      {
        LinqMetaData metaData = new LinqMetaData(adapter);

        var items = (from item in metaData.Item
                     where item.CustomerId == "123456"
                     select new
                    {
                      item.Id,
                      Description = item.Description ?? "",
                      QtyInStock = item.Stock.Where(s => s.WarehouseId == "whs").Sum(s => s.Quantity),
                      QtyInbound = item.InboundLine.Where(il => il.WarehouseId == "whs").Sum(il => il.Quantity) ?? 0,
                      QtyOutbound = item.OutboundLine.Where(ol => ol.WarehouseId == "whs").Sum(ol => ol.Quantity) ?? 0
                    } into result
                     where result.QtyInStock > 0
                     select result).TakePage(5,15);

        ObjectDumper.Write(items);
      }

awesome! . Ok so that bug is scratched

Yes, and although I have not done any real timings, my impression is that performance is better than Linq To Sql. But of course that is no surprise to you

twaindev wrote:

Yes, and although I have not done any real timings, my impression is that performance is better than Linq To Sql. But of course that is no surprise to you

To be fair: - their data fetch logic, so fetching data into objects, is faster. Though they generate a lot of IL at runtime to make this happen.

though their expression tree parsing is really slow. What I don't get is that they didn't realize that if you traverse a tree once from top to bottom, you can determine almost all information you want to know and can rewrite nodes into more special nodes so you don't have to analyze subtrees for that info again. Oh well...

Their hierarchical fetching is really bad, as I wrote on my blog as well. I really don't understand how they could have made such a tremendous mistake. It took me roughly 200 lines of code to get it working..