Specification inside LINQ with EF 4.3

Question

I stumbled trying to use my specification inside a LINQ query. The trouble here is with my specification with params.

Let's fake a simple scenario:

public class Car {
    public Guid Id { get; set; }
    public string Color { get; set; }
    public int UsedPieces { get; set; }
    // whatever properties
}

public class Piece {
    public Guid Id { get; set; }
    public string Color { get; set; }
    // whatever properties
}

public static class PieceSpecifications : ISpecification<Piece> {
    public static ISpecification<Piece> WithColor(string color) {
        return new Specification<Piece>(p => p.Color == color);
    }
}

what I'm actually trying to do

// Get accepts ISpecification and returns IQueryable<Car> to force just one call to database
var carWithPieces = _carRepository.Get(CarSpecifications.UsedPiecesGreaterThan(10));

var piecesWithColor = from p in _pieceRepository.Get()
                      let car = carWithPieces.FirstOrDefault() // entire query will does one call to database
                      where PieceSpecifications.WithColor(car.Color).IsSatisfiedBy(p) // unfortunately it isn't possible
                   // where p.Color == car.Color -> it works, but it's not what I want
                      select p;

I know it's a little bit confusing, but I'm trying to avoid a lot of roundtrips inside my real(big) scenario and I know that actually it's impossible to do using raw LINQ with entity framework. I'm tired to try so many blogs and failed(mine) approaches. Someone knows some real good approach. There's another way to do that?

Error

System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsSatisfiedBy(App.Model.Piece)' method, and this method cannot be translated into a store expression.

UPDATE

Basic Specification Pattern

public class Specification<T> : ISpecification<T> {
    private readonly Expression<Func<T, bool>> _predicate;

    public Specification(Expression<Func<T, bool>> predicate) {
        _predicate = predicate;
    }

    public Expression<Func<T, bool>> Predicate {
        get { return _predicate; }
    }

    public bool IsSatisfiedBy(T entity) {
        return _predicate.Compile().Invoke(entity);
    }
}

UPDATE

It's pretty easy neat if I do this

// call to database
var car = _carRepository
    .Get(CarSpecifications.UsedPiecesGreaterThan(10))
    .FirstOrDefault();

// Whoah! look I'm working, but calling to database again.
var piecesWithColor = _pieceRepository
    .Get(PieceSpecifications.WithColor(car.Color))
    .ToArray();

Repository

// The Get function inside repository accepts ISpecification<T>.
public IQueryable<T> Get(ISpecification<T> specification) {
    return Set.Where(specification.Predicate);
}

Check my post, I updated with a simple specification implementation. — Kim Tranjan, Mar 14 '12 at 20:39
You are right, you just can't do it. Linq aren't so clever to interpret you custom code into t-sql. Lets wait for .Net 4.5 and EF 5.0 where with enum support (this feature connects with question) we can see some new syntax sugar. I'm also interesting in it. — Denis Agarev, Mar 14 '12 at 20:45

score 1 · Answer 1 · answered Mar 14 '12 at 20:58

1

You can't compile and invoke expression if you want to use it in LINQ-to-entities query. Try to use Predicate directly because LINQ-to-entities builds expression tree which is evaluated by EF LINQ provider and translated to SQL.

IMHO using specification this way doesn't make sense. LINQ-to-entities query is a composite specification. So either use Linq-to-entities or build your own query language using specification and let your repository translate your query to LINQ query.

answered Mar 14 '12 at 20:58

Ladislav Mrnka

360,892
59
660
670

1

My application requires a reuse of predicates and that's why I thought that specification pattern would fit perfectly. I can't imagine how to implement some reusable predicates. – Kim Tranjan Mar 14 '12 at 21:35
What happens if you try using extension `Where` method like this: `.Where(PieceSpecifications.WithColor(car.Color).Predicate)` instead? – Ladislav Mrnka Mar 14 '12 at 21:43
1

I can't do that because `car` is declared inside the query. `let car = carWithPieces.FirstOrDefault()`. Like I said, I'm trying to avoid roundtrips. – Kim Tranjan Mar 14 '12 at 22:04
@KimTranjan: Why can't you do that? Does it fail to compile? Does it produce a runtime error? Does it return the wrong results? – Gabe Mar 17 '12 at 13:22
`let car = carWithPieces.FirstOrDefault()`, declared inside my query. As I said, I'm avoiding roundtrips. It is clear that `.Where(PieceSpecifications.WithColor(car.Color).Predicate)` going to work, but instead of declaring `car` inside the query, I would declare outside, causing roundtrips. `car = _carRepository.Get(CarSpecifications.UsedPiecesGreaterThan(10)).FirstOrDefault())` – Kim Tranjan Mar 17 '12 at 13:32
@KimTranjan Have to agree with Ladislav here... there are workarounds, like Kevin M. mentions too, but the scenario at hand should provoke you to rethink your solution perhaps. [+1] – one.beat.consumer Mar 23 '12 at 23:46
1

There's a problem with trying to use "DRY" in my solution? – Kim Tranjan Mar 24 '12 at 00:37

score 1 · Answer 2 · answered Mar 21 '12 at 08:27

1

Take a look at using AsExpandable extension method.

http://www.albahari.com/nutshell/linqkit.aspx

answered Mar 21 '12 at 08:27

kerem

2,699
1
32
37

LINQKit really extend a great part of LINQ to Entities, but I can't find a way to works `PieceSpecifications.WithColor(car.Color).Predicate.Invoke(p)`. Unfortunately seems that LINQKit doesn't support a method with params, like WithColor. Thanks anyway. – Kim Tranjan Mar 23 '12 at 16:24

score 0 · Answer 3 · answered Mar 23 '12 at 02:02

0

Maybe make IsSatisfiedBy() and extension method to IQueryable. Here is K. Scott Allen's approach: http://odetocode.com/Blogs/scott/archive/2012/03/19/avoiding-notsupportedexception-with-iqueryable.aspx

answered Mar 23 '12 at 02:02

Kevin M

1,110
8
9

It's another scenario. The problem isn't about how to select a collection with another type(DTO). Maybe it will be helpful to someone. Thank you. – Kim Tranjan Mar 23 '12 at 16:29

Specification inside LINQ with EF 4.3

3 Answers3