Linq executing query generates not supported exception

Question

I'm trying to execute a linq query in an extension method but I am getting the following exception on the ToArray() function call - it seems the query is having issues with my IList somehow, I have tried many different things and googled but fails to see the issue

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: Cannot compare elements of type 'System.Collections.Generic.IList`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types, enumeration types and entity types are supported.

Code

public static IList<Shared.Poco.DimValue> ToDimValuePocoList(this IQueryable<DAL.DimValue> source)
{
        IList<Shared.Poco.DimValue> values = new List<Shared.Poco.DimValue>();

        foreach (DAL.DimValue dv in source.ToArray())
        {
            values.Add(new Shared.Poco.DimValue
            {
                DimensionId = dv.DimensionID,
                DimValueName = dv.DimValueName,
                DimValueNo = dv.DimValueNo
            });
        }

        return values;
}

The class the calls the extension looks like this:

public class DimValue : IDimValue
{
    private IDimValueRepository _repository;
    private IAccessableDimensionValues _accessableDimensionValues;
    private IRevision _revision;

    public DimValue(IDimValueRepository reposotory, IAccessableDimensionValues accessableDimensionValues, IRevision revision)
    {
        _repository = reposotory;
        _accessableDimensionValues = accessableDimensionValues;
        _revision = revision;
    }

    public IList<Shared.Poco.DimValue> GetDimValueList(int budgetId, Shared.Poco.Dimension dimension, IList<string> dimensionValues, Shared.Poco.User user)
    {
        IList<Shared.Poco.DimValue> values = new List<Shared.Poco.DimValue>();
        
        int budgetRevisionId = _revision.GetLatesRevision(budgetId);
        IList<string> uniqueAccessableUBLValues = _accessableDimensionValues.GetUniqueAccessableDimensionValues(dimension, user.UserId, budgetRevisionId);
        
        if (dimensionValues != null && dimensionValues.Count > 0)
        {
            uniqueAccessableUBLValues = dimensionValues;
        }

        return _repository.GetDimValueList(budgetId, dimension.ToString(), uniqueAccessableUBLValues, user).ToDimValuePocoList();
    }

The implementation of the injected repository class inherits from the following base repository class

public interface IRepository<T>
{
    void Insert(T entity);
    void Delete(T entity);
    IQueryable<T> SearchFor(Expression<Func<T, bool>> predicate);
    IQueryable<T> GetAll();
    T GetById(int id);
}

public class Repository<T> : IRepository<T> where T : class
{
    protected DbSet<T> DbSet;

    public Repository(DbContext dataContext)
    {
        DbSet = dataContext.Set<T>();
    }

    #region IRepository<T> Members

    public void Insert(T entity)
    {
        DbSet.Add(entity);
    }

    public void Delete(T entity)
    {
        DbSet.Remove(entity);
    }

    public IQueryable<T> SearchFor(Expression<Func<T, bool>> predicate)
    {
        return DbSet.Where(predicate);
    }

    public IQueryable<T> GetAll()
    {
        return DbSet;
    }

    public T GetById(int id)
    {
        return DbSet.Find(id);
    }

    #endregion
}

Repository implementation

public class DimValueRepository : Repository<DimValue>, IDimValueRepository
{
    public DimValueRepository(KonstruktEntities context) : base(context) { }

    public IQueryable<DimValue> GetDimValueList(int budgetId, string dimensionId, IList<string> dimensionFilter, Shared.Poco.User user)
    {
        return this.SearchFor(dv => dv.BudgetID == budgetId && dv.DimensionID == dimensionId &&
            (dimensionFilter == null || dimensionFilter.Count == 0 || dimensionFilter.Contains(dv.DimValueNo)));
    }


    public IQueryable<DimValue> GetDimValueList(Shared.Poco.Dimension dimension, string startsWith, Shared.Poco.User user)
    {
        return this.SearchFor(dv=>dv.DimensionID == dimension.ToString() &&
            dv.DimValueNo.StartsWith(startsWith));
    }
}

EDIT

It's failing on the following row in the ToDimValuePocoList function:

foreach (DAL.DimValue dv in source.ToArray())

Here is the row calling the extension in the DimValue class

return _repository.GetDimValueList(budgetId, dimension.ToString(), uniqueAccessableUBLValues, user).ToDimValuePocoList();

Can't see where this is happening, but it's because you're passing the list into a predicate, and EF chokes while converting the predicate into a sql command. I can't tell immediately from your code where this predicate is being used. Not sure of the solution, other than the ham-handed (get values, search in memory for items in list) or WAGs (try an array rather than an IList) — , Dec 15 '14 at 17:10
hmm, what can be done to fix the issue with the Where-predicate called from the SearchFor function i Repository?! I added the RepositoryImplementation also — JohanLarsson, Dec 15 '14 at 17:17
You can't use `dimensionFilter` in your predicate, as EF can't convert its use into a sql expression. Could VTC as dupe, but check this question out, as they have the same issue http://stackoverflow.com/q/374267/ See if that helps you. — , Dec 15 '14 at 17:55
Thanks that did it! I moved the dimensionFilter logic outside the predicate in the BL instead. Now it works fine. Cheers :) — JohanLarsson, Dec 15 '14 at 18:36
Okay, VTC as dupe. Shouldn't affect your account, so no worries. — , Dec 15 '14 at 19:34

score 0 · Accepted Answer · answered Jan 13 '16 at 08:57

0

Answered by @Will in the comment above, i.e. cannot use dimensionFilter in my predicate. So I moved that logic outside of the predicate.

answered Jan 13 '16 at 08:57

JohanLarsson

475
1
8
23

Linq executing query generates not supported exception

1 Answers1