none
query fails at runtime: local sequence cannot be used RRS feed

  • Question

  • Hi, I need to write a where clause that in English reads like "where, for each t1 in r1, there exists a t2 in r2 such that t1.user == t2.user and t1.name == t2.name". Since r2 is a local sequence, the query crashes at runtime. However, I see no way to convert this query to use the Contains() function.

    Here is the code, which compiles but crashes at runtime:

      q = q.Where(r =>  // q is a IQUeryable to which I am adding a where clause

                            (from at in dd.atrule_tags
                             where at.id_atrule == r.id

                             from tt in dd.tags
                             where tt.id == at.id_tag
                             select tt).All(
                                (tr =>


                                (from lt in lr.ruTags  // lr.ruTags is a local enumerable, not allowed in LINQ TO SQL
                                 where lt.ltcLt.ltIdUserOwner == tr.id_user_owner
                                 where lt.ltcLt.ltName == tr.name
                                 select lt).Any()

                                        )
                                                        )
                                                        );

    Probably I have to convert the query to something like

    lr.ruTags.Contains(x)

    but I don't see how.

    Thanks for suggestions.

    Tuesday, January 22, 2013 10:01 AM

All replies

  • may Let operator will help you...

    This link will help you...

    http://weblogs.asp.net/zeeshanhirani/archive/2010/06/14/watch-out-for-let-operator-in-linq-to-entities.aspx

    Tuesday, January 22, 2013 10:05 AM
  • thanks, I know the let operator but I don't see how it helps in this case...
    Tuesday, January 22, 2013 10:06 AM
  • I could find the problem more like yours with solution. Take a look, maybe it can help you

    http://stackoverflow.com/questions/1712105/linq-to-sql-exception-local-sequence-cannot-be-used-in-linq-to-sql-implementati


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Tuesday, January 22, 2013 10:13 AM
  • Thank you, as I said, I know the problem can be solved using Contains instead of Any, but in my case I don't know how to translate the query to use Contains.
    Tuesday, January 22, 2013 10:14 AM
  • Hi,

    Please check if this works:

    q = q.Where(r =>  // q is a IQUeryable to which I am adding a where clause

                            (from at in dd.atrule_tags
                             where at.id_atrule == r.id

                             from tt in dd.tags
                             where tt.id == at.id_tag
                             select tt).All(
                                (tr =>


                                (from lt in lr.ruTags  // lr.ruTags is a local enumerable, not allowed in LINQ TO SQL 
                                 where lt.ltcLt.ltIdUserOwner == tr.id_user_owner
                                 where lt.ltcLt.ltName == tr.name
                                 select lt).Count() != 0

                                        )
                                                        )
                                                        );

    Thursday, January 24, 2013 8:32 AM
  • Hi, unfortunately your code gives the same error.
    Thursday, January 24, 2013 2:31 PM
  • Hi Maurizio,

    Welcome to the MSDN forum.

    I am trying to involve a senior expert into your thread. Please wait for the response. Sorry for any inconvenience.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 25, 2013 8:50 AM
  • To get what you want can be done.  In Linq to SQL if you use a Contains condition it will translate it into an "In" statement in SQL.

    So to get what you want you need to create a collection "t2" that has a concatenation of user and name that you want to use to qualify the results.  You would then use a concatenation of the same two elements of the "t1" data on the server.

    If you check the SQL generated by this senario you will see the "in" clause.  

    While this is all good there is a potential problem.  There is a limit to the length of a SQL statement so if your t2 collection is large this method may not work.

    So you would end up with something like:

    1.  Create a t2 collection with strings containing the concatentation of user and name.

    2.  Create the linq statement that would look something like: (not syntactical correct)

    From q in t1 where t2.contains( t1.user+t1.name) select q

    Hope this helps

    LS


    Lloyd Sheen

    Wednesday, January 30, 2013 10:53 PM
  • THank you, the problem with your solution is that I don't know what syntax to use to define the concatenation "t1.user + t1.name" in such a way that it can be translated into SQL by linq.

    I tried creating a c# class called "NameAndIDUserOwner", and then using Let x = new NameAndIdUserOwner(... )   and Contains(...) in the Linq query, but I get an error like "no supported translation to SQL" .

    Thursday, January 31, 2013 3:55 PM
  • You cannot have local classes in a Linq statement and expect them to work on a server. 

    I did a small sample just to make sure it works but it is in VB, but here it is:

        Private Sub Button_Click(sender As System.Object, e As System.Windows.RoutedEventArgs)
            Dim dc As New DataClasses1DataContext
    
            Dim containsList As List(Of String) = (From s In dc.RSongInfos Where s.Artist.Contains("Stones") Select s.Artist + s.Title).ToList
    
            Dim res = (From ss In dc.RSongInfos Where containsList.Contains(ss.Artist + ss.Title) Select ss).ToString
            Dim stp As Integer = 1
        End Sub
    

    While the test is not very useful in real life it shows that the approach works.  

    The first Linq statement I am just using to populate the contains list.  You can get this from any source and should contain a concatenation of the elements you wish to filter by.

    The second shows the use of Contains.  When the Linq statment is compiled it then shows as :

    SELECT [t0].[SongNumber], [t0].[FolderNumber], [t0].[FileName], [t0].[Artist], [t0].[Title], [t0].[Genre], [t0].[Album], [t0].[TrackNumber]
    FROM [dbo].[RSongInfo] AS [t0]
    WHERE ([t0].[Artist] + [t0].[Title]) IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88,

    As you can see it gets translated to a SQL IN clause.

    Hope this helps

    LS


    Lloyd Sheen

    Monday, February 4, 2013 3:25 PM
  • Hi Maurizio,

    I think in your scenario you can use the other overload of the Contains method - http://msdn.microsoft.com/en-us/library/bb339118.aspx

    This method allows you to write your own comparer to be used by the Contains method. In your own comparer class you can then test your condition which is "where, for each t1 in r1, there exists a t2 in r2 such that t1.user == t2.user and t1.name == t2.name".

    So now you can write something like this -

    lr.ruTags.Contains(tr, custom_comparer)

    Hope this helps!

    Thanks,

    Kunal (MSFT)

    Friday, February 8, 2013 9:16 PM
  • You cannot do that and have it run on the server.  Only things that translate directly to SQL can be used to query the server.  Otherwise you have to download the items using SQL and then filter them on your workstation which could a large job and would most certainly send more data from the server to the workstation than needed.

    LS


    Lloyd Sheen

    Friday, February 8, 2013 9:51 PM