How to convert UNION ALL to OR in Linq to SQL expression trees (Oracle ORA-00932: inconsistent datatypes: expected got CLOB)

Question

I am working with EF6 and am using db first generated models for MSSQL and Oracle. In few places I am searching by multiple search criteria which results in UNION ALL sql generated where each query is being in it's own sub-select.

One of columns in Oracle table is CLOB and linq to sql after it wraps all selects with UNION ALL at the top of all UNIONS it calls SELECT DISTINCT "UnionAll1"."UNIQUE_ID" AS "C1", ... which requires to compare CLOBs and fails on Oracle side.

ORA-00932: inconsistent datatypes: expected - got CLOB

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Oracle.ManagedDataAccess.Client.OracleException: ORA-00932: inconsistent datatypes: expected - got CLOB

Is there a way to remove that DISTINCT statement? How can I make this work?

UPDATE Mechanism that generates LINQ looks like this:

public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    var subQueries = new List<IQueryable<T>>();
    if (search != null)
    {
        if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPolicyNumber(search));
        }

        if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByUniqueId(search));
        }

        if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPostCode(search));
        }
    }

    return subQueries.DefaultIfEmpty(queryable)
        .Aggregate((a, b) => a.Union(b));
}

Example of specific search method:

 public static IQueryable<IRequestId> SearchByRequestId<IRequestId>(this IQueryable<IRequestId> queryable, SearchModel search)
    {
        var interfacesToColumnNames = new Dictionary<Type, string>
        {
            {typeof (IRequestId<>), "requestid"},
            {typeof (IRequest_Id<>), "request_id"},
        };

        var paramLambda = Expression.Parameter(typeof (IRequestId));
        var columnLambda = Expression.Property(paramLambda, interfacesToColumnNames.Single(o => queryable.ElementType.GetInterfaces().Any(oo => oo.Name == o.Key.Name)).Value);
        var lambda = Expression.Lambda<Func<IRequestId, bool>>(
            Expression.Equal(columnLambda, Expression.Convert(Expression.Constant(search.RequestId), columnLambda.Type)), paramLambda);
        queryable = queryable.Where(lambda);

        return queryable;
    }

Example where it gets called in controller:

 public ActionResult QUOTE_HOUSE()
    {
        var onlineDocs =
            this.DatabaseManager.GetEntities<QUOTE_HOUSE>().ApplySearch(Search)
                .Take(10);
        return View("QUOTE_HOUSE", onlineDocs.ToList());
    }

It would be good if you include at least one problematic LINQ query. If I understand correctly, you are not doing explicitly `Distinct` or `Union`? — Ivan Stoev, Jun 19 '16 at 12:47
@IvanStoev Hi Ivan, most certainly not, reason I refrained of adding code it is quite complicated and might distract many with length and complexity from what I am actually trying to achieve. You can have a look at the code that combines results in this answer http://stackoverflow.com/questions/37815309/enumerable-emptyt-asqueryable-this-method-supports-the-linq-to-entities-i#answer-37815457 — Matas Vaitkevicius, Jun 19 '16 at 14:42
Hi Matas, I remember that question :) So the union is actually generated by you. — Ivan Stoev, Jun 19 '16 at 14:52
Any option to replace that union with `or` predicates? i.e. instead of `queryable.SearchByXXX(search)` which returns queryable with `Where` applied (I guess) to get only the where expressions, and then combine them with `Or` inside a single `Where`? — Ivan Stoev, Jun 19 '16 at 15:00
@IvanStoev I like this idea with `OR` chances are will be faster too. — Matas Vaitkevicius, Jun 19 '16 at 15:02

Ivan Stoev · Accepted Answer · 2016-06-23T00:12:27.803

Based on the additional information from the comments, the problematic queries are produced by the following procedure:

public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    var subQueries = new List<IQueryable<T>>();
    if (search != null)
    {
        if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPolicyNumber(search));
        }

        if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByUniqueId(search));
        }

        if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
        {
            subQueries.Add(queryable.SearchByPostCode(search));
        }
    }

    return subQueries.DefaultIfEmpty(queryable)
        .Aggregate((a, b) => a.Union(b));
}

where I assume the supporting methods are something like this

public static IQueryable<T> SearchByPolicyNumber<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    return queryable.Where(x => predicate_using_PolicyNumber(x, search));
}

public static IQueryable<T> SearchByUniqueId<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    return queryable.Where(x => predicate_using_UniqueId(x, search));
}

public static IQueryable<T> SearchByPostCode<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    return queryable.Where(x => predicate_using_PostCode(x, search));
}

The problem is that EF translates the LINQ Union operator to SQL UNION ALL subquery with applied DISTINCT SELECT ... as you already found. I have no idea why it does it this way instead of simply translating it to SQL UNION, but actually there is no guarantee that it would work with such type of columns either.

The only way to solve the issue I see is to eliminate the Union operator by replacing it with a single Where with Or conditions. In order to do that, you have to slightly change your design.

First, extract the predicate part from the supporting methods:

public static class SearchPredicates
{
    public static Expression<Func<T, bool>> ByPolicyNumber<T>(SearchModel search) where T : class 
    {
        return x => predicate_using_PolicyNumber(x, search);
    }

    public static Expression<Func<T, bool>> ByUniqueId<T>(SearchModel search) where T : class 
    {
        return x => predicate_using_UniqueId(x, search);
    }

    public static Expression<Func<T, bool>> ByPostCode<T>(SearchModel search) where T : class 
    {
        return x => predicate_using_PostCode(x, search);
    }
}

Then modify the main method like this:

public static IQueryable<T> ApplySearch<T>(this IQueryable<T> queryable, SearchModel search) where T : class 
{
    var predicates = new List<Expression<<Func<T, bool>>>();
    if (search != null)
    {
        if (search.PolicyNumber.HasValue && typeof (IPolicyNumber).IsAssignableFrom(queryable.ElementType))
            predicates.Add(SearchPredicates.ByPolicyNumber(search));
        if (search.UniqueId.HasValue && typeof (IUniqueId).IsAssignableFrom(queryable.ElementType))
            predicates.Add(SearchPredicates.ByUniqueId(search));
        if (!string.IsNullOrWhiteSpace(search.PostCode) && typeof(IPostCode).IsAssignableFrom(queryable.ElementType))
            predicates.Add(SearchPredicates.ByPostCode(search));
    }
    if (predicates.Count == 0)
        return queryable;

    var parameter = predicates[0].Parameters[0];
    var condition = predicates[0].Body;
    for (int i = 1; i < predicates.Count; i++)
        condition = Expression.Or(condition, predicates[i].Body.ReplaceParameter(predicates[i].Parameters[0], parameter));
    var predicate = Expression.Lambda<Func<T, bool>>(condition, parameter);
    return queryable.Where(predicate);
}

You can use any EF compatible predicate builder, here I'm building the predicate manually. The helper method used is:

public static class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

With all that applied, hopefully the issue will be resolved.

Ivan you are a star it does seem to work :), WHERE clause now looks like this. `AND ((1001 = "Extent1"."POLICY_NO") OR (900000977 = "Extent1"."UNIQUE_ID"))`. — Matas Vaitkevicius, Jun 20 '16 at 09:59
Cool :) And looking at the updated post, the transition to predicate seems to be even easier than I thought. — Ivan Stoev, Jun 20 '16 at 11:01
yep only had to return `lambdas` :) I have also changed this bit `f (predicates.Count == 0) return queryable; var oredWhere = predicates.Aggregate((a, b) => { var param = a.Parameters[0]; var cond = a.Body; return Expression.Lambda>(Expression.Or(cond, b.Body.ReplaceParameter(b.Parameters[0], param)),param); }); return queryable.Where(oredWhere);` I just had to use `Aggregate` somewhere :) — Matas Vaitkevicius, Jun 20 '16 at 11:05

score 0 · Answer 2 · answered Jun 28 '16 at 08:23

Just figured an alternative.

return subQueries.DefaultIfEmpty(queryable) .Aggregate((a, b) => a.Concat(b));

then after ToList() where you are actually using output .Distinct(new YourEqualityComparer())). To filter out possible duplicates.

This isn't ideal solution since requires you to manually implement IEquitable on structures, but if you are after performance it is likely to be faster. OR will require composite indexes on database while querying by two separate single column indexes then combining the data will be not require to have all possible column-index combinations on table. Another downside you are likely to use Take() before ToList() and then after filtering you might get to few records (and have to re-query).

How to convert UNION ALL to OR in Linq to SQL expression trees (Oracle ORA-00932: inconsistent datatypes: expected got CLOB)

2 Answers2