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

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.