CODEWITHSUNDEEP
c#sqllinqlinq-to-sqlexpression-trees
Grofit
Grofit

Reputation: 18445

Linq to sql expression tree execution zone issue

I have got a bit of an issue and was wondering if there is a way to have my cake and eat it.

Currently I have a Repository and Query style pattern for how I am using Linq2Sql, however I have got one issue and I cannot see a nice way to solve it. Here is an example of the problem:

var someDataMapper = new SomeDataMapper();
var someDataQuery = new GetSomeDataQuery();
var results = SomeRepository.HybridQuery(someDataQuery)
                            .Where(x => x.SomeColumn == 1 || x.SomeColumn == 2)
                            .OrderByDescending(x => x.SomeOtherColumn)
                            .Select(x => someDataMapper.Map(x));

return results.Where(x => x.SomeMappedColumn == "SomeType");

The main bits to pay attention to here are Mapper, Query, Repository and then the final where clause. I am doing this as part of a larger refactor, and we found that there were ALOT of similar queries which were getting slightly different result sets back but then mapping them the same way to a domain specific model. So take for example getting back a tbl_car and then mapping it to a Car object. So a mapper basically takes one type and spits out another, so exactly the same as what would normally happen in the select:

// Non mapped version
select(x => new Car 
{
    Id = x.Id,
    Name = x.Name,
    Owner = x.FirstName + x.Surname
});

// Mapped version
select(x => carMapper.Map(x));

So the car mapper is more re-usable on all areas which do similar queries returning same end results but doing different bits along the way. However I keep getting the error saying that Map is not able to be converted to SQL, which is fine as I dont want it to be, however I understand that as it is in an expression tree it would try to convert it.

{"Method 'SomeData Map(SomeTable)' has no supported translation to SQL."}

Finally the object that is returned and mapped is passed further up the stack for other objects to use, which make use of Linq to SQL's composition abilities to add additional criteria to the query then finally ToList() or itterate on the data returned, however they filter based on the mapped model, not the original table model, which I believe is perfectly fine as answered in a previous question:

Linq2Sql point of retrieving data

So to sum it up, can I use my mapping pattern as shown without it trying to convert that single part to SQL?

Upvotes: 1

Views: 270

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

Yes, you can. Put AsEnumerable() before the last Select:

var results = SomeRepository.HybridQuery(someDataQuery)
                            .Where(x => x.SomeColumn == 1 || x.SomeColumn == 2)
                            .OrderByDescending(x => x.SomeOtherColumn)
                            .AsEnumerable()
                            .Select(x => someDataMapper.Map(x));

Please note, however, that the second Where - the one that operates on SomeMappedColumn - will now be executed in memory and not by the database. If this last where clause significantly reduces the result set this could be a problem.

An alternate approach would be to create a method that returns the expression tree of that mapping. Something like the following should work, as long as everything happening in the mapping is convertible to SQL.

Expression<Func<EntityType, Car>> GetCarMappingExpression()
{
    return new Expression<Func<EntityType, Car>>(x => new Car 
    {
        Id = x.Id,
        Name = x.Name,
        Owner = x.FirstName + x.Surname
    });
}

Usage would be like this:

var results = SomeRepository.HybridQuery(someDataQuery)
                            .Where(x => x.SomeColumn == 1 || x.SomeColumn == 2)
                            .OrderByDescending(x => x.SomeOtherColumn)
                            .Select(GetCarMappingExpression());

Upvotes: 5

Related Questions