How to save multiple entities in a single cache and append records to the existing entity collection in Ignite?

This is more about a modelling question and here is what I want to achieve.

I’ve a Database named “DB100” and have entities “TABLE-1” and “TABLE-2”. Both entities are holding thousands of records and records will be added on a daily basis. The user should also be executing join queries on both tables , for e.g. “select a.Id, b.Name from TABLE-1 a JOIN TABLE-2 b where a.Id = b.Id” . What could be the best modelling approach for this requirement in Ignite?

Is the below approach good and possible? Or please share if there is any better way.

var cache = ignite.GetOrCreateCache<string, int>("DB100");

cache[“TABLE-1”] = EntityCollection-1;
cache[“TABLE-2”] = EntityCollection-2;

var sqlp = new SqlFieldsQuery("select a.Id, b.Name from TABLE-1 a JOIN TABLE-2 b where a.Id = b.Id");
                    using (var cursor = cache.Query(sqlp))
                    {
                        foreach (var row in cursor) // throws exception 
                        {
                            
                        }
                    }

If possible, we would like to append the entity collection on daily basis remotely without fetching entire collection to the client side. For this, we’ve tried with Entry Processor but resulted in error possibly due to a mismatch in server and client. Our client code (thick) in C# and server nodes are in C++. Please share any idea, thanks in advance.

Solution

Actually, you shouldn't use different values in one cache as entity collections. Instead, it would be better to create 2 separate caches with SQL support, which can be done in the next ways:

by using SqlQueryField annotation
by configuring Query Entities for the cache
or by dynamic cache creation using CREATE TABLE statement

For example:

CacheConfiguration<Long, CacheEntry1> ccfg = new CacheConfiguration<Long, CacheEntry1>()
    .setName("Cache1")
    .setQueryEntities(
            Arrays.asList(
                    new QueryEntity(Long.class, CacheEntry1.class)
                        .setTableName("Table1")
                        .addQueryField("id", Long.class.getName(), null)
                        .addQueryField("name", String.class.getName(), null)
            )
    );

IgniteCache<Long, CacheEntry1> cache1 = ignite.createCache(ccfg);

And the same should be done for the second cache. After that, you will be able to query data from the caches using SQL. In this case, to add a new record you'll just need to insert it into the cache, without fetching the entire collection to the client side.

Also, in case you would like to use joins you should be aware of data colocation, you can find more details regarding that here and here.