Query.ToListAsync() throws exception

[Jeremy Holt]

Hi,

I'm still very new to RavenDb .....

Can ToListAsync() work outside of the Silverlight.dll?

The following code produces this exception in an app that only refers to Raven.Client.Lightweight (v.1.2.0):

 var query = session.Query<Transactions_ByDailyBalance.Result, Transactions_ByDailyBalance>()

                            .Where(c => c.GroupCompanyId == groupCompanyId && c.Date >= periodFrom && c.Date <= periodTo)

                            .Take(1024).Skip(start);

                        if (accountId.IsNotNullOrEmpty())

                        {

                            query = query.Where(c => c.AccountId == accountId);

                        }

                        var resultQuery = await query.ToListAsync();

System.AggregateException was unhandled by user code

  HResult=-2146233088

  Message=One or more errors occurred.

  Source=mscorlib

  StackTrace:

       at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)

       at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout)

       at System.Threading.Tasks.Task.WaitAll(Task[] tasks)

       at Cornhouse.Cas.WebUI.Services.RavenAccountingService.GetOpeningAndPeriodBalanceForAccountId(DateTime periodFrom, DateTime periodTo, String groupCompanyId, String accountId) in d:\Documents\Visual Studio 11\Projects\Cornhouse.Cas\Cornhouse.Cas.WebUI\Services\AccountingService.cs:line 78

       at Cornhouse.Cas.WebUI.Services.RavenAccountingService.GetLedger(BaseAccountCode account, DateTime periodFrom, DateTime periodTo, Int32 page) in d:\Documents\Visual Studio 11\Projects\Cornhouse.Cas\Cornhouse.Cas.WebUI\Services\AccountingService.cs:line 37

       at Cornhouse.Cas.WebUI.Controllers.LedgerController.GetLedger(AccountBank account, DateTime periodFrom, DateTime periodTo, Int32 page) in d:\Documents\Visual Studio 11\Projects\Cornhouse.Cas\Cornhouse.Cas.WebUI\Controllers\LedgerController.cs:line 53

       at Cornhouse.Cas.Tests.ControllerTests.Account_service_tests.Get_ledger_should_return_transactions_for_hsbc_brl_account() in d:\Documents\Visual Studio 11\Projects\Cornhouse.Cas\Cornhouse.Cas.Tests\ControllerTests\Account_service_tests.cs:line 152

  InnerException: System.NotSupportedException

       HResult=-2146233067

       Message=Specified method is not supported.

       Source=Raven.Client.Lightweight

       StackTrace:

            at Raven.Client.Document.DocumentSession.Raven.Client.Linq.IDocumentQueryGenerator.AsyncQuery[T](String indexName) in c:\Builds\RavenDB-Unstable-v1.2\Raven.Client.Lightweight\Document\DocumentSession.cs:line 585

            at Raven.Client.Linq.RavenQueryProviderProcessor`1.GetAsyncLuceneQueryFor(Expression expression) in c:\Builds\RavenDB-Unstable-v1.2\Raven.Client.Lightweight\Linq\RavenQueryProviderProcessor.cs:line 1107

            at Raven.Client.Linq.RavenQueryProvider`1.ToAsyncLuceneQuery[TResult](Expression expression) in c:\Builds\RavenDB-Unstable-v1.2\Raven.Client.Lightweight\Linq\RavenQueryProvider.cs:line 234

            at Raven.Client.Linq.LinqExtensions.ToListAsync[T](IQueryable`1 source) in c:\Builds\RavenDB-Unstable-v1.2\Raven.Client.Lightweight\Linq\LinqExtensions.cs:line 240

            at Cornhouse.Cas.WebUI.Services.RavenAccountingService.<GetRawDataForBalancePeriodAsync>d__5.MoveNext() in d:\Documents\Visual Studio 11\Projects\Cornhouse.Cas\Cornhouse.Cas.WebUI\Services\AccountingService.cs:line 120

       InnerException: 

Many thanks

Jeremy

 

[clayton collie]

Based on the message, this seems to be a Linq query translation issue in the as opposed to an Async one.

I think your where clause should precede the Take, so try moving things around ...

var query = session.Query<Transactions_ByDailyBalance.Result, Transactions_ByDailyBalance>();

                        if (accountId.IsNotNullOrEmpty())

                        {

                            query = query.Where(c => c.AccountId == accountId);

                        }

query = query

                            .Where(c => c.GroupCompanyId == groupCompanyId && c.Date >= periodFrom && c.Date <= periodTo)

                            .Take(1024).Skip(start);

                        var resultQuery = await query.ToListAsync();

[Oren Eini (Ayende Rahien)]

Nope, this should work.

Can you produce a failing test?

[Jeremy Holt]

I rearranged the Where and Take clauses, but it made no difference.

My failing test which gives the exception:

[Test]

        public void Should_return_async_balance()

        {

            // Arrange

            var periodFrom = new DateTime(2012, 1, 1);

            var periodTo = new DateTime(2012, 12, 31);

            const string groupCompanyId = "groupcompanies-8";

            // Act

            var actual = GetQuery(periodFrom, periodTo, groupCompanyId);

            // Assert

            actual.Result.Should().HaveCount(m => m > 10);

        }

        private static async Task<IEnumerable<Transactions_ByDailyBalance.Result>> GetQuery(DateTime periodFrom, DateTime periodTo, string groupCompanyId)

        {

            using (var session = MvcApplication.RavenSession)

            {

                var query = session.Query<Transactions_ByDailyBalance.Result, Transactions_ByDailyBalance>()

                    .Where(c => c.GroupCompanyId == groupCompanyId && c.Date >= periodFrom.Date && c.Date <= periodTo.Date)

                    .Take(128)

                    .ToListAsync();

                await query;

                return query.Result;

            }

        }

[Jeremy Holt]

Hi - I'm new to Google Groups as well :) I posted the test in reply to Clayton's message.

The index is as follows:

public class Transactions_ByDailyBalance : AbstractIndexCreationTask<Transaction, Transactions_ByDailyBalance.Result>

    {

        public Transactions_ByDailyBalance()

        {

            Map = transactions => from transaction in transactions

                                  select new

                                         {

                                             transaction.GroupCompanyId,

                                             transaction.AccountCurrency,

                                             transaction.Account.Category,

                                             transaction.Account.GroupType,

                                             transaction.AccountId,

                                             transaction.AccountName,

                                             transaction.Date,

                                             transaction.Debit,

                                             transaction.Credit,

                                         };

            Reduce = data => from c in data

                             group new {c.Debit, c.Credit} by new {c.GroupCompanyId, c.Category, c.AccountCurrency, c.GroupType, c.AccountId, c.AccountName, c.Date}

                             into grouping

                             select new

                                    {

                                        grouping.Key.GroupCompanyId,

                                        grouping.Key.Category,

                                        grouping.Key.AccountCurrency,

                                        grouping.Key.GroupType,

                                        grouping.Key.AccountId,

                                        grouping.Key.AccountName,

                                        grouping.Key.Date,

                                        Debit = grouping.Sum(c => c.Debit),

                                        Credit = grouping.Sum(c => c.Credit),

                                    };

            Index(x => x.GroupCompanyId, FieldIndexing.Analyzed);

            Index(x => x.AccountCurrency, FieldIndexing.Analyzed);

            Index(x => x.Category, FieldIndexing.Analyzed);

            Index(x => x.AccountId, FieldIndexing.Analyzed);

            Index(x => x.AccountName, FieldIndexing.Analyzed);

            Index(x => x.Date, FieldIndexing.Default);

        }

        public class Result

        {

            public string AccountCurrency { get; set; }

            public string AccountId { get; set; }

            public string AccountName { get; set; }

            public decimal Balance { get; set; }

            public Category Category { get; set; }

            public decimal Credit { get; set; }

            public DateTime Date { get; set; }

            public decimal Debit { get; set; }

            public string GroupCompanyId { get; set; }

            public GroupType GroupType { get; set; }

            public override string ToString()

            {

                return string.Format("{0} Db:{1:N2} Cr:{2:N2}  Bal:{3:N2}", AccountName, Debit, Credit, Debit - Credit);

            }

        }

    }

[Oren Eini (Ayende Rahien)]

Hi,

This looks fine, but it isn't a full test that I  can copy and run.

For what it worth, here is a passing test from RavenDB:

[Fact]

public void AsyncQuery_WithWhereClause()

{

using (GetNewServer())

using (var store = new DocumentStore

{

Url = "http://localhost:8079"

}.Initialize())

{

using (var session = store.OpenAsyncSession())

{

session.Store(new Dummy{Name = "oren"});

session.SaveChangesAsync().Wait();

}

using (var session = store.OpenAsyncSession())

{

var results = session.Query<Dummy>()

.Customize(x=>x.WaitForNonStaleResults())

.Where(x => x.Name == "oren")

.ToListAsync();

results.Wait();

Assert.Equal(1, results.Result.Count);

[Jeremy Holt]

Oh - do you need to open an async session instead of a regular session?

[Oren Eini (Ayende Rahien)]

Yes, you cannot use async operation on a sync session.

[Jeremy Holt]

OK - I'll try it out tomorrow morning and report back.

[Jeremy Holt]

Good morning.

Works as promised!!

I wish I could have said "works as it says on the label", since I never spotted this in the documentation :)

Actually, I'm not really criticizing the documentation. Ironically, because it so difficult to find things, I'm being forced to adopt a completely different way of learning about Raven. 

I'm working on the simple premise that Raven does exactly what I need - MY problem is to find out how it does it. While I was struggling with the ListToAsync issue, I knew that it was possible to do (otherwise it wouldn't be there), so I was forced to refactor my code so that when eventually I found out how to do it, I would have only one place to change it. So the whole excercise means that I ended up with far superior code.

I asked a "dumb" question on SO the other day which you answered telling me that I was mixing Lucene query on the client with the server. This forced me to completely restructure the data and the way of thinking about the data itself. Result: an improvement of about 1000 x in accessing the data compared to the way I was doing it before.

If there is one thing I would really beg for, and that is better comments for intellisense - particularly for the Enum values for FieldIndex.Analysed etc. I know that this is in the documentation, but it doesn't seem to stop me from using FieldIndex.Analysed instead of FieldIndex.Default half the time and then spend hours trying to understand why the index isn't returning what I am expecting.

Oren - I'm afraid to tell you that you have just lost a subscriber to EFProf! Bye bye Entity Framework/Linq to Sql! I've still got one major problem in how to restructure my relational data into "document" format - but once I manage to work it out, it's Raven all the way!

Best regards

Jeremy

[Oren Eini (Ayende Rahien)]

inline

On Sun, Aug 19, 2012 at 2:56 PM, Jeremy Holt <jh...@amberwoodtrading.com> wrote:

Good morning.

Works as promised!!

I wish I could have said "works as it says on the label", since I never spotted this in the documentation :)

This is actually documented (briefly) here: http://ravendb.net/docs/client-api/working-asynchronously

We are aware of the documentation issues, we have hired someone to take care of that.

Expect to see results in the near future.

 

Actually, I'm not really criticizing the documentation. Ironically, because it so difficult to find things, I'm being forced to adopt a completely different way of learning about Raven. 

I'm working on the simple premise that Raven does exactly what I need - MY problem is to find out how it does it. While I was struggling with the ListToAsync issue, I knew that it was possible to do (otherwise it wouldn't be there), so I was forced to refactor my code so that when eventually I found out how to do it, I would have only one place to change it. So the whole excercise means that I ended up with far superior code.

I asked a "dumb" question on SO the other day which you answered telling me that I was mixing Lucene query on the client with the server. This forced me to completely restructure the data and the way of thinking about the data itself. Result: an improvement of about 1000 x in accessing the data compared to the way I was doing it before.

If there is one thing I would really beg for, and that is better comments for intellisense - particularly for the Enum values for FieldIndex.Analysed etc. I know that this is in the documentation, but it doesn't seem to stop me from using FieldIndex.Analysed instead of FieldIndex.Default half the time and then spend hours trying to understand why the index isn't returning what I am expecting.

The problem in putting stuff like that in the xml comments is that they aren't really the appropriate place to learn about the "what" of RavenDB. They will tell you what they do, but it isn't the place to put a three paragraphs explanation about full text search.

This is documented here: http://ravendb.net/docs/appendixes/lucene-indexes-usage

 

Oren - I'm afraid to tell you that you have just lost a subscriber to EFProf! Bye bye Entity Framework/Linq to Sql! I've still got one major problem in how to restructure my relational data into "document" format - but once I manage to work it out, it's Raven all the way!

I can live with this problem :-)

[Kijana Woodard]

Hmmmm. Maybe intelligence could have links to more substantial docs...with clean urls, might be handy.

[Oren Eini (Ayende Rahien)]

I don't understand your meaning here.

[Kijana Woodard]

Ooops. Android turned intellisense into intelligence.

[Oren Eini (Ayende Rahien)]

It doesn't support links, htough

[Kijana Woodard]

Yeah. I know you wouldn't be able to click on them, but if you could see docs/topic, then you would know where to go.

Could be a terrible idea in practice.

Would be a better idea for vs2014 to add support for intellisense links.

[Jeremy Holt]

I wasn't really advocating the entire documents in the XML comments, and I'm not sure that links in the comments would be particularly helpful - you'd be constantly losing your train of thought as you go off clicking everywhere.

I'm quite happy to wait for the new documentation. At least (hopefully) there will be only one place to go to look for something, rather than googling for pretty much everything.

On the other hand, with the current documentation "leaving something to desire", as I said in my original comment, I'm forced to try things, then try to find the documentation. All in all, it's working out quite well. (I'm just complaining because it is my nature to do so!).

I'm also trying to learn jquery, mvc, javascript and kendo all at the same time. I've "learned" MVC the fastest because of the volume of documentation available - however, I don't think I really "understand" it. I just solve my immediate problem and then move on. Same goes for jquery and javascript - I'm not really making the effort to get a deep understanding at this moment - in time, no doubt, I will find the time to study it in more detail.

The kendo documentation is a million times worse than the RavenDb documentation - I've considered throwing it out a dozen times, but keep going back until I can make it work. I just discovered this morning that I can "debug" js files in VS 2012!

However, Raven is a completely different beast. If I can "master" it and understand what I am doing (various epiphany moments) - the result is that it completely affects the application from the ground up.

You have no idea how liberating the idea of "documents" is (well you probably do :). Just thinking it through has resolved dozens of structural problems in our existing application. 

You have no idea how liberating  "safe by design is" (well you do!). Talk about falling into the pit of success. The first time I came across it my reaction was WTF. However, off I went again to restructure my code, and waddya know - I get a 100% improvement in code quality, logic and performance. (My current app is WPF that loads 20,000 items from the server to group them on the client - I managed to rewrite the logic to do it in chunks of 512 items - with most of the hard work occurring in the index on the server).

If I could close with one more gripe - please can we have a few less "Blogs" examples. Philip Haydon wrote a series of blogs about what he is persisting which was enormously helpful, because he was referring to order/ order details - at least this was something I could identify with.

I would describe my first two weeks with Raven as a massive learning curve - jeez - what the hell are map/reduce indexes?? However, once you start to work your way up the curve, it gets a little bit easier .....

Best regards

Jeremy

[Oren Eini (Ayende Rahien)]

Jeremy,
Can you have a list of question prepared? We can do a webcast in which we will go over all of them and answer them.

[Jeremy Holt]

Hi Oren,

I'm not quite sure what you meant by putting forward my questions to make a web cast so I've put together a description of our business and the "problem(s)".

I feel like I'm being a bit cheeky - it's almost like asking for a free consultancy, but I guess there must be other people out there, coming to Raven from the relational world that may benefit from your comments on how to model a "real world" problem that is not about Blogs and Posts :)

Perhaps my questions would make a little more sense if I explained our business a little:

Bear in mind that I have already written an app for this in SQL which is creaking at the seams. 

We are commodity traders – one minute we are buying product, and the next we are selling. We (Cornhouse Ltd) therefore appear on our contract either as the Seller or the Buyer.

We are also brokers (Amberwood Ltd) where any other purchaser or vendor can appear on the contract. We are also more often than not the broker for Cornhouse Ltd.

Each contract consists of the header details:

Contract number (contracts/Id)

Seller

Buyer

Commodity Group (CommodityGroupId) – Peanuts, Cashews, Almonds etc

Commodity (sub division of the commodity group) i.e. US Origin Peanuts 2010 crop or Argentine Origin Peanuts 2012 crop etc

Payment Terms

Packing,

Contract clauses.

1. For each contract there are its details. The details are divisible, i.e. at the time of the purchase we have bought 100 mt of physical peanuts in Rotterdam. The supplier issued one sales invoice which we paid for.

2. We may then break the lot down into 10 x 10 mt different sales to a variety of customers. For each lot sold we need to raise a sales invoice.

3. We may often make a sale to a customer of 20 mt, where part of the goods are allocated from one contract and part from another.

4. We therefore have a Many <-> Many relationship between the contract details and the invoices.

5. When we present information to the bank about our stocks, they want to know which purchases have been paid for and when, and where the goods are physically located, quantity In the warehouse, warrant number etc.

6. It is easy enough to query for the physical goods, and easy enough to get the financial information (from the invoices) – however, it’s very difficult to get the information for both parts at the same time.

The contract details look like this:

ContractDetails/Id

Contract Number (contracts/id)

PreAllocatedId (contractdetails/Id)

AllocationId(contractdetails/id)

Invoice Number (transactions/id)

Grade (type of product)

Contract quantity

Shipping period

Price

Destination

Shipped Quantity

Vessel Name

Warehouse Quantity

Warehouse Name

Warrant number

etc.

The contract/contract details lends itself nicely to a document structure. It has the added enormous bonus of allowing us to be very flexible on the payment terms and contract clauses. 

At the moment, this information comes from a look up table, i.e. we store PaymentId = 5 – “Nett cash against documents”. However, we may sell “Nett cash against documents for payment by the 17th August”, which means that either we create a new PaymentId for this, or (what actually happens) the secretary copies the contract to Word and types the “exceptional” payment term in. Same goes for the contract clauses which may be slightly different to the “standard” look up clauses.

The problem with this is that we have contracts in our system, which we don’t know are the same that were physically sent to the Buyer and Seller. With a document database, I can simply suggest the template clause, and let the user type in and save the actual details inside the contract.

An explanation of the PreAllocatedId and AllocationId:

Since we are traders we do not necessarily buy and sell on the same day – i.e. we take trading positions – either long or short. This means that when we make a sales contract, we don’t know at the time where we are going to buy it. When we eventually cover our position, it is nice to be able to pair off the purchase and sale contracts – so the PreAllocatedId is applied to both contracts.

e.g.

Contracts/100

Seller: Cornhouse

Buyer: Oren

{

ContractDetails/43224 - 25 mt W320 grade cashews for shipment in October 2012

}

Contracts/205

Seller: XYZ Company

Buyer: Cornhouse

{

{ContractDetails/65551 – 25 mt W320 grade cashews for shipment in October 2012},

{ContractDetails/65552 – 25 mt W320 grade cashews for shipment in November 2012}

}

When we buy in the contract we would set the PreallocatedId on both contracts as follows:

Contracts/100

Seller: Cornhouse

Buyer: Oren

{

ContractDetails/43224 - 25 mt W320 grade cashews for shipment in October 2012

PreallocatedId/43224

}

Contracts/205

Seller: XYZ Company

Buyer: Cornhouse

{

{ContractDetails/65551 – 25 mt W320 grade cashews for shipment in October 2012, 

PreAllocatedId/43224},

{ContractDetails/65552 – 25 mt W320 grade cashews for shipment in November 2012}

}

When we invoice or pay for the goods the same procedure applies to the AllocationId, so the contracts now looks like this:

Contracts/100

Seller: Cornhouse

Buyer: Oren

{

ContractDetails/43224 - 25 mt W320 grade cashews for shipment in October 2012,

PreallocatedId/43224,

AllocationId/76543

}

Contracts/205

Seller: XYZ Company

Buyer: Cornhouse

{

{ContractDetails/65551 – 25 mt W320 grade cashews for shipment in October 2012, PreAllocatedId/43224, AllocationId/76543},

{ContractDetails/65552 – 25 mt W320 grade cashews for shipment in November 2012}

}

The purpose of the PreallocatedId and AllocationId is so that we can easily see where the contract is going to or coming from.

We will now issue our sales or purchase invoice which goes into a table of Transactions.

At the moment, our current system keeps a seperate table of just the ContractDetailsId and the TransactionId, which is what allows us to track which invoices are associated with each contract detail, and which contract details are associated with each transaction (invoice). In addition to invoices (which are defined as "related to a contract detail") other transactions such as bank, expenses go into this table. 

This table looks like:

contractdetails/id transactions/id

100 765

101 766

101 645

102 646

102 765

etc

So from here I can find all the transactions for contractdetails 102 (646 and 765) or the other way round.

It is worth noting at this point that once a contract has been invoiced it is rare (if not impossible) for its' transaction/invoice to change. If goods are rejected and come back into stock this is a new transaction. In other words, this data is basically write once, read many. However, we still need to be able to write again to the transaction, eg to update it when we receive payment or reconcile it on the bank statement.

My problem lies in how to model the Many <-> Many relationship between the contract details and the transactions.

I'm certain I need to keep the transactions in a seperate table (which they are now), so that I can easily query them - account balance for each client, stocks, bank etc. From this table we generate the Trial Balance, Income Statement, Debtors list etc.

Actually, while writing this missive, it occurs to me that I could actually keep a copy of the transaction in the contract. However, I'm not wild about the idea - just thinking about how to handle changes to the transaction/invoice.

..... two hours later ..... I've come to the conclusion that in fact it makes a lot of sense to keep a COPY of the transaction together with the contract detail. If I endure that a transaction can only be changed through one form only I can control the updates to the copied data. Further, I gain the benefit for free of having my financial details in the same document as the contract itself, allowing me to get the value of the individual contract. With map/reduce queries I should be able to easily calculate the financial value of the stocks by analysing the Contracts document.

So that is basically our system. On the face of it, its not particularly complex, it has a nice aggregate root being the Contract, and the only fly in the ointment is maintaining the relationship between the financial side of the business and the physical side of the business.

I have avoided using "computer speak" here, i.e. Database Diagrams etc, so that I could better describe our requirements. 

I would say again that we do have a working system in place. However, in recent weeks our internet provider has dropped the quality of our internet access from Brazil from 35 MBps to 3 MBps. This had made our system virtually unusable. I desperately need to rewrite the application from the ground up. I won't embarass myself here with the crap that I wrote originally, but lets just say that EF Prof comes up with a page that says "WTF do you think you are doing here????" - so you did put in the dumb programmer warning page like I asked you to a couple of months back :)

Frankly, rewriting the app, using SQL and WPF is just going to be waste of time. I will eventually end up making the same mistakes I always make - you see there is no "safe by design" in SQL as you well know. 

So I'm now rewriting it on the back of RavenDb and MVC. I'm still in the early mock-up stage, but at least I have a lot of experience from my previous app, and know more or less what we (as users) all need. 

I would also just add, that I am not a programmer - I do this in my spare time. I'm  actually an edible nuts/soft commodities trader that got dragged into doing this.

As a very final comment, the very act of writing this message has actually allowed me to find the solution to the problem. Still, I would be very interested in hearing other peoples ideas on modelling this kind of data. I guess that this kind of model is probably very common in the real world.

Best regards

Jeremy