IQueryable, Where, Guid and

Question

I'm working my way through the MVC Storefront code and trying to follow the path of a repository, a service and a model that is a poco, outside of the dbml/data context. It's pretty easy to follow actually, until I started writing tests and things failed in a way I just don't understand.

In my case, the primary key is a uniqueidentifier instead of an int field. The repository returns an IQueryable:

public IQueryable<Restaurant> All()
{
    return from r in _context.Restaurants select new Restaurant(r.Id)
        {
             Name = r.Name
        };
 }

In this case, Restaurant is a Models.Restaurant of course, and not _context.Restaurants.Restaurant.

Filtering in the service class (or in repository unit tests) against All(), this works just as expected:

var results = Repository.All().Where(r => r.Name == "BW<3").ToList();

This works just fine, has one Model.Restaurant. Now, if I try the same things with the pkid:

var results = Repository.All().Where(r => r.Id == new Guid("088ec7f4-63e8-4e3a-902f-fc6240df0a4b")).ToList();

If fails with:

The member 'BurningPlate.Models.Restaurant.Id' has no supported translation to SQL.

If seen some similiar posts where people say it's because r => r.Id is [Model.Restaurants] is a class level the linq2sql layer isn't aware of. To me, that means the the first version shouldn't work either. Of course, if my pk is an int, it works just fine.

What's really going on here? Lord knows, it's not very intuitive to have one work and one not work. What am I misunderstanding?

Answer 1

Not having actually typed this code out, have you tried

var results = Repository.All().Where(r => r.Id.Equals(new Guid("088ec7f4-63e8-4e3a-902f-fc6240df0a4b")).ToList()

Ninja

Answer 2

this probably has to do with fact that you trying to instantiate the guid in the query, and I think that LINQ to SQL is trying to convert that to actual SQL code before the object is created.

Try instantiating before the query and not on the query.

Answer 3

I think the problem here is due to using a constructor overload, and expecting the query to fill it in. When you do a projection like this, you have to put all the things you want to be in the projection query in the actual projection itself. Otherwise Linq won't include that in the SQL query.

So, rewrite your bits like so:

return from r in _context.Restaurants select new Restaurant()
        {
             Id=r.Id,
             Name = r.Name
        };

This should fix it up.