CODEWITHSUNDEEP
c#linqentity-framework
paulpeters
paulpeters

Reputation: 158

LINQ to Entities can't compile a store expression

I use Entity Framework 6.0.1 and I have next problem:

I have the next db structure: 

public class User 
{
   int Id { get; set;}
   string E-mail {get; set;}
   string Name {get; set;}
   ...
}

class House 
{
   string Id {get; set;}
   string Name { get; set; }
   string Street { get; set; }
   . . . 
   IQueryable<User> Users { get; set; }
}

Every House may be linked to many Users. Any User may be linked to many Houses.

I need to create a query in which to get a list of houses to which is attached a particular user I know only user Id.

I wrote the next statement:

var houses = this.context.Houses
                .Where(house => house.Users.Any(i => i.Id == my_searched_user_id));

but I get error: LINQ to Entities does not recognize the method 'System.String ElementAtOrDefault[String](System.Collections.Generic.IEnumerable`1[System.String], Int32)' method, and this method cannot be translated into a store expression.

I change it to 

var houses = this.context.Houses
                .Where(house => house.Users.**ToList()**.Any(i => i.Id == my_searched_user_id));

but without any luck :(

Upvotes: 0

Views: 147

Answers (2)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

You should not define your Users collection as an IQueryable. It should instead be an ICollection:

public virtual ICollection<User> Users {get;set}

I'm surprised that even compiles... but still, that's not really your problem. Your problem is in code you have not shown. You need to show all the code because something else is causing this error, something to do with indexing a string variable.

Upvotes: 0

ntl
ntl

Reputation: 1299

You added ToList in wrong place. Try next code:

     var houses = this.context.Houses
            .Where(house => house.Users.Any(i => i.Id == my_searched_user_id))
            .ToList();

ToList will cast IQueryable to IEnumerable, so the query will be executed.

Upvotes: 1

Related Questions