Apache Ignite Linq over SQL-CodePudding

I set up all of my caches using SQL api; I want to use Linq to query over the data in these tables.

How to do this properly?

This is what I have. My model look like this:

public class Thing
{
    [QuerySqlField]
    public Guid Id { get; set; }

    [QuerySqlField]
    public string Name { get; set; }

    [QuerySqlField]
    public DateTime EffectiveDate { get; set; }
}

I defined the table as follow:

CREATE TABLE IF NOT EXISTS Things (
    Id UUID,
    Name VARCHAR,
    EffectiveDate TIMESTAMP,

    PRIMARY KEY(Id))
WITH ""
    TEMPLATE = PARTITIONED,
    CACHE_NAME = consoleappserver.Thing,
    VALUE_TYPE = consoleappserver.Thing""

Now I am attempting a linq over that

var cache = cli.GetCache<Guid, Thing>(typeof(Thing).FullName);
var things = cache.AsCacheQueryable();
var effectiveDate = DateTime.SpecifyKind(DateTime.Today, DateTimeKind.Utc);
things = things.Where(t => t.Value.EffectiveDate <= effectiveDate);

foreach (var kv in things)
{
    Console.WriteLine("Things #{0} '{1}'",
        kv.Value.Id, kv.Value.Name);
}

I get the following error because the sql looks like this:

'Failed to parse query. Column "_T0.EffectiveDate" not found; SQL statement:
select _T0._KEY, _T0._VAL from "PUBLIC"."THINGS" as _T0 
where (_T0."EffectiveDate" <= ?) [42122-199]'

CodePudding user response：

The problem: Ignite converts column names to uppercase, but LINQ provider uses quoted identifiers, so the requested column name EffectiveDate does not match actual EFFECTIVEDATE.

Solution 1

Use quoted identifiers in the DDL query so that column name case is preserved:

CREATE TABLE IF NOT EXISTS Things (
    "Id" UUID,
    "Name" VARCHAR,
    "EffectiveDate" TIMESTAMP,

    PRIMARY KEY("Id"))
WITH ""
    TEMPLATE = PARTITIONED,
    CACHE_NAME = consoleappserver.Thing,
    VALUE_TYPE = consoleappserver.Thing""

Solution 2

Specify uppercase column names in the model class:

public class Thing
{
    [QuerySqlField(Name = "ID")]
    public Guid Id { get; set; }

    [QuerySqlField(Name = "NAME")]
    public string Name { get; set; }

    [QuerySqlField(Name = "EFFECTIVEDATE")]
    public DateTime EffectiveDate { get; set; }
}