DevForce Community Forum : Cache is bypassed (new case) when grouping entities

Cache is bypassed (new case) when grouping entities : Thanks Denis,your suggestion does...

Fri, 09 Dec 2011 14:04:30 -700

Author: Frederic
Subject: 3134
Posted: 09-Dec-2011 at 2:04pm

Thanks Denis,

your suggestion does the trick... spot on!

I was hoping to apply the same query strategy on all my project queries but that did not work for queries returning scalar results:

This query cannot have a FetchStrategy of DataSourceAndCache. DataSourceAndCache implies a union of the results of a DataSource query and a CacheOnly query. This query returns a scalar result and the union of two scalar values is undefined.

I have read both articles discussing query inversion and cache management but I would lie if I was to pretend I understood it all :)

Nethertheless, Your suggestion helped me to make a good progress and I thank you for that.

Frederic

Cache is bypassed (new case) when grouping entities : Hi Frederic;We do QueryInversion...

Thu, 08 Dec 2011 18:13:07 -700

Author: DenisK
Subject: 3134
Posted: 08-Dec-2011 at 6:13pm

Hi Frederic;

We do QueryInversion to determine whether a query can be satisfied from the cache. As it turns out, not all queries can be inverted and this includes queries that involve projections (Select, SelectMany) such as in your case.

When this happens, we recommend that you force a query to go to the cache by specifying a different QueryStrategy with the query. Here are 2 examples.

var orderList = manager.GetQuery().With(QueryStrategy.CacheOnly)
        .GroupBy(o => new { o.CustomerID, o.EmployeeID })
        .Where(g => (g.Count() > 2))
        .SelectMany(g => g, (g, order) => order);

var DataSourceAndCache = new QueryStrategy(FetchStrategy.DataSourceAndCache, MergeStrategy.PreserveChanges, QueryInversionMode.Try);

var orderList = manager.GetQuery().With(DataSourceAndCache)
        .GroupBy(o => new { o.CustomerID, o.EmployeeID })
        .Where(g => (g.Count() > 2))
        .SelectMany(g => g, (g, order) => order);

Please see http://drc.ideablade.com/xwiki/bin/view/Documentation/query-inversion and http://drc.ideablade.com/xwiki/bin/view/Documentation/query-cache for more discussions on determining whether a query can be satisfied against the cache.

Cache is bypassed (new case) when grouping entities : Good afternoon,I have previously...

Thu, 08 Dec 2011 13:21:40 -700

Author: Frederic
Subject: 3134
Posted: 08-Dec-2011 at 1:21pm

Good afternoon,

I have previously posted a problem about devforce queries bypassing the cache. That problem was solved using a more recent version of Devforce. Today I have installed what I believe is the latest versaion of Devforce but I found a new case of the cache being bypassed.

I'm using Devforce 2010, version 6.1.3.3 on SQL Server 2008 R2.

Say I want to group orders by customerId and employeeId and take all orders where the count of the grouping is say above 2.

The query would look like this:

				var orderList = manager.GetQuery()
						.GroupBy(it => new { it.CustomerID, it.EmployeeID })
						.Where(g => (g.Count() > 2))
						.SelectMany(g => g, (g, order) => order);

This query gives me all orders meeting the criteria but is always executed against the database server.

So if I create a new employee, a new customer and create 3 new orders for that new customer placed by my new employee, now the above query does not returns my new orders.

Though, employee count is now one more than at the start of the program, so is customer and order count is 3 more than at the start but still my groupby query does not see the new orders.

If I then apply changes to the database using the

manager.SaveChanges();

command, the group by query shows my new orders.

I feel this is not right, I wish all my queries to hit the cache first then the database. How can I do so when using grouping queries?

Thanks a lot for your support

Frederic

Sample code:

NorthwindIB manager = new NorthwindIB(
shouldConnect: true,
dataSourceExtension: String.Empty,
entityServiceOption: IbEm.EntityServiceOption.UseLocalService,
compositionContextName: String.Empty);

var employeeQuery = manager.GetQuery();
var customerQuery = manager.GetQuery();
var orderQuery = manager.GetQuery();

var orderList = manager.GetQuery()
.GroupBy(it => new { it.CustomerID, it.EmployeeID })
.Where(g => (g.Count() > 2))
.SelectMany(g => g, (g, order) => order);

Int32 employeCountBefore = employeeQuery.ToList().Count; // 9
Int32 customerCountBefore = customerQuery.ToList().Count; // 93
Int32 orderCountBefore = orderQuery.ToList().Count; // 830
Int32 orderListCountBefore = orderList.ToList().Count; // First time count is 324
Employee newEmployee = AddEmployee(manager);
Customer newCustomer = AddCustomer(manager);
AddThreeOrders(manager, newEmployee, newCustomer);

Int32 employeCountAfter = employeeQuery.ToList().Count; // 10
Int32 customerCountAfter = customerQuery.ToList().Count; // 94
Int32 orderCountAfter = orderQuery.ToList().Count; // 833
Int32 orderListCountBeforeSave = orderList.ToList().Count; // Still 324 !!!

manager.SaveChanges();

Int32 orderListCountAfter = orderList.ToList().Count; // Now 327 !!!