entity-frameworklinq-to-entitiesentity-sql

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.

Solution

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