You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by davida <da...@live.com> on 2017/02/19 18:20:57 UTC
SqlFieldQueries on property of custom user type
Hi all,
Does Ignite.NET supports SqlFieldQuery referencing properties/fields of
custom type?
Queries referencing Dummy object succeed;
"select top(10) Dummy from KPIDetail"
//OK
"select Dummy, count(*) from KPIDetail group by Dummy" //OK
"select Dummy from KPIDetail order by Dummy" //OK
The Question: Is there a way to query fields of Dummy object ? e.g:
"select Dummy.ID from KPIDetail" or "select Dummy from KPIDetail
where Dummy.ID < 10"
The obvious workaround is adding fields of Dummy object as public properties
to KPIDetail object (see KPIDetail.DummyID), but I noticed that it works
only with IBinarySerializer. When reflective serialization is used DummyID
is deserialized as null in following queries:
"select top(10) DummyID from KPIDetail" - returns 10 rows, value in
IQueryCursor is null
"select top(10) DummyID from KPIDetail where DummyID = 10" - nothing
is returned
Here is the code:
[Serializable]
public class KPIDetail
{
[Dapper.Contrib.Extensions.Key]
public int ID { get; set; }
public DateTime? Date { get; set; }
public string Origin { get; set; }
public string Destination { get; set; }
public int WayBillNumber { get; set; }
public DateTime? WayBillDate { get; set; }
public string Shipper { get; set; }
public string Consignee { get; set; }
public float? LoadedDays { get; set; }
public float? EmptyDays { get; set; }
public float? TotalDays { get; set; }
public float? LoadedMiles { get; set; }
public float? EmptyMiles { get; set; }
public float? TotalMiles { get; set; }
public int? VolumeCount { get; set; }
public DummyKPI Dummy { get; set; } = new DummyKPI();
public int DummyID {
get { return Dummy.ID; }
set { Dummy.ID = value;}
}
}
[Serializable]
public class DummyKPI
{
private static int i = 1;
public DummyKPI()
{
ID = i++;
Str = ID.ToString();
}
public int ID { get; set; }
public string Str { get; set; }
}
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SqlFieldQueries on property of custom user type
Posted by davida <da...@live.com>.
Another option(for filtering) are scan queries, but I believe field queries
are generally faster than scan queries as it involves deserializing objects
in ICacheEntryFilter.
Related to: SqlFieldQueries on property of custom user type
Feb 19, 2017; 10:20pm — by davidaonline davida
Does Ignite.NET supports SqlFieldQuery referencing properties/fields of
custom type?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719p10721.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SqlFieldQueries on property of custom user type
Posted by davida <da...@live.com>.
Thanks Denis it works (forgot to add the field in app.config's queryfields
collection).
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719p10751.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SqlFieldQueries on property of custom user type
Posted by Denis Magda <dm...@apache.org>.
It’s possible to execute queries accessing fields of inner classes. In your example you need to remove “ChildObject” prefix from the query and it will work fine:
"select ParentID, ChildID from Parent where ChildID < 10”
—
Denis
> On Feb 20, 2017, at 6:14 AM, davida <da...@live.com> wrote:
>
> Hi Denis, unfortunately that did not work for me (I had problem with inner
> class).
> Let me clarify what I was trying to do. I have following classes:
>
> class Parent {
> int ParentID;
> Child ChildObject; // assume initialized
> }
> class Child {
> int ChildID;
> }
>
> Parent properly is serialized with its child object. I want to execute
> following query:
> "select ParentID, ChildObject.ChildID from Parent where
> ChildObject.ChildID<10".
>
> I assume this should not be possible to execute as child object is custom
> user type. Am I correct ?
> Many Thanks.
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719p10732.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SqlFieldQueries on property of custom user type
Posted by davida <da...@live.com>.
Hi Denis, unfortunately that did not work for me (I had problem with inner
class).
Let me clarify what I was trying to do. I have following classes:
class Parent {
int ParentID;
Child ChildObject; // assume initialized
}
class Child {
int ChildID;
}
Parent properly is serialized with its child object. I want to execute
following query:
"select ParentID, ChildObject.ChildID from Parent where
ChildObject.ChildID<10".
I assume this should not be possible to execute as child object is custom
user type. Am I correct ?
Many Thanks.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719p10732.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: SqlFieldQueries on property of custom user type
Posted by Denis Magda <dm...@apache.org>.
> The Question: Is there a way to query fields of Dummy object ? e.g:
> "select Dummy.ID from KPIDetail" or "select Dummy from KPIDetail
> where Dummy.ID < 10”
Label ID field of Dummy using QuerySqlField annotation and you can access it in the query this way:
> “select ID from KPIDetail" or "select Dummy from KPIDetail
> where ID < 10”
More info is here:
https://apacheignite-net.readme.io/docs/sql-queries#section-single-column-indexes <https://apacheignite-net.readme.io/docs/sql-queries#section-single-column-indexes>
—
Denis
> On Feb 19, 2017, at 10:20 AM, davida <da...@live.com> wrote:
>
> Hi all,
>
> Does Ignite.NET supports SqlFieldQuery referencing properties/fields of
> custom type?
>
> Queries referencing Dummy object succeed;
> "select top(10) Dummy from KPIDetail"
> //OK
> "select Dummy, count(*) from KPIDetail group by Dummy" //OK
> "select Dummy from KPIDetail order by Dummy" //OK
>
> The Question: Is there a way to query fields of Dummy object ? e.g:
> "select Dummy.ID from KPIDetail" or "select Dummy from KPIDetail
> where Dummy.ID < 10"
>
> The obvious workaround is adding fields of Dummy object as public properties
> to KPIDetail object (see KPIDetail.DummyID), but I noticed that it works
> only with IBinarySerializer. When reflective serialization is used DummyID
> is deserialized as null in following queries:
> "select top(10) DummyID from KPIDetail" - returns 10 rows, value in
> IQueryCursor is null
> "select top(10) DummyID from KPIDetail where DummyID = 10" - nothing
> is returned
>
>
> Here is the code:
>
> [Serializable]
> public class KPIDetail
> {
> [Dapper.Contrib.Extensions.Key]
> public int ID { get; set; }
>
> public DateTime? Date { get; set; }
> public string Origin { get; set; }
> public string Destination { get; set; }
> public int WayBillNumber { get; set; }
> public DateTime? WayBillDate { get; set; }
> public string Shipper { get; set; }
> public string Consignee { get; set; }
> public float? LoadedDays { get; set; }
> public float? EmptyDays { get; set; }
> public float? TotalDays { get; set; }
> public float? LoadedMiles { get; set; }
> public float? EmptyMiles { get; set; }
> public float? TotalMiles { get; set; }
> public int? VolumeCount { get; set; }
>
> public DummyKPI Dummy { get; set; } = new DummyKPI();
> public int DummyID {
> get { return Dummy.ID; }
> set { Dummy.ID = value;}
> }
> }
>
> [Serializable]
> public class DummyKPI
> {
> private static int i = 1;
> public DummyKPI()
> {
> ID = i++;
> Str = ID.ToString();
> }
> public int ID { get; set; }
> public string Str { get; set; }
> }
>
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/SqlFieldQueries-on-property-of-custom-user-type-tp10719.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.