Entity framework (3.5): How to translate a certain LINQ query to eSQL?

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.

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)

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.

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.

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);