The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

Home

Forums

Photos

The EntitySpaces Community » EntitySpaces 2008 » Preview / Roadmap Issues » A problem with Exists

A problem with Exists

Last post 05-06-2008, 8:58 PM by Mike.Griffin. 12 replies.


	Sort Posts: Previous Next

04-25-2008, 5:23 AM 9019

Ammo
Joined on 04-09-2008
Posts 10

A problem with Exists

Examples:

1) we get incorect SQL with some Exists or In in Where clause

UsersQuery uq = new UsersQuery("uq");
UsersQuery uq1 = new UsersQuery("uq1");
UsersQuery uq2 = new UsersQuery("uq2");
uq1.Select(uq1.UserID);
uq2.Select(uq2.UserID);
uq.Where(uq.Exists(uq2), uq.Exists(uq1));

this example will return data but sql is incorrect

2)if In or  Exist  folow after normaly condition then we get incorrect SQL too

UsersQuery uq = new UsersQuery("uq");
UsersQuery uq1 = new UsersQuery("uq1");
UsersQuery uq2 = new UsersQuery("uq2");
uq1.Select(uq1.UserID);
uq2.Select(uq2.UserID);
uq.Where(uq.UserID > 100, uq.Exists(uq1));

result :

exec sp_executesql N'SELECT * FROM [Users] uq WHERE uq.[UserID] > @UserID1 AND  EXISTS@UserID1 (SELECT uq1.[UserID] AS ''UserID''  FROM [Users] uq1) ',N'@UserID1 int',@UserID1=100

3) try experiment with In or Exists in Or or And caluse

UsersQuery uq = new UsersQuery("uq");
UsersQuery uq1 = new UsersQuery("uq1");
UsersQuery uq2 = new UsersQuery("uq2");
uq1.Select(uq1.UserID);
uq2.Select(uq2.UserID);
uq.Where(uq.And(uq.UserID > 100, uq.Exists(uq1)));

we got exception before sql posted to server

pls fix it before beta2

Report abuse

04-27-2008, 9:49 PM 9051 in reply to 9019

David.Parsons
Joined on 01-14-2007
Posts 1,400

Re: A problem with Exists

Those issues should be fixed in last night's ES2008 Beta II release.

David Neal Parsons
www.entityspaces.net

Report abuse

04-29-2008, 2:37 AM 9084 in reply to 9051

Ammo
Joined on 04-09-2008
Posts 10

Re: A problem with Exists

Beta2 testing:

UsersQuery uq = new UsersQuery("uq");

UsersQuery uq1 = new UsersQuery("uq1");

uq1.Select(uq1.UserID);

uq.Where(uq.And(uq.UserID.In(uq1)));

null referrence exception :(

Can you post fast bug fix?

Report abuse

04-29-2008, 4:31 AM 9090 in reply to 9084

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

We really don't support the older AND and OR methods, you're query is not correct at all, it should be like this:

uq.Where(uq.UserID.In(uq1)));

In the future use | for OR and & and AND however in this case there is no reason for it's use. We really don't just post "fixes". Our fixes come in schedule maintenance releases or in this case a future beta, just report the issues and we will fix them.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

04-30-2008, 6:29 AM 9108 in reply to 9090

Ammo
Joined on 04-09-2008
Posts 10

Re: A problem with Exists

ApplicationsQuery uq = new ApplicationsQuery("uq");

ApplicationsQuery uq1 = new ApplicationsQuery("uq1");

ApplicationsQuery uq2 = new ApplicationsQuery("uq2");

uq1.Select(uq1.AppId);

uq2.Select(uq2.AppId);

uq.Where(uq.AppId.In(uq1) & uq.AppId.In(uq2));

exception again :(

Report abuse

04-30-2008, 7:08 AM 9109 in reply to 9108

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

Okay, you could really help us out here by doing two things.

1) Post the call stack and the entire exception.

2) Trap the exception and post the Query.es.LastQuery text

Also, is that you're entire query above? It seems to me you are not posting the entire query? Your query seems to change on every post? We can fix things but need some help from you.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

04-30-2008, 8:47 AM 9112 in reply to 9109

David.Parsons
Joined on 01-14-2007
Posts 1,400

Re: A problem with Exists

Mike, it throws a System.NullReferenceException on the Load. BTW, change this:

uq.Where(uq.AppId.In(uq1) & uq.AppId.In(uq2));

to this:

uq.Where(uq.AppId.In(uq1), uq.AppId.In(uq2));

and it works.

FYI, as I step thru, the query passed in the GetFromStatement for JoinAlias "uq2" has a null providerMetadata, so it is actually this test in line 651 of CreateFullName that has the null reference:

iQuery.ProviderMetadata.Catalog != null

David Neal Parsons
www.entityspaces.net

Report abuse

04-30-2008, 8:49 AM 9113 in reply to 9112

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

Okay, thanks David, I will debug and fix tonight ...

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

05-06-2008, 5:17 AM 9193 in reply to 9113

Ammo
Joined on 04-09-2008
Posts 10

Re: A problem with Exists

I simplified examples of queries with problems of course(for you).

Real queries can be very difficult.

As i understood, i should wait release version for fixing this problems

But i should know, what way should i use now (on developing stage)

So questions:

1st) I have SQL query:

SELECT *

FROM a

WHERE a.c1 in (select b.c1 from b)

AND (a.c2 in in (select c.c1 from c) OR a.c3 in in (select d.c1 from d))

Should I use

a.Where(a.c1.In(b),a.Or(a.c2.In(c),a.c3.In(d)));

a.Where(a.c1.In(b) & (a.c2.In(c) | a.c3.In(d)));

2nd) Why are you stopping support "older AND and OR methods"? I used them in many places as they were very useful in many cases.

At least you could make the old code using these methods not compilable anymore. Now I cannot see what is wrong in my code on compilation stage. If I would get at least some compiler warnings like 'method XXX is obsolete, use method YYY instead', I could always keep my code actual to latest versions and avoid surprises like the one discussed in this thread.

Overall, keeping backward compatibility from version to version is not in list of your great advantages present days :(

Report abuse

05-06-2008, 5:24 AM 9194 in reply to 9193

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

We are not actually removing the OR and AND methods, they are going to remain, however, whenever somebody has an issues and posts code using them we will post how to do it in our new way, if there is a bug found with AND() or OR() we will most likely not fix it if the new & and | methods suffice. As far as backwards compatibily I do not agree, even now you can run ES2007 generated classes against the ES2008 assemblies. We have been very good about breaking changes.

We will be releasing an updated beta this weekend. The issues with the "In" clause will be fixed in that release.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

05-06-2008, 8:05 PM 9203 in reply to 9194

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

I am not trying to be difficult here it's just that I still do not under the bug. This code works perfectly for me?

Code:

ProductsQuery p = new ProductsQuery("p");
CategoriesQuery c = new CategoriesQuery("c");
EmployeesQuery e = new EmployeesQuery("e");

c.Select(c.CategoryID);
e.Select(e.EmployeeID);
p.Select(p.CategoryID); 

p.Where(p.CategoryID.In(c), p.CategoryID.In(e));

ProductsCollection coll = new ProductsCollection();
coll.Load(p);

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

05-06-2008, 8:37 PM 9204 in reply to 9203

David.Parsons
Joined on 01-14-2007
Posts 1,400

Re: A problem with Exists

Mike, This will fail:

p.Where(p.CategoryID.In(c) & p.CategoryID.In(e));

David Neal Parsons
www.entityspaces.net

Report abuse

05-06-2008, 8:58 PM 9205 in reply to 9204

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,829

Re: A problem with Exists

I got it, just fixed it, finally !!

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse