Query spec with subquery using CorrelatedOver
Joined: 02-May-2014
Joined: 02-May-2014
Joined: 02-May-2014
Joined: 02-May-2014
EtienneKepler wrote:
Yes, that's what I think at the beginning, but I found the reason why it doesn't work.
If I remove the .Trim() on all fields, it works. Is there a reason why it can't be used ?
The Trim() function is executed in the database, so it can't find a correlation between the outcome of the function and the related element, as it will build a comparer based on the predicate specified: as one side produces a value in the DB, it can't determine what the real outcome is, as it compares field values: it calculates hashes which are compared. The nested query is merged in-memory using these hashes, so if one side's value is to be computed again by a function, which executes in the DB, it's impossible to find a correlation.
(I hope that makes sense, if not please let me know 
)
Also the filter on Cancelled == false have to be done in the where clause
Yes, because the correlatedover predicate expression is solely used to build comparers so a parent (the outer query element) can be found for a child (nested query element) using value hashes which are calculated from each sides' fields as specified in the predicate expression.
The cancelled == false predicate has no play in that correlation (as it's a filter on the elements, not a correlation definition, i.e. which child belongs to which parent) so it has to be specified in the where clause.
the result is the same (I change TradeslibFields.Side.Equal into TradeslibFields.Side.NotEqual ==> It always use the Equal condition, the NotEqual doesn't seems to work.
Do you know why ?
are both fields null by any chance? As this is tied to data and your query is rather big, could you create a repro case for this with just the query which fails and e.g. on Adventureworks or northwind so no databases have to be distributed? Thanks
Joined: 02-May-2014
Hi Otis,
Thanks for the explanation, it's now clear to me. For the last part, the field is nullable in one table and not nullable in the other table, but in both case they are filled.
I've made an easier sample. Here are the 2 tables :
CREATE TABLE [dbo].[TableTestCase1]( [ID] [int] IDENTITY(1,1) NOT NULL, [ISIN] nchar NULL, [side] nchar NULL, [quantity] [int] NOT NULL, CONSTRAINT [PK_TableTestCase1] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]
CREATE TABLE [dbo].[TableTestCase2]( [ID] [int] IDENTITY(1,1) NOT NULL, [ISIN] nchar NULL, [side] nchar NULL, [broker] nchar NOT NULL, CONSTRAINT [PK_TableTestCase2] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]
insert into TableTestCase1 values ('FR001', 'A', 10) insert into TableTestCase1 values ('FR001', 'V', 20)
insert into TableTestCase2 values ('FR001', 'A', 'BuySide') insert into TableTestCase2 values ('FR001', 'V', 'SellSide')
And here is the code :
private class Test
{
public string broker { get; set; }
public BindingList<TableTestCase1Entity> listTest { get; set; }
}
private void TestMethods()
{
using (var adapter = this._adapterTools.GetNewAdapter()) // This create a new adapter
{
var qf = new QueryFactory();
var q = qf.Create()
.Select
(() => new Test
{
broker = TableTestCase2Fields.Broker.Source("tt").ToValue<string>(),
listTest =
new BindingList<TableTestCase1Entity>
(
qf.TableTestCase1
.CorrelatedOver
(
TableTestCase1Fields.Isin.Equal(TableTestCase2Fields.Isin.Source("tt"))
.And(TableTestCase1Fields.Side.Equal(TableTestCase2Fields.Side.Source("tt")))
)
.ToResultset<TableTestCase1Entity>()
)
}
)
.From
(
qf.TableTestCase2.As("tt")
);
var q2 = qf.Create()
.Select
(() => new Test
{
broker = TableTestCase2Fields.Broker.Source("tt").ToValue<string>(),
listTest =
new BindingList<TableTestCase1Entity>
(
qf.TableTestCase1
.CorrelatedOver
(
TableTestCase1Fields.Isin.Equal(TableTestCase2Fields.Isin.Source("tt"))
.And(TableTestCase1Fields.Side.NotEqual(TableTestCase2Fields.Side.Source("tt")))
)
.ToResultset<TableTestCase1Entity>()
)
}
)
.From
(
qf.TableTestCase2.As("tt")
);
var list1 = adapter.FetchQuery(q);
var list2 = adapter.FetchQuery(q2);
}
}
When I run this, list1 and list2 are equals, but they shouldn't. The property listTest should be filled differently.
I hope this informations are sufficient
thanks, we'll look into it
Joined: 02-May-2014
Thanks Otis,
I've done a workaround for now, I removed the condition and filtered data after the query execution. It loads more data, but it works , waiting for a fix.
Regards, Etienne.
It turns out a little different than I described earlier, and it can't be fixed. The hashcodes of both the child row fields in the correlation predicate and the parent row fields in the correlation predicate are calculated. These hashes are combined from the hashcodes of the various fields, so if there are multiple fields in the parent in the predicate, then for each field a hashcode is calculated and these hashcodes are then combined into a single code (as a string, as that's the easiest here, which always works, hashcodes are ints, and the code is easy to find back in a lookup hashset).
For each child the hashcode for the fields in the correlation predicate for the child are calculated, and then looked up in the hashes for the parent. If there's a match, the child will be merged into the parent row.
The correlation predicate is used for the nested query to limit the data set returned, and when there's a hashcollision, i.e. multiple rows have the same hashvalue but in theory can have different values. If that's the case, the compiled correlation predicate is used to find the right row in the list of rows which have the same hashvalue.
This system is very efficient, but has a drawback: the operators in the predicate can't be anything else than Equal: in the case of NotEqual, the hashcode isn't matching, as the hashcode is simply created from the value. The parent row isn't found based on using the predicate, but through hashes as it would otherwise be way too slow (n*m). With hashes it's OK.
Therefore I can't fix it... What I thought yesterday looked great on paper, I forgot that the merging was done by hash comparisons. This morning I already thought it couldn't work and indeed, testing it and reviewing the code underlined that. Sorry. 
I've added an exception throw to the process method to signal unsupported operator usage.
Joined: 02-May-2014
Hi Otis,
Thanks for you investigation, I will use the workaround so 
In this case, I have only 2 possible values, maybe I can use the where clause, I will try it later.
Regards, Etienne.