locked
Query with SQL IN for related EntitySet RRS feed

  • Question

  • Hi all,

    Hopefully somebody can help me with the following.
    I've set up an ADO.Net Data Service with an EDM that contains some related entities:

    Schedule - DestinationGroup - Destination

    A schedule can be created and assigned to some destinations (devices like PC's or the likes) through a destinationgroup.
    I already entered some testing data, but the difficulty is to extract the schedules that are assigned to certain destinations.
    It basically comes down to a SQL IN equivalent for LINQ.

    I'm trying this:

    Dim Guid1 As New Guid("dbb73d43-a3cb-4356-9137-0451e51f9365")

    Dim Guid2 As New Guid("e8a1da7d-73df-4fe9-9e54-7960702c0568")

    Dim Guid3 As New Guid("441dbfef-1329-4688-bc29-ff0b5e47b892")

    Dim GuidList As New List(Of Guid)(New Guid() {Guid1, Guid2, Guid3})


    Dim
    DestFunc As New Func(Of DataServiceReference.Destination, Boolean) _

    (Function(D As DataServiceReference.Destination) GuidList.Contains(D.DestinationID))


    Dim
    Query = From Sched In MyEntities.Schedules _

    Where Sched.DestinationGroup.Destinations.Any(DestFunc) _

    Select Sched


    This works perfectly in the ObjectContext from the Entity Framework, but not in ADO.NET Data Services's DataServiceContext.
    I get an error simply saying "The method 'Any' is not supported."
    I've seen some topics on this, stating that the DataServiceContext doesn't support set-based operations, and others that try a workaround like:

    WHERE IN clause?
    http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/

    But to no avail.


    Anyone who can help me out with this?
    Any help would be greatly appreciated!

    Friday, January 9, 2009 3:58 PM

Answers

  • Hi ,
     
    I changed the blog post to use an expression to extract the value of the key property ,
    change your query to look something like this :

    Dim Query = MyEntities.Schedules.IsIn(Of Destinations,Of Guid)(GuidList,Func(Of Destination,Guid)( dest.DestinationId )    )

    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Wednesday, May 6, 2009 5:53 PM
    Moderator

All replies

  • Hello,

    Any() linq operator (or other set operators) is not supported in ADO.Net data services as the current URI sintax does not define any set operations.

    Best regards,
    Monica
    Saturday, January 10, 2009 12:47 AM
    Moderator
  • Hi Henk,
     Monica is right , the Linq provider for out client library does not implement the ANY operator as our URI syntax does not define an function similar to the  ANY  method . But ,  you can use the Expression APIs to create an Expression that can provide support for the IS-IN or COntains function in the  client library , as is illustrated in my blog post here :
    http://blogs.msdn.com/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data-services.aspx

    Which gives you the ability to do this query :

    Dim Query = From Sched In MyEntities.Schedules _
    Where Sched.DestinationGroup.Destinations.IsIn(Of Desntinations)(GuidList,"DestinationID") _
    Select Sched

    Hope this helps

    Phani Raj Astoria
    Sunday, January 11, 2009 7:09 AM
    Moderator
  • Hi Monica and Phani,

    Thank you for your quick replies!

    Yes, I've seen a number of topics on the web that state that set operations are not supported by ADO.Net Data Services, but I've also seen attempts in getting a workaround for that.

    Thanks Phani for your suggestion!
    It seems the thing I'm looking for.
    I will give it a try and let you know.

    Monday, January 12, 2009 7:51 AM
  • Hi Phani,

    I've tried your solution, but encountered some problems.

    First there was a type mismatch:
    Error: 'IsIn' is not a member of 'System.Collections.ObjectModel.Collection(Of DataServiceReference.Destination)'.
    Sched.DestinationGroup.Destinations seemed to be of type System.Collections.ObjectModel.Collection(Of DataServiceReference.Destination) instead of type DataServiceQuery(Of DataServiceReference.Destination) as required by the Extension method.

    So I changed the type in the Extension method to affect types of System.Collections.ObjectModel.Collection(Of DataServiceReference.Destination).
    But then I got the following message:
    Error: Value of type 'System.Data.Services.Client.DataServiceQuery(Of DataServiceReference.Destination)' cannot be converted to 'Boolean'. 
    The Extension method returns a result set in the form of a DataServiceQuery, not a boolean that is required for the where clause in the LINQ query.

    I'm a little puzzled as to adapt the Extension method to return a boolean value, valid for my query.
    I don't know if that's easily done with the LINQ Expressions in the Extension method as I am not that familiar with Expressions.
    Changing

    Return DirectCast(Query.Where(filterLambdaExpression), DataServiceQuery,(Of T))

    into

    Return DirectCast(Query.Any(filterLambdaExpression), Boolean)

    obviously rendered (as ".Any(" is not supported):
    The expression [10007].DestinationGroup.Destinations.IsIn(value(System.Collections.Generic.List`1[System.Guid]), "DestinationID") is not supported.

    Any ideas on what needs to be adapted to get it to work?

    Monday, January 12, 2009 11:26 AM
  • Hi Henk,
    The Solution I pointed to produces an IQueryable<T> with the ANY expression already applied to it ,
     given a set of values and a property to filter by.
    It goes through the choices passed in and simulates an IN query using the other standard operators supported by the DataServiceContext's Linq translator.
    It produces a FIlter Expression that returns an Entity that satisifies the condition .

    You can change your code to be :

    1 Dim Query = From Sched In MyEntities.Schedules _ Where   
    2 Sched.DestinationGroup.Destinations.IsIn(Of Destinations)(GuidList,"DestinationID")   
    3 Select Sched 

    By default , the query produces an expression that looks like the ANY expression , you dont need to apply another expression on top of it
    to get the desired result .

     


    Phani Raj Astoria
    Monday, January 12, 2009 6:29 PM
    Moderator
  •  

    Hi Phani,

    That is what I did, I changed the code like you told me, but it caused the aforementioned errors.
    The IsIn Extension method produces an IQueryable indeed, but that's not accepted in the Where part of the LINQ query.
    "Where" expects an expression that results in a boolean, not an IQueryable.

    I've seen that your solution works perfectly for a direct result set, i.e. an IQueryable, as in your example:

    var customersAround = nwContext.Customers.IsIn<Customers>(citiesIWillVisit, c=> c.City);

    But using it in the Where part of the LINQ query is something else.
    Then a Boolean result is expected.

    Please advise...


    Henk.

    Tuesday, January 13, 2009 8:50 AM
  • Hi ,
     
    I changed the blog post to use an expression to extract the value of the key property ,
    change your query to look something like this :

    Dim Query = MyEntities.Schedules.IsIn(Of Destinations,Of Guid)(GuidList,Func(Of Destination,Guid)( dest.DestinationId )    )

    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Wednesday, May 6, 2009 5:53 PM
    Moderator