Question

May 22, 2015 at 1:43 PM
I am trying to use VITA framework for small project that connect to Sql Server, Sql Server Ce and SqLite. (The same data schema and data).

I have made query which part looks like:
...
(searchParams.Geom1 & w.Geom1) == w.Geom1 &&
(searchParams.Geom2 & w.Geom2) == w.Geom2 &&
(searchParams.Geom3 & w.Geom3) == w.Geom3 &&
...
where "w" alias for entity and searchParams are user parameters. Geom{X} in is Boolean type resp. bit in database.

VITA framework generates command
...
AND ((@P17 = 1) AND sti$.[Geom1] = 1) = sti$.[Geom1]
AND ((@P18 = 1) AND sti$.[Geom2] = 1) = sti$.[Geom2]
AND ((@P19 = 1) AND sti$.[Geom3] = 1) = sti$.[Geom3]
...
and fails during parsing (Incorrect syntax near '=')

I used also EnityFramework 6 that generates following command:
...
AND ((CASE WHEN ((@p__linq__17 = 1) AND ([Filter1].[Geom1] = 1)) THEN cast(1 as bit) WHEN ( NOT ((@p__linq__17 = 1) AND ([Filter1].[Geom1] = 1))) THEN cast(0 as bit) END) = [Filter1].[Geom1])

AND ((CASE WHEN ((@p__linq__18 = 1) AND ([Filter1].[Geom2] = 1)) THEN cast(1 as bit) WHEN ( NOT ((@p__linq__18 = 1) AND ([Filter1].[Geom2] = 1))) THEN cast(0 as bit) END) = [Filter1].[Geom2])

AND ((CASE WHEN ((@p__linq__19 = 1) AND ([Filter1].[Geom3] = 1)) THEN cast(1 as bit) WHEN ( NOT ((@p__linq__19 = 1) AND ([Filter1].[Geom3] = 1))) THEN cast(0 as bit) END) = [Filter1].[Geom3])

...
which returns what I expect.

I thing that VITA evaluates where clause in linq query wrong but I am not sure.

Make I something incorrect manner or is this VITA specific problem?
May 23, 2015 at 4:06 AM
Hi
yes, this is a bug in Linq translator. The problem is this 'weird' nature of bit in some servers (MS SQL and SQLite), when Bit column should be treated as int '1', not bool. So when translating expressions, the expr 'BitCol == true' is converted into 'BitCol = 1'. You see it in resulting SQL. But it fails to catch this in the '==' expression over derived value. I will look into this. Definitely I don't want to produce the monstrous SQL that EF generates :)
Until I fix this, you may try to rearrange the query expression so it runs OK. I'm a bit puzzled over the 'meaning' of this comparison, what kind of search you're doing, very strange arrangement to me.
In any case the expression
(A & B) = B
is equivalent to
A | !B
(check it with all value pairs) - and this should work (I hope) - try it.
thanks and sorry for your troubles
Roman
May 25, 2015 at 12:58 PM
Hi Roman!

Thank you for fast response.

I agree, EF creates very odd SQLs.

The query has to do with this: http://en.wikipedia.org/wiki/Topological_index .
It's part of our code, we are copying from version to version without we reviewed it. You are right, A | !B is equivalent. Thank you.

I found another small issue that concerns Sql Server and Sql Server Ce: "IndexName" property is ignored in "IndexAttribute". The name is always auto generated.

Thank you again

Best regards,

Milos
May 26, 2015 at 3:16 AM
Edited May 26, 2015 at 4:49 AM
Hi
wow, about topological index in chemistry - seems like you're doing some serious stuff. I knew only about topological indexes in terms of SCC grouping/sorting of graphs - pretty simple algorithm (it is used inside VITA to compute proper order of updates/inserts/deletes).
About IndexName - this is misunderstanding, the name is confusing. It is not the name of database index, it is a code/biz logic friendly alias that is reported in exception like unique index violation - to clearly identify which index was violated if there are more than one. The better name should probably be FriendlyAlias, I will most likely change it.
Regards
Roman
May 26, 2015 at 8:31 AM
Oh, really misunderstanding. To optimize I tried to generate a "long" index, in terms of number of fields which contain and then I came across a problem with the length of the name generated index. Index name was longer than allowed length in the SQL Server. This issue is not included in SQLite. I considered that "IndexName" is suitable to solve this problem. :)

Regarding "... pretty simple alogrithm ..." and the entire framework, I think you did a real great job, for which you have my admiration.

If you would like to know more about our software here is a link to our homepage: www.highchem.com

Sincerely,

Milos
Jun 22, 2015 at 9:07 PM
This bool expression problem should be fixed now, in the latest version
Jul 9, 2015 at 10:30 AM
Thank you very much.

Currently I froze on the following issues:

I have two entities, such as "A" and "B", where "A" refers to "B" through Column "BId" and when the record is deleted in Table B, the corresponding row in Table A will have column "BId" set to null. Classic "FOREIGN KEY ... DELETE CASCADE SET NULL".

I can not achieve this. Is there any way to do this in the Vita framework ?

Thank you.

Milos
Jul 9, 2015 at 4:54 PM
Only Cascade DELETE is supported. I personally never encountered in practice the case for CASCADE SET NULL, so decided to skip it. You'll have to nullify references explicitly. BTW, there's a function to help you:
session.CanDeleteEntity(entToDelete, out blockingEntityTypes);

(it is extension). See TestCanDelete unit test in Extended unit tests project
Jul 9, 2015 at 4:59 PM
By the way, in a few days I'll push new feature - update/insert/delete ops using LINQ. This will make these kind of updates much easier and efficient.