LinqProvider that gets data from from two places and merges them

Question

Hi all,

I have a Linq query that looks something like the following

var query3 = from c in Session.CreateLinq<AccountTransaction>()
             join a in Session.CreateLinq<Account>() on c.Account equals a
             where c.DebitAmount >= 0
             select new { a.Name, c.DebitAmount }
;

The Session object interacts with a datasource behind the scenes but it also has an internal cached state which may have changes. When I run a query I would like to query the both the internal cached state AND the datasource and then merge the results together, with the internal cached state taking precendence.

I am using re-linq for the generation of the query against the datasource which is working fine. What I am not sure about is how to also do the query against the internal state using the same Linq query.

There's a call GetAllCached() on Session that I can use instead of Session.CreateLinq if I just wanted to query the internal state. But I'm not sure at which point in my custom provider I can handle handing off to the datasource AND the internal state using GetAllCached().

Any suggestions appreciated from any Linq gurus.

Answer 1

If I understand correctly, you have a single custom LINQ provider for your datasource, and a (presumably type-safe) way of getting cached results as well.

In this case, I recommend just using LINQ to Objects to access your cached set. You can use AsEnumerable to "step out" of your custom LINQ provider into LINQ to Objects.

The join brings up a problem, though. Since either of these types may exist in the cache, it's not possible to push logic to the DB. For example, is it possible to have an AccountTransaction in the cache without its Account also being in the cache?

If you allow any situation in the cache (e.g., AccountTransaction without associated Account records), then you have to do the join in memory and not in the db:

var allDebitAccountTransactions = Session.GetAllCached<AccountTransaction>()
    .Where(x => x.DebitAmount >= 0)
    .Union(Session.CreateLinq<AccountTransaction>()
        .Where(x => x.DebitAmount >= 0));
var allAccounts = Session.GetAllCached<Account>()
    .Union(Session.CreateLinq<Account>());
var query3 = from c in allDebitAccountTransactions
             join a in allAccounts where c.Account equals a
             select new { a.Name, c.DebitAmount };

However, if you have more control over your cache, and only allow AccountTransaction objects to be present if their associated Account objects are present, then you can push the join operation to the datasource and do another one in memory, merging the results:

var datasourceResults = from c in Session.CreateLinq<AccountTransaction>()
    join a in Session.CreateLinq<Account>() on c.Account equals a
    where c.DebitAmount >= 0
    select new { a.Name, c.DebitAmount, c.Id };
var cacheResults = from c in Session.GetAllCached<AccountTransaction>()
    join a in Session.GetAllCached<Account>() on c.Account equals a
    where c.DebitAmount >= 0
    select new { a.Name, c.DebitAmount, c.Id };
var query3 = cacheResults.Union(datasourceResults)
    .Select(x => new { x.Name, x.DebitAmount });

I think. I am not an expert in LINQ, so I'm curious to see other responses.

Answer 2

        // From Database
        var query1 = from c in Session.CreateLinq<AcccountTransaction>()
                     join a in Session.CreateLinq<Account>()
                     on c.Account equals a
                     where c.DebitAmount >= 0
                     select new { Account = a, AccountTrans = c };
                     //select new { a.Name, c.DebitAmount }; 

        // From Cache
        var query2 = from c in Session.GetAllCached<AcccountTransaction>()
                     join a in Session.GetAllCached<Account>()
                     on c.Account equals a
                     where c.DebitAmount >= 0
                     select new { Account = a, AccountTrans = c };
                     //select new { a.Name, c.DebitAmount };   



        //var query3 = query2.Union(query1.Except(query2));
        var query4 = query2.Union(query1);

Modified: 04:51 AM Singapore Time