1

I have the following LINQ query that I need to translate to Entity SQL /eSQL):

return (ObjectQuery<User>) from user in Users
   where !user.Roles.Any(r => r.AnIntegerProperty < 0)
   select user;

User.Roles is an navigation property to the n:m relation to Roles and there also is a Role.Users navigation property the other way round. There aren't User_Roles or Roles_User Entities available in the model, and I can't add these.

I also can't use the LINQ statement here, because I need to add .OrderBy("it." + propertyname) (comes from another source, can't change that too) later on which is not possible if the ObjectQuery is build with linq.

So how do I translate this to eSQL? And where can I find good eSQL samples? I searched for a whole day until now and must admit that eSQL reference is lousy and there aren't any usable examples around the web.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Sebastian P.R. Gingter
  • 5,955
  • 3
  • 31
  • 73
  • I don't understand "I also can't use the LINQ statement here, because I need to add .OrderBy("it." + propertyname)". QueryBuilder methods (e.g., the `OrderBy("it." + ...` you suggest) work fine with LINQ. Just cast the `IQueryable` to `ObjectQuery` and go. – Craig Stuntz Mar 17 '10 at 14:24
  • I tried it, but then at runtime I get this error: "Query builder methods are not supported for LINQ to Entities queries." – Sebastian P.R. Gingter Mar 17 '10 at 15:15
  • You have to do the QB part first. – Craig Stuntz Mar 18 '10 at 14:01
  • I can't. I provide the data that is used from foreign code later on. And that foreign code uses query builder methods on the ObjectQuery objects I have to provide. They do ordering and i.e. paging later on and maybe even some additional filtering. But I have to provide an ObjectQuery object that already filters the data by some business rules and that is usable for others. Only thing I want is to translate the query into entity SQL so that this works... – Sebastian P.R. Gingter Mar 18 '10 at 14:16

4 Answers4

1

In case you haven't find solution, this will work 

SELECT VALUE u FROM YourDataContextEntities.Users AS u WHERE NOT EXISTS(SELECT r FROM u.Roles AS r WHERE r.AnyIntegerProperty < 0)
Syma
  • 574
  • 1
  • 4
  • 11
0

I think that dynamic linq library may be solution here:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

You can create filtering expressions using dynamic property names, so there is no need to make translations.

LukLed
  • 31,452
  • 17
  • 82
  • 107
  • But there is no dynamic property name involved in my statement? As stated, the .OrderBy("it." + name) comes from another source and I can't change that and additionally I must not break the existing foreign code relying on the classes I currently work on. – Sebastian P.R. Gingter Mar 17 '10 at 12:14
  • @Sebastian P.R. Gingter: `.OrderBy("it." + name)` should work. I don't know what is the problem here. – LukLed Mar 17 '10 at 12:39
  • Doesn't work. You can't use .OrderBy("esqlStatement") on a ObjectQuery that was contructed in code using LINQ. The error message that comes at runtime is: "Query builder methods are not supported for LINQ to Entities queries.". If I build the ObjectQuery using eSQL (with a simpler statement like taking users older than 17) it works. So I need to use eSQL to build the query I stated but I am totally stuck on that. – Sebastian P.R. Gingter Mar 17 '10 at 12:50
0

I'd write it something like 

contexte.Users.Where("NOT EXISTS (SELECT VALUE r FROM it.Roles AS r WHERE  r.AnIntegerProperty < 0)")

not tested but I've already tried something similar so thi should work for you.

moi_meme
  • 9,180
  • 4
  • 44
  • 63
0

It is difficult to find an answer without knowing the specifics of what is available on Users and Roles. However, given what you have said, will the following work:

return (ObjectQuery<User>) from user in Users
                            where !(from role in dataContext.Roles
                                    where role.AnIntegerProperty < 0
                                    select role.UserId).Contains(user.UserId);
Thomas
  • 63,911
  • 12
  • 95
  • 141