3

Why am I getting:

The method 'Where' cannot follow the method 'Select' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.

...when using the WHERE clause, like when calling:

XrmServiceContext.CreateQuery<Contact>().Project().To<Person>().Where(p => p.FirstName == "John").First();

?

This works:

XrmServiceContext.CreateQuery<Contact>().Project().To<Person>().First();

Also this works:

XrmServiceContext.CreateQuery<Contact>().Where(p => p.FirstName == "John").First();

I'm using AutoMapper QueryableExtension.

Additional info:

  • I don't want to call ToList() before the Where clause. I know it will works that way.
  • CreateQuery<TEntity>() returns IQueryable<TEntity>.
Community
  • 1
  • 1
Joao Leme
  • 9,598
  • 3
  • 33
  • 47

3 Answers3

2

It's because whatever query provider you are using isn't able to handle this. It's not invalid in the general case; in fact most query providers do support filtering after projecting. Certain query providers simply aren't as robust as others, or they are representing a query model that is less flexible/powerful than the LINQ interface (or both). As a result, LINQ operations that are correct from the C# compiler's point of view might still not be translatable by the query provider, so the best it can do is throw an exception at runtime.

Servy
  • 202,030
  • 26
  • 332
  • 449
  • It would make sense if the following returns the same error, but it doesn't: XrmServiceContext.CreateQuery().Where(p => p.FirstName == "John").First();. So the provider can handle. It's when using AutoMapper that I get the error. – Joao Leme Apr 23 '13 at 17:16
  • 1
    @JoaoLeme The automapper is, internally, doing a projection (i.e. it's calling `Select` on the IQueryable). That is how it's Mapping the object from a `Contact` to a `Person`. Since your query provider (apparently) can't filter after projecting, doing that breaks. Removing the projection makes it work, as does removing the filter. You can do one, the other, or filter before projecting, so long as you don't filter after you project. For more details it is honestly specific to the query provider you're using. – Servy Apr 23 '13 at 17:18
  • 1
    @JoaoLeme It's in the query provider as Servy says. It works with entity framework. – Gert Arnold Apr 23 '13 at 19:47
  • So the problem is the "select" on line 42 of https://github.com/AutoMapper/AutoMapper/blob/master/src/AutoMapper/QueryableExtensions.cs? No work around/alternative without the selec? – Joao Leme Apr 23 '13 at 20:21
  • @JoaoLeme You can either filter before the select, not select, not filter, filter on the client side, rather than the server side, or use a different query provider that is capable of handling a filter after a projection. – Servy Apr 23 '13 at 20:24
  • @Servy Would I be able to map/translate a person linq expression to a contact linq expression? That way I could pass the expression as a parameter and call it before the .Project().To(). – Joao Leme Apr 23 '13 at 20:43
  • @JoaoLeme Why don't you try it and see. If, after spending some time on the issue, you can't come up with anything, that sounds like it could be another question entirely. It's rather separate from this question. – Servy Apr 23 '13 at 20:44
0

Why don't you just move the where so it is before the projection? It will result in a single query being executed which filters and projects:

XrmServiceContext.CreateQuery<Contact>().Where(p => p.FirstName == "John").Project().To<Person>().First();
Paul Hiles
  • 9,558
  • 7
  • 51
  • 76
0

Looking at AutoMapper's instructions for the QueryableExtensions it has an example showing the Where clause before the projection. You need to refactor your code to support this model, as opposed to placing the Where clause after the projection.

public List GetLinesForOrder(int orderId)
{
  Mapper.CreateMap()
    .ForMember(dto => dto.Item, conf => conf.MapFrom(ol => ol.Item.Name);

  using (var context = new orderEntities())
  {
    return context.OrderLines.Where(ol => ol.OrderId == orderId)
             .Project().To().ToList();
  }
}

Given the limitations of Dynamic CRM's LINQ provider you should not expect AutoMapper to necessarily get the LINQ query correct.

There is actually a logic behind this design. As the developer you create a working Where clause. You then let AutoMapper's Project().To() define the select statement. Since CRM's LINQ provider has support for anonymous types in it should work correctly. The purpose of projection in AutoMapper is to limit the data retrieved from each class to only that needed for the projected to class. It is not intended to write a Where clause based on the projected to class.

Nicknow
  • 7,154
  • 3
  • 22
  • 38
  • The CRM Linq provider has this limitation, but not EF. Filtering (the Where clause) after projection works just fine with Entity's Framework Linq provider. – Joao Leme Apr 25 '13 at 13:18
  • Agreed. CRM's Linq provider isn't as flexible as EF's since it has to convert to CRM's QueryExpression and can't use straight SQL. That said, LINQ providers come in all shapes and sizes which is why I would recommend doing your `Where` before the projection, so that the Where is not modified by AutoMapper. I can't think of reason that you would need to do the `Where` after projection. – Nicknow Apr 25 '13 at 16:30
  • I'm trying to avoid changing my service layer, by simply changing my data access layer (Repository). The "Where" is on my service layer (business logic) with my domain models. I get access to my PM (persistent model - CRM in this case) calling "Repository.Table". The "Where" clause before projection must be done using the PM, so It would be much easier to have it after projection, BUT since I've been able to convert (map) the linq expression from DM to PM I'm now adjusting my service layer to pass the linq expression (where clause) as a parameter. – Joao Leme Apr 26 '13 at 12:45