Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

LINQ to Entities and using a collection in the query...

Back to Forum: Tech Off
  • User profile image
    ulas

    Hey all,

    This is my first post on channel9 so let me start by saying "Hi!" Smiley I hope the holiday season has been treating you all well.

    Now, onto the problem I have been having with LINQ...

    I am new to LINQ and I have been trying to do something pretty simple with a DB I am playing around with: There are 3 tables, User, UserToClub, and Club. UserToClub maps clubs to a given user and has the user's ID and the club's ID as FKs. When I generated EDMX for this DB I got two Entities - User and Club. I just want to filter clubs based on a given list of users, in this case based on a given city. The code compiles fine but I get a runtime error when I try to call Count on myClubs which I guess is when the statement gets actually evaluated. The error is:

     LINQ to Entities does not recognize the method 'Boolean Contains(Club)' method, and this method cannot be translated into a store expression. (System.NotSupportedException)

    Any help would be appreciated. The code follows:

    //Pick user with the right ID
    var myUser = from aUser in myDB.User
                  where aUser.City.Equals(city)
                  select aUser;

    //Filter clubs based on user ID
    var myClubs = from aUser in myUser
                  from aClub in myDB.Club
                  where aUser.Club.Contains(aClub)
                  select aClub;

    It seems pretty straightforward to me but I guess I am missing something (yeah, "Doh!", I know...).


    Just when I was starting to really love LINQ...

    ulas

  • User profile image
    dpratt71

    The most straightforward way I can think to do this involves stepping outside the "from...in" syntax a bit:

    var myClubs =
        from aClub in myDB.Club
        where aClub.Users.Any(aUser => aUser.City == city)
        select aClub;

    This assumes that the navigation property from Club to User is named "Users" (if you haven't changed anything, it's probably "User").

  • User profile image
    ulas

    dpratt71 said:

    The most straightforward way I can think to do this involves stepping outside the "from...in" syntax a bit:

    var myClubs =
        from aClub in myDB.Club
        where aClub.Users.Any(aUser => aUser.City == city)
        select aClub;

    This assumes that the navigation property from Club to User is named "Users" (if you haven't changed anything, it's probably "User").

    Ah, thanks for the reply Smiley I will give this a try in a bit and let you know what happens Smiley

    Quick question though - what is the main reason why my initial version was not working? Did I miss something obvious or is this a matter of lack of support in Linq for a particular type of query? (during runtime since VS does not complain at compiletime).

    ulas

  • User profile image
    dpratt71

    ulas said:
    dpratt71 said:
    *snip*
    Ah, thanks for the reply Smiley I will give this a try in a bit and let you know what happens Smiley

    Quick question though - what is the main reason why my initial version was not working? Did I miss something obvious or is this a matter of lack of support in Linq for a particular type of query? (during runtime since VS does not complain at compiletime).

    ulas

    Basically, it has to do with the fact that the Entity Framework is handed an object graph that represents your query. EF then has to translate that representation into a SQL query. There are many things you can do in code that EF simply cannot translate.

  • User profile image
    ulas

    dpratt71 said:

    The most straightforward way I can think to do this involves stepping outside the "from...in" syntax a bit:

    var myClubs =
        from aClub in myDB.Club
        where aClub.Users.Any(aUser => aUser.City == city)
        select aClub;

    This assumes that the navigation property from Club to User is named "Users" (if you haven't changed anything, it's probably "User").

    Yup, that worked like a charm Smiley Thanks a lot!

    I hope VS will be able to tell what is not supported in LINQ queries at some point. Or even better, may be the Entity Framework will be able to translate everything you can do in code Wink

  • User profile image
    stevo_

    ulas said:
    dpratt71 said:
    *snip*
    Yup, that worked like a charm Smiley Thanks a lot!

    I hope VS will be able to tell what is not supported in LINQ queries at some point. Or even better, may be the Entity Framework will be able to translate everything you can do in code Wink
    We'll it can't do that (translate everything), but they do have a pattern for letting you describe a proceedure to use for a given method. 

    http://blogs.msdn.com/efdesign/archive/2008/10/08/edm-and-store-functions-exposed-in-linq.aspx

    It would be nice if the model was more 'stood off' I feel, meta data is great - but when you don't even control the source, its just as useless..

    Even still the design is imperfect but thats the way the ecosystem is for this software..

  • User profile image
    ulas

    stevo_ said:
    ulas said:
    *snip*
    We'll it can't do that (translate everything), but they do have a pattern for letting you describe a proceedure to use for a given method. 

    http://blogs.msdn.com/efdesign/archive/2008/10/08/edm-and-store-functions-exposed-in-linq.aspx

    It would be nice if the model was more 'stood off' I feel, meta data is great - but when you don't even control the source, its just as useless..

    Even still the design is imperfect but thats the way the ecosystem is for this software..
    Ah, thanks for the link. I am pretty happy with the expressiveness of Linq in terms of simplifying a lot of mundane tasks one tends to do in code. It is just a bit of a downer to get everything compiling happily and then hit a runtime exception when the expression is evaluated. I realize, in a way, this is similar to trying to dividing one variable with another which may happen to be zero which would throw you a runtime exception but I just feel like checking which functions you use in the linq expression at compile time wouldn't be as difficult.

  • User profile image
    MasterPi

    ulas said:
    stevo_ said:
    *snip*
    Ah, thanks for the link. I am pretty happy with the expressiveness of Linq in terms of simplifying a lot of mundane tasks one tends to do in code. It is just a bit of a downer to get everything compiling happily and then hit a runtime exception when the expression is evaluated. I realize, in a way, this is similar to trying to dividing one variable with another which may happen to be zero which would throw you a runtime exception but I just feel like checking which functions you use in the linq expression at compile time wouldn't be as difficult.
    You can use the lambda equivalent too:

    myDb.user.Where(x=>x.City.Equals(city)).Where(y=>y.Club.Contains(aClub));

    I think IntelliSense is better at helping you with the lambda.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.